MySQL 中的 INSERT 是怎么加锁的？

加了插入意向锁后，插入数据之前，此时执行了 select...lock in share mode 语句（没有取到待插入的值），然后插入了数据，下一次再执行 select...lock in share mode（不会跟插入意向锁冲突），发现多了一条数据，于是又产生了幻读。会出现这种情况吗？

一、INSERT 加锁的困惑

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row.

二、编译 MySQL 源码

• CMake：CMake 本身并不是编译工具，它是通过编写一种平台无关的 CMakeList.txt 文件来定制编译流程的，然后再根据目标用户的平台进一步生成所需的本地化 Makefile 和工程文件，如 Unix 的 Makefile 或 Windows 的 Visual Studio 工程；
• Bison：MySQL 在执行 SQL 语句时，必然要对 SQL 语句进行解析，一般来说语法解析器会包含两个模块：词法分析和语法规则。词法分析和语法规则模块有两个较成熟的开源工具 Flex 和 Bison 分别用来解决这两个问题。MySQL 出于性能和灵活考虑，选择了自己完成词法解析部分，语法规则部分使用了 Bison，所以这里我们还要先安装 Bison。Bison 的默认安装路径为 C:\Program Files\GnuWin32，但是千万不要这样，一定要记得选择一个不带空格的目录，譬如 C:\GnuWin32 要不然在后面使用 Visual Studio 编译 MySQL 时会卡死；
• Visual Studio：没什么好说的，Windows 环境下估计没有比它更好的开发工具了吧。

D:\mysql-5.6.40> mkdir project
D:\mysql-5.6.40> cd project
D:\mysql-5.6.40\project> cmake -G "Visual Studio 11 2012 Win64" ..
复制

• 首先是 sql\sql_locale.cc 文件，看名字就知道这个文件用于国际化与本土化，这个文件里有各个国家的语言字符，但是这个文件却是 ANSI 编码，所以要将其改成 Unicode 编码；
• 打开 sql\mysqld.cc 文件的第 5239 行，将 DBUG_ASSERT(0) 改成 DBUG_ASSERT(1)，要不然调试时会触发断言；

三、调试 INSERT 加锁流程

> use test;
> create table t(id int NOT NULL AUTO_INCREMENT , PRIMARY KEY (id));
> insert into t(id) values(1),(10),(20),(50);
复制

if (lock_rec_other_has_conflicting(
        static_cast<enum lock_mode>(
            LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION),
        block, next_rec_heap_no, trx)) {
 
    /* Note that we may get DB_SUCCESS also here! */
    trx_mutex_enter(trx);
 
    err = lock_rec_enqueue_waiting(
        LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION,
        block, next_rec_heap_no, index, thr);
 
    trx_mutex_exit(trx);
} else {
    err = DB_SUCCESS;
}
复制

impl_trx = trx_rw_is_active(trx_id, NULL);
 
if (impl_trx != NULL
    && !lock_rec_has_expl(LOCK_X | LOCK_REC_NOT_GAP, block,
              heap_no, impl_trx)) {
    ulint    type_mode = (LOCK_REC | LOCK_X
                 | LOCK_REC_NOT_GAP);
 
    lock_rec_add_to_queue(
        type_mode, block, heap_no, index,
        impl_trx, FALSE);
}
复制

• 执行 insert 语句，判断是否有和插入意向锁冲突的锁，如果有，加插入意向锁，进入锁等待；如果没有，直接写数据，不加任何锁；
• 执行 select ... lock in share mode 语句，判断记录上是否存在活跃的事务，如果存在，则为 insert 事务创建一个排他记录锁，并将自己加入到锁等待队列；

