转自 58沈剑 架构师之路 挖坑,InnoDB的七种锁 和 插入InnoDB自增列,居然是表锁? 以及 尹发条地精 InnoDB 的意向锁有什么作用?有修改
总的来说,InnoDB共有七种类型的锁:
(1)共享/排它锁(Shared and Exclusive Locks)
(2)意向锁(Intention Locks)
(3)记录锁(Record Locks)
(4)间隙锁(Gap Locks)
(5)临键锁(Next-key Locks)
(6)插入意向锁(Insert Intention Locks)
(7)自增锁(Auto-inc Locks)
1. 共享/排它锁(Shared and Exclusive Locks)
《InnoDB并发为何这么高?》一文介绍了通用的共享/排它锁,在InnoDB里当然也实现了标准的行级锁(row-level locking),共享/排它锁:
(1)事务拿到某一行记录的共享S锁,才可以读取这一行;
select * from t where id>2 lock in share mode;
(2)事务拿到某一行记录的排它X锁,才可以修改或者删除这一行;
select * from t where id>2 for update;
其兼容互斥表如下:
S | X | |
---|---|---|
S | 兼容 | 互斥 |
X | 互斥 | 互斥 |
即:
(1)多个事务可以拿到一把S锁,读读可以并行;
(2)而只有一个事务可以拿到X锁,写写/读写必须互斥;
共享/排它锁的潜在问题是,不能充分的并行,解决思路是数据多版本,具体思路在《InnoDB并发为何这么高?》里介绍过,这里不再深入展开。
2. 意向锁(Intention Locks)
InnoDB支持多粒度锁(multiple granularity locking),它允许行级锁与表级锁共存,实际应用中,InnoDB使用的是意向锁。
意向锁是指,未来的某个时刻,事务可能要加共享/排它锁了,先提前声明一个意向。
意向锁有这样一些特点:
(1)首先,意向锁,是一个表级别的锁(table-level locking);
(2)意向锁分为:
意向共享锁(intention shared lock, IS),它预示着,事务有意向对表中的某些行加共享S锁
意向排它锁(intention exclusive lock, IX),它预示着,事务有意向对表中的某些行加排它X锁
举个例子:
select ... lock in share mode,要设置IS锁;
select ... for update,要设置IX锁;
(3)意向锁协议(intention locking protocol)并不复杂:
事务要获得某些行的S锁,必须先获得表的IS锁
事务要获得某些行的X锁,必须先获得表的IX锁
(4)由于意向锁仅仅表明意向,它其实是比较弱的锁,意向锁之间并不相互互斥,而是可以并行,其兼容互斥表如下:
IS | IX |
---|---|
IS | 兼容 |
IX | 兼容 |
(5)额,既然意向锁之间都相互兼容,那其意义在哪里呢?它会与共享锁/排它锁互斥,其兼容互斥表如下:
S | X | |
---|---|---|
IS | 兼容 | 互斥 |
IX | 互斥 | 互斥 |
画外音:排它锁是很强的锁,不与其他类型的锁兼容。这也很好理解,修改和删除某一行的时候,必须获得强锁,禁止这一行上的其他并发,以保障数据的一致性。
解释:1
- 在MySQL中有表锁,LOCK TABLE my_table_name READ; 用读锁锁表,会阻塞其他事务修改表数据。LOCK TABLE my_table_name WRITE; 用写锁锁表,会阻塞其他事务读和写。
- InnoDB引擎又支持行锁,行锁分为共享锁,一个事务对一行的共享只读锁。排它锁,一个事务对一行的排他读写锁。
- 这两中类型的锁共存的问题
考虑这个例子:
事务A锁住了表中的一行,让这一行只能读,不能写。之后,事务B申请整个表的写锁。如果事务B申请成功,那么理论上它就能修改表中的任意一行,这与A持有的行锁是冲突的。数据库需要避免这种冲突,就是说要让B的申请被阻塞,直到A释放了行锁。数据库要怎么判断这个冲突呢?
- step1:判断表是否已被其他事务用表锁锁表
- step2:判断表中的每一行是否已被行锁锁住。注意step2,这样的判断方法效率实在不高,因为需要遍历整个表。
于是就有了意向锁。在意向锁存在的情况下,事务A必须先申请表的意向共享锁,成功后再申请一行的行锁。在意向锁存在的情况下,上面的判断可以改成
- step1:不变
- step2:发现表上有意向共享锁,说明表中有些行被共享行锁锁住了,因此,事务B申请表的写锁会被阻塞。
注意:申请意向锁的动作是数据库完成的,就是说,事务A申请一行的行锁的时候,数据库会自动先开始申请表的意向锁,不需要我们程序员使用代码来申请。
总结一下就是:
IX,IS是表级锁,不会和行级的X,S锁发生冲突。只会和表级的X,S发生冲突
3. 记录锁(Record Locks)
记录锁,它封锁索引记录,例如:
select * from t where id=1 for update;
它会在id=1的索引记录上加锁,以阻止其他事务插入,更新,删除id=1的这一行。
需要说明的是:
select * from t where id=1;
则是快照读(SnapShot Read),它并不加锁。
4. 间隙锁(Gap Locks)
间隙锁,它封锁索引记录中的间隔,或者第一条索引记录之前的范围,又或者最后一条索引记录之后的范围。
InnoDB,RR:
t(id PK, name KEY, sex, flag);
表中有四条记录:
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B
这个SQL语句
select * from t
where id between 8 and 15
for update;
会封锁区间,以阻止其他事务插入id=5~+infinity的记录。
画外音:
为什么要阻止?
如果能够插入成功,头一个事务执行相同的SQL语句,会发现结果集多出了一条记录,即幻影数据。
间隙锁的主要目的,就是为了防止其他事务在间隔中插入数据,以导致“不可重复读”
。
如果把事务的隔离级别降级为读提交(Read Committed, RC),间隙锁则会自动失效。
5. 临键锁(Next-Key Locks)
临键锁,是记录锁与间隙锁的组合,它的封锁范围,既包含索引记录,又包含索引区间。
更具体的,临键锁会封锁索引记录本身,以及索引记录之前的区间。
如果一个会话占有了索引记录R的共享/排他锁,其他会话不能立刻在R之前的区间插入新的索引记录。
画外音:原文是说
If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.
例子:
create table w (
id int primary key,
name varchar(20),
key name_idx (name)
);
+----+------+
| id | name |
+----+------+
| 1 | m |
| 2 | m |
| 3 | w |
| 4 | w |
+----+------+
事务1:
begin;
select * from v where name = 'w' for update;
间隙区间是
[-∞ ~ m),[m ~ w),[w ~ +∞],从w向前找到上一个区间和下一个区间连在一起就是锁定区域:[m ~ +∞]
事务2:
insert into w values(5, 'm'); /* 阻塞 */
insert into w values(5, 'n'); /* 阻塞 */
insert into w values(5, 'w'); /* 阻塞 */
insert into w values(5, 'z'); /* 阻塞 */
insert into w values(5, 'a'); /* ok */
如果事务1锁住不存在的记录:
begin;
select * from v where name = 'n' for update;
间隙区间是
[-∞ ~ m),[m ~ w),[w ~ +∞],从n向前找到上一个区间就是锁定区域:[m ~ w)
事务2:
insert into w values(5, 'm'); /* 阻塞 */
insert into w values(5, 'n'); /* 阻塞 */
insert into w values(5, 'v'); /* 阻塞 */
insert into w values(5, 'l'); /* ok */
insert into w values(6, 'w'); /* ok */
总结:
- 如果where 过索引的条件的记录是范围(如between … and 或是 大于小于等),会使用临键锁
- 如果where 过唯一索引的条件是等于比较,不存在也会使用临键锁
- 如果where 过唯一索引的条件是等于比较,且结果存在,使用记录锁
- 临键锁的主要目的,也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC,临键锁则也会失效
6. 插入意向锁(Insert Intention Locks)
对已有数据行的修改与删除,必须加强互斥锁X锁,那对于数据的插入,是否还需要加这么强的锁,来实施互斥呢?插入意向锁,孕育而生。
插入意向锁,是间隙锁(Gap Locks)的一种(所以,也是实施在索引上的),它是专门针对insert操作的。
它的玩法是:
多个事务,在同一个索引,同一个范围区间插入记录时,如果插入的位置不冲突,不会阻塞彼此。
画外音:官网的说法是
Insert Intention 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.
这样,之前挖坑的例子,就能够解答了。
在MySQL,InnoDB,RR下:
t(id unique PK, name);
数据表中有数据:
10, shenjian
20, zhangsan
30, lisi
事务A先执行,在10与20两条记录中插入了一行,还未提交:
insert into t values(11, xxx);
事务B后执行,也在10与20两条记录中插入了一行:
insert into t values(12, ooo);
(1)会使用什么锁?
(2)事务B会不会被阻塞呢?
回答:虽然事务隔离级别是RR,虽然是同一个索引,虽然是同一个区间,但插入的记录并不冲突,故这里:
使用的是插入意向锁
并不会阻塞事务B
7. 自增锁(Auto-inc Locks)
原文对自增锁的理解似乎有问题,自增锁是表锁,但局限于insert语句级别,不是事务级别
。请参考mysql官方文档:
https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-increment-lock-modes