[MySQL 学习] Innodb锁系统(2)关键函数路径

前提:

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

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挖掘出几个关键的函数,下回将开始深入分析这些函数的执行流程。

原创文章,转载请注明: 转载自Simple Life

本文链接地址: [MySQL 学习] Innodb锁系统(2)关键函数路径

Post Footer automatically generated by wp-posturl plugin for wordpress.


Comments

Leave a Reply

Your email address will not be published. Name and email are required


Current month ye@r day *