前提：

以下分析基于标准的配置选项：

tx_isolation = REPEATABLE-READ

innodb_locks_unsafe_for_binlog = OFF

lock->type_mode用来表示锁的类型，实际上lock->type_mode包含了几乎所有锁的模式信息，例如锁类型判断是X锁还是S锁 lock->type_mode &LOCK_TYPE_MASK

LOCK_MODE_MASK	0xFUL	用于表示锁模式掩码
LOCK_TYPE_MASK	0xF0UL	用于表示锁类型，LOCK_TABLE或者LOCK_REC
LOCK_WAIT	256	表示需要锁等待，还没有获得锁，只是在等待队列中等待
LOCK_ORDINARY	0	普通的next-key锁，锁记录，并锁记录前面的gap，这样可以防止幻读。 假设索引包括10，11，13，20，则next-key锁为： (negative infinity, 10], (10, 11], (11, 13], (13, 20], (20, positive infinity) 我们经常在innodb_locks表中看到的supremum pseudo-record就是锁住了最大值往后的gap.
LOCK_GAP	512	只持有记录前的gap锁，例如，在一个gap上的x锁无法修改bit被设置的记录 在从索引记录链上移除记录时会加该类型的锁。
LOCK_REC_NOT_GAP	1024	也就是普通记录锁，只锁住记录，因此不会阻塞向该记录之前的gap中插入记录。
LOCK_INSERT_INTENTION	2048	插入意图锁,目的是让插入索引记录时等待，直到在gap上没有其他冲突的锁 记住，即使获得了等待的锁，也依然会持有插入意图锁

不同锁模式

enum lock_mode {

    LOCK_IS = 0, /* intention shared */

    LOCK_IX, /* intention exclusive */

    LOCK_S, /* shared */

    LOCK_X, /* exclusive */

    LOCK_AUTO_INC, /* locks the auto-inc counter of a table

            in an exclusive mode */

    LOCK_NONE, /* this is used elsewhere to note consistent read */

    LOCK_NUM = LOCK_NONE/* number of lock modes */

};

当使用唯一键或主键来检索数据时，gap锁是没有必要的（但如果查找的where条件中只包含了唯一索引的部分列时，则需要gap锁）

INSERT INTENTION锁在INSERT数据之前，它也是一种GAP锁，当多个事务同时插入记录到同一个索引gap时，如果不是GAP中的同一个位置，则无需等待。例如索引中有两个记录4和7，两个并发事务同时插入5和6，都会各自在获取记录排他锁之前先锁住INSERT INTENTION锁，但互相并不阻塞。

1.创建测试表

drop table section;

CREATE TABLE `section` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `title` varchar(255) NOT NULL,

  `tree_left` int(11) DEFAULT NULL,

  `tree_right` int(11) DEFAULT NULL,

  `tree_level` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `tree_left` (`tree_left`) ,

  KEY `tree_right` (`tree_right`)

) ENGINE=InnoDB;

INSERT INTO `section` VALUES (‘1′, ‘root’, ‘1’, ’14’, ‘0’);

INSERT INTO `section` VALUES (‘4′, ‘left tree’, ‘8’, ’13’, ‘1’);

INSERT INTO `section` VALUES (’10’, ‘left tree3′, ’11’, ’11’, ‘3’);

INSERT INTO `section` VALUES (’11’, ‘right Tree’, ‘2’, ‘7’, ‘1’);

INSERT INTO `section` VALUES (’16’, ‘right Tree 2′, ‘3’, ‘6’, ‘2’);

INSERT INTO `section` VALUES (’27’, ‘right Tree 3′, ‘4’, ‘5’, ‘3’);

 

2.场景测试，目的是找出关键函数

断点：

lock_rec_create

sel_set_rec_lock

lock0lock.c:1050

 

a.根据主键值删除记录

delete from section where id =8;

这里会根据主键扫描，堆栈如下：

ha_innobase::index_read

 ->row_search_for_mysql

     |–>sel_set_rec_lock (第4280行)

          –>二级索引lock_sec_rec_read_check_and_lock

          –>聚集索引lock_clust_rec_read_check_and_lock

             ->lock_rec_lock

                   |–>lock_rec_lock_fast->lock_rec_lock_fast->lock_rec_create

                   |–>lock_rec_lock_slow

传递参数type_mode = 1027 =  3+1024 =  LOCK_X +LOCK_REC_NOT_GAP

lock->type_mode = (type_mode & ~LOCK_TYPE_MASK) | LOCK_REC = 1059 = LOCK_X | LOCK_REC_NOT_GAP | LOCK_REC

不过如果是根据范围(或者where条件不是确定主键时)来删除记录，则在主键记录上加LOCK_X锁(锁模式值为3)

b.根据二级索引值删除记录

delete from section where tree_left = 8;

首先根据二级索引查找记录，对二级索引记录加锁

ha_innobase::index_read->row_search_for_mysql->sel_set_rec_lock->lock_sec_rec_read_check_and_lock->lock_rec_lock->lock_rec_lock_fast->lock_rec_create

type_mode=3 = LOCK_X

lock->type_mode = (type_mode & ~LOCK_TYPE_MASK) | LOCK_REC = 35 = LOCK_REC | LOCK_X

第二次，根据二级索引记录回查聚集索引

