InnoDB行锁机制（gap锁是如何阻塞插入操作的)
我们知道在MySQL InnoDB存储引擎中，gap锁和gap锁是相互兼容的，但是gap锁和插入意向锁之前是冲突的，那么这个阻塞的过程是判定的呢？
InnoDB 在执行insert操作时，并不会显示加锁，如果是主键插入，只会设置对应记录上的trx id隐藏列，称为隐式加锁。
一、构建场景
比如说如下表结构
mysql> show create table t7\G
*************************** 1. row ***************************
Table: t7
Create Table: CREATE TABLE `t7` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)表中数据如下
mysql> select * from t7;
+----+------+
| id | name |
+----+------+
| 10 | aaa |
+----+------+
1 row in set (0.00 sec)假设事务操作如下
sesison-2 在执行插入操作时会被阻塞，确切来说，是被事务1session-1中的事务阻塞的。这种阻塞是如何实现的呢？
二、加锁过程分析
对于session-1中的事务(假设为trx1)来讲，由于select操作未能精确定外到数据行，所以需要在下一条记录上加gap锁。具体的加锁情况如下
2018-12-28T13:48:21.274985+08:00 3 [Note] InnoDB: current trx: 1806424 rec lock pool total size: 8
2018-12-28T13:48:21.281071+08:00 3 [Note] InnoDB: trx_id: 1806424 create a record lock and add it to lock hash table,
space_id: 78
page_no: 3
heap_no: 2
n_bits: 72
primary key: 1
is record lock: 1
is waiting: 0
is gap: 1
is record not gap: 0
is insert intention: 0
lock_mode: 3 (0:LOCK_IS, 1:LOCK_IX, 2:LOCK_S, 3:LOCK_X, 4:LOCK_AUTO_INC, 5:LOCK_NONE)而对于session-2中的事务,加锁类型为const ulint type_mode = LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION; 然后会去检测插入行的下一条记录是否存在锁，如果存在，检测是否存在锁冲突
lock_rec_has_to_wait(trx_t const*, unsigned long, ib_lock_t const*, bool) lock0lock.cc:856锁冲突过程如下
- 通过锁冲突矩阵，如果通过了，直接返回false，也就是不冲突。锁冲突矩阵如下，其实就是一个二维数组。
return(lock_compatibility_matrix[mode1][mode2]);
* Note that for rows, InnoDB only acquires S or X locks.
* For tables, InnoDB normally acquires IS or IX locks.
* S or X table locks are only acquired for LOCK TABLES.
* Auto-increment (AI) locks are needed because of
* statement-level MySQL binlog.
* See also lock_mode_compatible().
*/
static const byte lock_compatibility_matrix[5][5] = {
/** IS IX S X AI */
/* IS */ { TRUE, TRUE, TRUE, FALSE, TRUE},
/* IX */ { TRUE, TRUE, FALSE, FALSE, TRUE},
/* S */ { TRUE, FALSE, TRUE, FALSE, FALSE},
/* X */ { FALSE, FALSE, FALSE, FALSE, FALSE},
/* AI */ { TRUE, TRUE, FALSE, FALSE, FALSE}
};- 如果锁冲突矩阵返回检测失败，也就是冲突，需要下面额外的检测条件
- 1.如果是supremum行或者锁类型为只锁gap && 锁类型没有插入意向属性，则不存在冲突
- 2.如果要创建的锁类型没有插入意向属性 && 老得锁结构是gap锁 则不冲突
- 3.如果新创建的锁为gap锁 并且 已存在的锁不是gap锁，则不冲突
- 4.如果已经存在的锁为插入意向，则不冲突。
- 如果没有满足锁冲突矩阵，并且上面的4个条件也都不满足，就证明锁冲突了。
锁冲突检测代码如下，自行查阅。
if (trx != lock2->trx
&& !lock_mode_compatible(static_cast<lock_mode>(
LOCK_MODE_MASK & type_mode),
lock_get_mode(lock2))) {
/* We have somewhat complex rules when gap type record locks
cause waits */
if ((lock_is_on_supremum || (type_mode & LOCK_GAP))
&& !(type_mode & LOCK_INSERT_INTENTION)) {
/* Gap type locks without LOCK_INSERT_INTENTION flag
do not need to wait for anything. This is because
different users can have conflicting lock types
on gaps. */
return(FALSE);
}
if (!(type_mode & LOCK_INSERT_INTENTION)
&& lock_rec_get_gap(lock2)) {
/* Record lock (LOCK_ORDINARY or LOCK_REC_NOT_GAP
does not need to wait for a gap type lock */
return(FALSE);
}
if ((type_mode & LOCK_GAP)
&& lock_rec_get_rec_not_gap(lock2)) {
/* Lock on gap does not need to wait for
a LOCK_REC_NOT_GAP type lock */
return(FALSE);
}
if (lock_rec_get_insert_intention(lock2)) {
/* No lock request needs to wait for an insert
intention lock to be removed. This is ok since our
rules allow conflicting locks on gaps. This eliminates
a spurious deadlock caused by a next-key lock waiting
for an insert intention lock; when the insert
intention lock was granted, the insert deadlocked on
the waiting next-key lock.
Also, insert intention locks do not disturb each
other. */
return(FALSE);
}
return(TRUE);
}
return(FALSE);那么对应到本例中的情况
插入语句加锁如下
2018-12-28T13:48:21.274985+08:00 3 [Note] InnoDB: current trx: 1806425 rec lock pool total size: 8
2018-12-28T13:48:21.281071+08:00 3 [Note] InnoDB: trx_id: 1806425 create a record lock and add it to lock hash table,
space_id: 78
page_no: 3
heap_no: 2
n_bits: 72
primary key: 1
is record lock: 1
is waiting: 1
is gap: 1
is record not gap: 0
is insert intention: 1
lock_mode: 3 (0:LOCK_IS, 1:LOCK_IX, 2:LOCK_S, 3:LOCK_X, 4:LOCK_AUTO_INC, 5:LOCK_NONE)不满足以上的任何锁冲突检测通过条件，所以被阻塞。
从我们最初对行锁的认识也能够说的通，对于trx1中，我们查询select * from t7 where id =5 for update,返回数据为空，那么为了避免幻象，所有可以插入5的地方都不能插入数据。所以trx2插入数据被阻塞是应该的。
