一、InnoDB锁算法
InnoDB支持到行级别粒度的并发控制,下面我们分析下几种常见的行级锁类型,以及在哪些情况下会使用到这些类型的锁。
1)Record Lock(源码定义名称:LOCK_REC_NOT_GAP)
锁带上这个Flag时,表示这个锁对象只是单纯的锁在记录上,不会锁记录之前的GAP。在RC隔离级别下一般加的都是该类型的记录锁(但唯一二级索引上的duplicate key检查除外,总是加LOCK_ORDINARY类型的锁)。简单说就是单个行记录上加锁,防止事务间修改或删除数据。
2)Gap Lock(源码定义名称:LOCK_GAP)
间隙锁,表示只锁住一段范围,不锁记录本身,通常表示两个索引记录之间,或者索引上的第一条记录之前,或者最后一条记录之后的锁。可以理解为一种间隙锁,一般在RR隔离级别下会使用到GAP锁。你可以通过切换到RC隔离级别,或者开启选项innodb_locks_unsafe_for_binlog
来避免GAP锁。这时候只有在检查外键约束或者duplicate key检查时才会使用到GAP LOCK。
3)Next-Key Lock(源码定义名称:LOCK_ORDINARY)
Gap Lock + Record Lock,锁定一个范围及锁定记录本身。当前MySQL默认情况下使用RR的隔离级别,而Next-key LOCK正是为了解决RR隔离级别下的不可重复读问题和幻读问题,所谓不可重复读就是一个事务内执行相同的查询,会看到不同的行记录。在RR隔离级别下这是不允许的。
假设索引上有记录1,4,5,8,12
,我们执行类似语句SELECT … WHERE col > 10 FOR UPDATE
。如果我们不在(8, 12)
之间加上Next-key LOCK,另外一个会话就可能向其中插入一条记录9,再执行一次相同的SELECT ... FOR UPDATE
,就会看到新插入的记录。这也是为什么MySQL插入一条记录时,需要判断下一条记录上是否加锁了,如果加锁就需要等待。
Record Lock总是会去锁住索引记录,如果InnoDB存储引擎表建立的时候没有设置任何一个索引,这时InnoDB存储引擎会使用隐式的主键来进行锁定。
Next-Key Lock是结合了Gap Lock和Record Lock的一种算法,在Next-Key Lock算法下,InnoDB对于行的查询都是采用这种锁定算法,对于不同SQL查询语句,可能设置共享的Next-key Lock和排他的Next-Key Lock。InnoDB对于行的查询都是采用这种锁定算法的,例如一个索引有10, 11, 13, 20这四个值,那么该索引可能被Next-key Locking的区间为(负无穷, 10], (10, 11], (11, 13], (12, 20], (20, 正无穷)
。需要理解一点,InnoDB中加锁都是给所有记录一条一条加锁,并没有一个直接的范围可以直接锁住,所以会生成多个区间。
InnoDB在RR隔离级别下采用Next-key Lock(在RC隔离级别,对于外键及唯一性检查也会采用Next-key Lock),采用Next-Key Lock的锁定技术称为Next-Key Locking。其设计的目的是为了解决“不可重复读”问题(RR隔离级别就是解决不可重复读问题)。然而,当查询条件为等值时,且索引有唯一属性时(就是只锁定一条记录),InnoDB存储引擎会对Next-Key Lock进行优化,将其降级为Record Lock,即仅锁住索引本身,而不是一个范围,因为此时不会产生重复读问题。
下面针对这几种锁算法,分别测试一下RR隔离级别+主键、RR隔离级别+普通索引、RC隔离级别+主键、RC隔离级别+普通索引的加锁方式。
RR隔离级别+主键
看下面例子,首先根据如下代码创建测试表t,然后开启两个事务进行操作。
1 2 3 4 5 6 |
create table t(a int primary key); insert into t select 1; insert into t select 2; insert into t select 5; insert into t select 7; insert into t select 9; |
创建完上面的环境之后,下面分别开启两个会话用来运行事务1和事务2。
# 事务1
1 2 3 4 5 6 7 |
mysql> begin; mysql> select * from t where a=5 for update; +---+ | a | +---+ | 5 | +---+ |
锁信息:
1 2 3 4 5 6 7 8 9 |
---TRANSACTION 35735, ACTIVE 4 sec 2 lock struct(s), heap size 1136, 1 row lock(s) #-- 2个锁结构,1个意向锁,1个记录锁 MySQL thread id 1537, OS thread handle 123145316831232, query id 86614 localhost root TABLE LOCK table `test`.`t` trx id 35735 lock mode IX RECORD LOCKS space id 362 page no 3 n bits 72 index PRIMARY of table `test`.`t` trx id 35735 lock_mode X locks rec but not gap #-- 记录锁 Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000005; asc ;; #-- 记录5 1: len 6; hex 000000008b89; asc ;; 2: len 7; hex ae000001180110; asc ;; |
# 事务2
1 2 3 4 5 6 7 8 9 |
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t select 4; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.01 sec) |
表t共有1、2、5、7、9五个值,在上面的例子中,在事务1中首先对“a=5”进行X锁定,而由于a是主键且唯一,因此锁定的仅是5这个值,而不是(2,5)这个范围。从事务1的锁信息也是可以看出的,这样在事务2中插入值4而不会阻塞,可以立即插入并返回。即锁定由Next-key Lock算法降级为Record Lock,从而提高应用的并发性。
需要注意的一点是,对于唯一键值的锁定,Next-Key Lock降级为Record Lock仅存在于查询所有的唯一索引列。若唯一索引由多个列组成,而查询仅是查找多个唯一索引列中的其中一个,那么查询其实是range类型查询,而不是const类型查询,故InnoDB存储引擎依然使用Next-Key Lock进行锁定。
但如果对主键进来范围查询时,锁的范围是怎么样的呢?
# 事务1
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t where a<=5 for update; +---+ | a | +---+ | 1 | | 2 | | 5 | +---+ 3 rows in set (0.00 sec) |
锁信息:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
---TRANSACTION 35733, ACTIVE 6 sec 2 lock struct(s), heap size 1136, 4 row lock(s) #-- 2个锁结构,1个意向锁IX,4个记录锁 MySQL thread id 1537, OS thread handle 123145316831232, query id 86585 localhost root TABLE LOCK table `test`.`t` trx id 35733 lock mode IX RECORD LOCKS space id 362 page no 3 n bits 72 index PRIMARY of table `test`.`t` trx id 35733 lock_mode X #-- 加Next-key lock Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000001; asc ;; #-- 记录1 1: len 6; hex 000000008b85; asc ;; 2: len 7; hex ab000001150110; asc ;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000002; asc ;; #-- 记录2 1: len 6; hex 000000008b86; asc ;; 2: len 7; hex ac000001530110; asc S ;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000005; asc ;; #-- 记录5 1: len 6; hex 000000008b89; asc ;; 2: len 7; hex ae000001180110; asc ;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000007; asc ;; #-- 记录7 1: len 6; hex 000000008b8c; asc ;; 2: len 7; hex b00000011a0110; asc ;; |
# 事务2
1 2 3 4 5 6 |
mysql> insert into test.t select 6; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into test.t select 8; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 |
表t共有1、2、5、7、9五个值,在上面的例子中,在事务1中执行“a<=5”范围查询时,根据锁信息可以看出事务1加锁范围是(负无穷, 1]、(1, 2]、(2, 5]、(5, 7]
,且1、2、5、7
这四条记录也都加锁了,也就是说再插入>=7
的记录都是会产生锁等待,插入记录8是成功的。
那么InnoDB如何判断是否允许插入数据呢?对于普通索引,insert的加锁策略是:查找小于等于 insert_rec 的第一条记录,然后查看第一条记录的下一个记录是否有GAP锁,有则等待,没有则插入。
比如说我们插入6这条记录,首先定位>=6
的记录,也就是5,然后确认5的下一条记录是否锁住了GAP?这里也就是7,当7这条记录有锁时代表锁住的是(5, 7)
这个范围,就不允许插入(会申请一把插入意向锁),保证了可重复读。证明了在RR隔离级别下使用了Next-key Lock来保证其“可重复读”的特性。如果没有锁就直接插入即可。
但是如果插入的记录有唯一约束时,只判断下一条记录是否锁住了GAP就不行了,显然会插入重复数据破坏唯一性。这时还会把插入的记录与前一条数据进行比较,如果相同则给插入记录的前一条记录加S Lock(lock in share mode),加锁成功则返回duplicate key,否则等待S Lock。
但这个地方有一个疑问,为什么MySQL在加锁时,不直接加5这条记录本身以及<5的记录呢?为什么还要给
(5, 7)
加锁呢?因为感觉(5, 7)加不加锁并不会影响RR级别可重复读的特性啊。其实这就跟B+树有关系了,首先MySQL定位到1这条记录并加锁,然后顺着1往后读取数据并加锁,直到读取到第一条不匹配数据才能确定是否停止继续读取数据,而在RR隔离级别下只要被读到的数据都需要进行加锁。如果你查询条件是<5,那么加锁只会加到5这条记录为止。
但如果是在RC隔离级别下,只会对符合条件的记录进行加记录锁,不会对满足条件的下一条记录进行加锁。
RR隔离级别+普通索引
正如上面所介绍的,Next-Key Lock降级为Record Lock仅在查询的列是唯一索引且条件为等值查询的情况下。若是辅助索引,则情况会完全不同,同样,首先根据如下代码创建测试表z。
1 2 3 4 5 6 |
create table z(id int primary key,b int,index(b)); insert into z values(1,1); insert into z values(3,1); insert into z values(5,3); insert into z values(7,6); insert into z values(10,8); |
表z的列b是辅助索引,若在事务1中执行下面的SQL语句:
1 2 |
mysql> begin; mysql> select * from z where b=3 for update; |
锁结构:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
---TRANSACTION 35760, ACTIVE 4 sec 4 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 1537, OS thread handle 123145316831232, query id 86633 localhost root TABLE LOCK table `test`.`z` trx id 35760 lock mode IX RECORD LOCKS space id 363 page no 4 n bits 72 index b of table `test`.`z` trx id 35760 lock_mode X #-- Next-key lock (1, 3) Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 4; hex 80000005; asc ;; RECORD LOCKS space id 363 page no 3 n bits 72 index PRIMARY of table `test`.`z` trx id 35760 lock_mode X locks rec but not gap Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 6; hex 000000008bac; asc ;; 2: len 7; hex aa000001580110; asc X ;; 3: len 4; hex 80000003; asc ;; RECORD LOCKS space id 363 page no 4 n bits 72 index b of table `test`.`z` trx id 35760 lock_mode X locks gap before rec #-- GAP LOCK Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000007; asc ;; |
很明显,这时SQL语句通过索引列b进行查询,因此其使用传统的Next-key Locking技术加锁,并且由于有两个索引,其需要分别进行锁定。对于聚集索引,其仅对列id等于5的索引加上Record Lock。那么,为什么主键索引上的记录也要加锁呢?因为有可能其他事务会根据主键对 z 表进行更新,试想一下,如果主键索引没有加锁,那么显然会存在并发问题。
而对于辅助索引,其加上的是Next-key Lock,锁定的范围是(1, 3),特别需要注意的是,InnoDB存储引擎还会对辅助索引下一个键值加上Gap lock,即还有一个辅助索引范围为(3, 6)的锁。因此,若在新的事务2中运行下面的SQL语句,都会被阻塞。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from z where id=5 lock in share mode; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into z select 2,1; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into z select 4,2; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into z select 6,6; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
可以看出以下结论:
第一个SQL语句,出现锁超时是因为在事务1中执行的SQL语句已经对聚集索引中的列“a=5”的值加上X锁,因此执行会被阻塞。
第二个SQL语句,主键插入2,没有问题,插入的辅助索引值1也不在锁定的范围(1,3)中,因此可以执行成功。
第三个SQL语句,主键插入4,没有问题,插入的辅助索引值2在锁定的范围(1,3)中,因此执行会被阻塞。
第四个SQL语句,插入的主键6没有被锁定,6也不在范围(1,3)之间。但插入的值6在另一个锁定的范围(3,6)中,故同样需要等待。
在RR隔离级别下,对于INSERT的操作,其会检查插入记录的下一条记录是否被锁定,若已经被锁定,则不允许操作,从而避免不可重复读问题。而下面的SQL语句,不会被阻塞,可以立即执行。
1 2 3 |
insert into z select 8,6; insert into z select 2,0; insert into z select 6,7; |
从上面的例子中可以看到,Gap Lock的作用是为了阻止多个事务将记录插入到同一个范围内,解决“不可重复读”问题的产生。例如在上面的例子中,事务1中用户已经锁定了b=3的记录,若此时没有Gap Lock锁定(3, 6)
,那么用户可以插入索引b列为3的记录,这会导致事务1中的用户再次执行同样的查询时会返回不同的记录,即产生不可重复读问题。
这里有一个问题值得思考一下,为什么插入(6, 6)
不允许,而插入(8, 6)
是允许的。这跟InnoDB索引结构有关系,我们知道二级索引是指向主键,所以结构如:(1, 1), (1, 3), (3, 5), (6, 7), (8, 10)
。真正的Gap锁锁住的也是((1, 1), (1, 3))
这样的结构,所以当我们插入(6, 6)
时,需要插入到(3, 5), (6, 7)
之间,这区间被锁,所以无法插入;而我们插入(8, 6)
是需要插入到(6, 7), (8, 10)
之间,没有锁存在,所以可以插入成功。
另外,在RR隔离级别下,我们访问条件为二级索引的情况下,就算访问一条不存在的记录同样需要加Next-key Lokcs,比如我们查询
1 2 3 4 5 |
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from z where b=7 for update; Empty set (0.01 sec) |
锁结构如下:
1 2 3 4 5 6 7 8 |
---TRANSACTION 35783, ACTIVE 3 sec 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 1559, OS thread handle 123145317388288, query id 86874 localhost root TABLE LOCK table `test`.`z` trx id 35783 lock mode IX RECORD LOCKS space id 363 page no 4 n bits 80 index b of table `test`.`z` trx id 35783 lock_mode X locks gap before rec #-- 加Gap Lock Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000008; asc ;; 1: len 4; hex 8000000a; asc ;; |
从锁结构可以看到对于(6, 8)这个区间加了Gap Lock,也就是说插入这个区间的数据都会被阻塞。
虽然在RR隔离级别默认使用Gap Lock,但用户可以通过以下两种方式来显式地关闭Gap Lock:
1)将事务的隔离级别设置为READ COMMITTED;
2)将参数innodb_locks_unsafe_for_binlog设置为1;
当设置了上述参数或隔离级别调整到READ COMMITTED时,除了外键约束和唯一性检查(duplicate key)依然需要Gap Lock,其余情况仅使用Record Lock进行锁定。但需要知道的是,上述设置破坏了事务的隔离性,并且对于MySQL复制来说,可能会导致主从数据的不一致。虽然MySQL目前默认隔离级别是RR,但是基本生产环境标配基本都是RC隔离级别+ROW格式。
RC隔离级别+主键
# 事务1
1 2 3 4 5 6 7 8 9 10 11 |
mysql> set session transaction isolation level read committed; mysql> begin; mysql> select * from z where id>3 for update; +----+------+ | id | b | +----+------+ | 5 | 3 | | 7 | 6 | | 10 | 8 | +----+------+ 3 rows in set (0.00 sec) |
# 事务2
1 2 3 4 5 |
mysql> set session transaction isolation level read committed; mysql> begin; mysql> insert into z select 6,6; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 |
从两个事务的执行结果可以看出,当我在事务1执行主键范围for update时,事务2对这个范围扔可以申请写锁。证明RC隔离级别没有使用NEXT-KEY Lock,而是使用行级锁锁住对应的记录,具体可以show engine innodb status。
RC隔离级别+普通索引
# 事务1
1 2 3 4 5 6 7 8 9 |
mysql> begin; mysql> select * from z where b>3 for update; +----+------+ | id | b | +----+------+ | 7 | 6 | | 10 | 8 | +----+------+ 2 rows in set (0.00 sec) |
# 事务2
1 2 3 4 5 6 7 8 9 10 |
mysql> insert into z select 8,4; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> update z set b = 10 where b = 6; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> update z set b = 10 where b = 1; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 |
在事务2中,分别执行了三种不同的情况,第一种插入数据到事务1的for update范围内是可以的,因为这里事务1在RC模式下没有加NEXT-KEY LOCK锁,所以可以插入数据。但是第二种操作出现锁等待了,我们选择普通索引作为条件,此时MySQL给普通索引b>3的记录都会加行锁。同时,这些记录对应主键索引上的记录也都加上了锁。第三种操作成功了,说明b<3的记录都无锁。想看锁详细信息可以参考“MySQL InnoDB锁信息分析”
在RC隔离级别下,普通索引跟唯一索引的加锁基本没有什么区别,有的也就是在做等值查询时,唯一索引只会有一条记录,而普通索引可能会有多条。
对于不同隔离级别下的不同索引的组合,其各自加锁方式分析,详情请看何登成大神的文章“MySQL加锁处理分析”
二、InnoDB锁与索引
InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将会给所有记录加锁!在RR隔离级别下,由于查询条件没有索引,那么InnoDB需要扫描所有数据来查找数据,对于扫描过的数据InnoDB都会加上锁,并且是加Next-key lock。注意,InnoDB表无索引加锁在RR和RC隔离级别下是不同的。
在实际应用中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能,下面通过一些实际例子来加以说明。
在下所示的例子中,开始tab_no_index表没有索引:
1 2 |
create table tab_no_index(id int,b int) engine=innodb; insert into tab_no_index values(1,1),(2,2),(3,3),(4,4),(100,100); |
RR + 无索引
# 事务A
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> set tx_isolation = 'repeatable-read'; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_no_index where id = 1 for update; +------+------+ | id | b | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec) |
查看事务信息
1 2 3 4 5 |
---TRANSACTION 35703, ACTIVE 3 sec 2 lock struct(s), heap size 1136, 6 row lock(s) MySQL thread id 1528, OS thread handle 123145316552704, query id 86469 localhost root TABLE LOCK table `test`.`tab_no_index` trx id 35703 lock mode IX RECORD LOCKS space id 360 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`tab_no_index` trx id 35703 lock_mode X |
可以看到这个事务中有此参数6 row lock(s),所有记录全部锁住了。另外,由于我们没有显式主键,InnoDB自行创建了一个ROW_ID,索引名称为GEN_CLUST_INDEX。并且加了Next-key lock,锁模式为X。
# 事务B
1 2 3 4 5 6 7 8 |
mysql> set tx_isolation = 'repeatable-read'; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into tab_no_index values(5,5); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
如上所示的例子中,看起来事务A只给一行加了排他锁,但事务B在请求其他行的排他锁时,却出现了锁等待!原因就是在没有索引的情况下,InnoDB只能扫描所有记录(锁住所有记录)。当我们给其增加一个唯一索引后,InnoDB就只锁定了符合条件的行。
1 2 |
create table tab_with_index(id int,b int,primary key(id)); insert into tab_with_index values(1,1),(2,2),(3,3),(4,4),(100,100); |
InnoDB存储引擎的表在使用主键索引时使用行锁例子。
# 事务A
1 2 3 4 5 6 7 8 9 10 |
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_with_index where id = 1 for update; +------+------+ | id | name | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec) |
# 事务B
1 2 3 4 5 6 7 8 9 10 |
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_with_index where id = 2 for update; +------+------+ | id | name | +------+------+ | 2 | 2 | +------+------+ 1 row in set (0.00 sec) |
由这个例子可以看出,对于id是主键索引的情况下,只锁了id=1这一行记录。其余的行都是可以进行DML操作的,但前提条件是以id为条件。如果是以b字段为条件,那么还是会锁的。
1 2 |
mysql> select * from tab_with_index where b = 100 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
RC + 无索引
上面演示了在RR隔离级别下,对于where条件是非索引的情况下,InnoDB是对所有记录加Next-key Locks。
但是在RC隔离级别下,对于where条件是非索引的情况下,其没有对所有记录加锁,而是只对命中的数据的聚簇索引加X锁。根据MySQL官方手册,对于update和delete操作,RC只会锁住真正执行了写操作的记录,这是因为尽管InnoDB会锁住所有记录,MySQL Server层会进行过滤并把不符合条件的锁当即释放掉(违背了2PL的约束)。同时对于UPDATE语句,如果出现了锁冲突(要加锁的记录上已经有锁),InnoDB不会立即锁等待,而是执行semi-consistent read:返回改数据上一次提交的快照版本,供MySQL Server层判断是否命中,如果命中了才会交给InnoDB锁等待。
如果是RR隔离级别,一般情况下MySQL是不能这样优化的,除非设置了innodb_locks_unsafe_for_binlog
参数,这时也会提前释放锁,并且不加GAP锁,这就是所谓的semi-consistent read,关于semi-consistent read可以参考这里。
# 事务A
1 2 3 4 5 6 7 8 |
mysql> set tx_isolation = 'read-committed'; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> delete from tab_no_index where id = 1; Query OK, 1 rows affected (0.00 sec) |
查看锁结构:
1 2 3 4 5 |
---TRANSACTION 35705, ACTIVE 9 sec 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1 MySQL thread id 1532, OS thread handle 123145316274176, query id 86486 localhost root TABLE LOCK table `test`.`tab_no_index` trx id 35705 lock mode IX RECORD LOCKS space id 360 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`tab_no_index` trx id 35705 lock_mode X locks rec but not gap |
2个锁结构,其中一个意向锁,一个行锁。同样在GEN_CLUST_INDEX索引上锁,但是显示说不是一个gap锁,证明没有加Next-key lock,与在RR隔离级别下是不一样的。
# 事务B
1 2 3 4 5 6 7 8 9 10 |
myslq> delete from tab_no_index where id = 99; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> update tab_no_index set id=2 where id=100; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> insert into tab_no_index select 99,99; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 |
然后在另一个事务执行delete无法执行,而update和insert却可以。具体delete操作产生锁等待的原因还不太清楚。
三、InnoDB中锁的实现机制
1. 页锁对象 + 位图的实现方式
InnoDB中锁是根据页的组织形式进行管理的,行锁在InnoDB中的定义如下:
1 2 3 4 5 |
struct lock_rec_struct { ulint space ulint page_no ulint n_bits } |
其中space/page_no可以唯一决定一个页,nbits是一个位图。因此要查看某行记录是否上锁,只需要根据space/page_no找到对应的页,然后根据位图中对应位置是否是1来决定此行记录是否上锁。而给某条记录上锁,首先查看记录所在页是否已经有锁对象,如果锁对象已经存在,则将位图上对应位置置1。如果不存在,则生成一个锁对象,然后将位图对应位置置1。
这种锁的实现机制可以最大程度地重用锁对象,节省系统资源,且不存在锁升级的问题。可想而知,如果每个行锁都生成一个锁对象,将会导致严重的性能损耗,比如接近于全表扫描的查询就会生成大量的锁对象,内存开销将会很大。位图的方式很好地避免了这个问题。
2. 通过事务或(space, page_no)再Hash的方式组织页锁对象
InnoDB提供了两种方式对行锁进行访问:
通过事务中的trx_t变量访问。一个事务可能在不同页上有多个行锁,因此需要变量trx_locks将一个事务中的所有行锁信息进行链接,这样就可以很快地查看一个事务中的所有锁对象。
通过space/page_no访问。InnoDB提供了一个全局变量lock_sys_struct来方便查询行锁信息,lock_sys_struct包含一个HashTable,Hash的key是space/page_no,value是锁对象lock_rec_struct。
<延伸>
MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁
目前为止,MySQL官方默认隔离级别还是RR,但生产中基本都是RC+ROW标配。那么RR与RC到底哪种隔离级别更好,及各自性能又如何呢?其实官方也有一篇针对隔离级别的比较,可以看一看:MySQL Performance : Impact of InnoDB Transaction Isolation Modes in MySQL 5.7