-------------
RW-LATCH INFO
-------------
RW-LOCK: 000002C97F62FC70
Locked: thread 10304 file D:\mysql-5.6.40\storage\innobase\btr\btr0cur.cc line 879  S-LOCK
RW-LOCK: 000002C976A3B998
Locked: thread 10304 file D:\mysql-5.6.40\storage\innobase\btr\btr0cur.cc line 256  S-LOCK
Locked: thread 10304 file d:\mysql-5.6.40\storage\innobase\include\btr0pcur.ic line 518  S-LOCK
Locked: thread 2820 file D:\mysql-5.6.40\storage\innobase\btr\btr0cur.cc line 256  S-LOCK
Locked: thread 2820 file D:\mysql-5.6.40\storage\innobase\row\row0ins.cc line 2339  S-LOCK
RW-LOCK: 000002C976A3B8A8  Waiters for the lock exist
Locked: thread 2820 file D:\mysql-5.6.40\storage\innobase\btr\btr0cur.cc line 256  X-LOCK
Total number of rw-locks 16434
OS WAIT ARRAY INFO: reservation count 10
--Thread 10304 has waited at btr0cur.cc line 256 for 26.00 seconds the semaphore:
S-lock on RW-latch at 000002C976A3B8A8 created in file buf0buf.cc line 1069
a writer (thread id 2820) has reserved it in mode  exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file btr0cur.cc line 256
Last time write locked in file D:\mysql-5.6.40\storage\innobase\btr\btr0cur.cc line 256
OS WAIT ARRAY INFO: signal count 8
Mutex spin waits 44, rounds 336, OS waits 7
RW-shared spins 3, rounds 90, OS waits 3
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 7.64 mutex, 30.00 RW-shared, 0.00 RW-excl
复制

case BTR_MODIFY_LEAF:
    mode = latch_mode == BTR_SEARCH_LEAF ? RW_S_LATCH : RW_X_LATCH;
    get_block = btr_block_get(
        space, zip_size, page_no, mode, cursor->index, mtr);
复制

UNIV_INTERN
dberr_t
row_ins_clust_index_entry_low(
/*==========================*/
    ulint        flags,    /*!< in: undo logging and locking flags */
    ulint        mode,    /*!< in: BTR_MODIFY_LEAF or BTR_MODIFY_TREE,
                depending on whether we wish optimistic or
                pessimistic descent down the index tree */
    dict_index_t*    index,    /*!< in: clustered index */
    ulint        n_uniq,    /*!< in: 0 or index->n_uniq */
    dtuple_t*    entry,    /*!< in/out: index entry to insert */
    ulint        n_ext,    /*!< in: number of externally stored columns */
    que_thr_t*    thr)    /*!< in: query thread */
{
    /* 开启一个 mini-transaction */
    mtr_start(&mtr);
     
    /* 调用 btr_cur_latch_leaves -> btr_block_get 加 RW_X_LATCH */
    btr_cur_search_to_nth_level(index, 0, entry, PAGE_CUR_LE, mode,
                    &cursor, 0, __FILE__, __LINE__, &mtr);
     
    if (mode != BTR_MODIFY_TREE) {
        /* 不需要修改 BTR_TREE，乐观插入 */
        err = btr_cur_optimistic_insert(
            flags, &cursor, &offsets, &offsets_heap,
            entry, &insert_rec, &big_rec,
            n_ext, thr, &mtr);
    } else {
        /* 需要修改 BTR_TREE，先乐观插入，乐观插入失败则进行悲观插入 */
        err = btr_cur_optimistic_insert(
            flags, &cursor,
            &offsets, &offsets_heap,
            entry, &insert_rec, &big_rec,
            n_ext, thr, &mtr);
        if (err == DB_FAIL) {
            err = btr_cur_pessimistic_insert(
                flags, &cursor,
                &offsets, &offsets_heap,
                entry, &insert_rec, &big_rec,
                n_ext, thr, &mtr);
        }
    }
     
    /* 提交 mini-transaction */
    mtr_commit(&mtr);
}
复制

• 修改一个页需要获得该页的 X-LATCH；
• 访问一个页需要获得该页的 S-LATCH 或 X-LATCH；
• 持有该页的 LATCH 直到修改或者访问该页的操作完成。

• 执行 insert 语句，对要操作的页加 RW-X-LATCH，然后判断是否有和插入意向锁冲突的锁，如果有，加插入意向锁，进入锁等待；如果没有，直接写数据，不加任何锁，结束后释放 RW-X-LATCH；
• 执行 select ... lock in share mode 语句，对要操作的页加 RW-S-LATCH，如果页面上存在 RW-X-LATCH 会被阻塞，没有的话则判断记录上是否存在活跃的事务，如果存在，则为 insert 事务创建一个排他记录锁，并将自己加入到锁等待队列，最后也会释放 RW-S-LATCH；

原文:https://www.cnblogs.com/javastack/archive/2021/11/18/15571691.html