row_sel_get_clust_rec_for_mysql->lock_clust_rec_read_check_and_lock->lock_rec_lock->lock_rec_lock_fast->lock_rec_create

type_mode=1027

lock->type_mode = LOCK_X | LOCK_REC_NOT_GAP | LOCK_REC

获得主键记录后，就可以执行更新了(row_update_for_mysql)

第三次，扫描下一条记录，看看是否满足条件。

ha_innobase::general_fetch->row_search_for_mysql->sel_set_rec_lock->lock_sec_rec_read_check_and_lock->lock_rec_lock->lock_rec_lock_slow->lock_rec_create

type_mode=515

lock->type_mode = 547 = LOCK_GAP | LOCK_X |  LOCK_REC

 

 

 

 

 

从show engine innodb status里的信息来看，也符合

TABLE LOCK table `test`.`section` trx id E1885D33 lock mode IX

RECORD LOCKS space id 109 page no 4 n bits 80 index `tree_left` of table `test`.`section` trx id E1885D33 lock_mode X

RECORD LOCKS space id 109 page no 3 n bits 80 index `PRIMARY` of table `test`.`section` trx id E1885D33 lock_mode X locks rec but not gap

RECORD LOCKS space id 109 page no 4 n bits 80 index `tree_left` of table `test`.`section` trx id E1885D33 lock_mode X locks gap before rec

c.二级索引上存在重复值

测试表：

drop table t1;

create table t1 (a int primary key , b int ,key(b));

insert into t1 values (1,10),(2,20),(3,20),(4,30);

delete from t1 where b = 20;

lock_sec_rec_read_check_and_lock->lock_rec_lock->lock_rec_lock_fast->lock_rec_create

heap_no=3

type_mode=3,lock->type_mode=35

row_sel_get_clust_rec_for_mysql->lock_clust_rec_read_check_and_lock->lock_rec_lock->lock_rec_lock_fast->lock_rec_create

type_mode=1027

lock->type_mode = 1059

row_update_for_mysql

继续查下一条数据。

sel_set_rec_lock->lock_sec_rec_read_check_and_lock->lock_rec_lock->lock_rec_lock_fast

type_mode=3，

row_sel_get_clust_rec_for_mysql->lock_clust_rec_read_check_and_lock->lock_rec_lock->lock_rec_lock_fast

mode=1027

row_update_for_mysql

row_search_for_mysql

lock_sec_rec_read_check_and_lock->lock_rec_lock->lock_rec_lock_slow->lock_rec_create

type_mode=547

show engine innodb status的结果：

4 lock struct(s), heap size 1248, 5 row lock(s), undo log entries 2

MySQL thread id 389, OS thread handle 0x7fa35e53c700, query id 1545 localhost root

TABLE LOCK table `test`.`t1` trx id E1885D8E lock mode IX

RECORD LOCKS space id 118 page no 4 n bits 72 index `b` of table `test`.`t1` trx id E1885D8E lock_mode X

RECORD LOCKS space id 118 page no 3 n bits 72 index `PRIMARY` of table `test`.`t1` trx id E1885D8E lock_mode X locks rec but not gap

RECORD LOCKS space id 118 page no 4 n bits 72 index `b` of table `test`.`t1` trx id E1885D8E lock_mode X locks gap before rec

 

因此，如果事务不提交的话，试图插入tree_left值为[4,11)范围的值都会被阻塞掉。

c.二级索引上存在重复值

测试表：

drop table t1;

create table t1 (a int primary key , b int ,key(b));

insert into t1 values (1,10),(2,20),(3,20),(4,30);

delete from t1 where b = 20;

lock_sec_rec_read_check_and_lock->lock_rec_lock->lock_rec_lock_fast->lock_rec_create

heap_no=3

type_mode=3,lock->type_mode=35

row_sel_get_clust_rec_for_mysql->lock_clust_rec_read_check_and_lock->lock_rec_lock->lock_rec_lock_fast->lock_rec_create

type_mode=1027

lock->type_mode = 1059

row_update_for_mysql

继续查下一条数据。

sel_set_rec_lock->lock_sec_rec_read_check_and_lock->lock_rec_lock->lock_rec_lock_fast

type_mode=3，

row_sel_get_clust_rec_for_mysql->lock_clust_rec_read_check_and_lock->lock_rec_lock->lock_rec_lock_fast

mode=1027

row_update_for_mysql

row_search_for_mysql

lock_sec_rec_read_check_and_lock->lock_rec_lock->lock_rec_lock_slow->lock_rec_create

type_mode=547

show engine innodb status的结果：

4 lock struct(s), heap size 1248, 5 row lock(s), undo log entries 2

MySQL thread id 389, OS thread handle 0x7fa35e53c700, query id 1545 localhost root

TABLE LOCK table `test`.`t1` trx id E1885D8E lock mode IX

RECORD LOCKS space id 118 page no 4 n bits 72 index `b` of table `test`.`t1` trx id E1885D8E lock_mode X

RECORD LOCKS space id 118 page no 3 n bits 72 index `PRIMARY` of table `test`.`t1` trx id E1885D8E lock_mode X locks rec but not gap

RECORD LOCKS space id 118 page no 4 n bits 72 index `b` of table `test`.`t1` trx id E1885D8E lock_mode X locks gap before rec

 

这里只是通过几个gdb挖掘出几个关键的函数，下回将开始深入分析这些函数的执行流程。