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插入資料被阻塞是應該的。