[转]MySQL中的七种锁

PunkLu 2020年02月10日 154次浏览
MySQL中的七种锁
本文整理自微信公众号架构师之路,是我看过的最好的讲技术的公众号,推荐。

原文来自该公众号2018年精选文章中的数据库篇中的几篇文章。链接:

架构师之路2018年精选文章

自增锁

一,案例说明

MySQL,InnoDB,默认的隔离级别(RR),假设有数据表:

t(id AUTO_INCREMENT, name);

数据表中有数据:

1, shenjian

2, zhangsan

3, lisi

事务A执行,还未提交

insert into t(name) values(xxx);

事务B执行:

insert into t(name) values(ooo);

问:事务B会不会被阻塞?

二,案例分析

InnoDB在RR隔离级别下,能解决幻读问题,上面这个案例中:

(1)事务A先执行insert,会得到一条(4, xxx)的记录,由于是自增列,故不用显示指定id为4,InnoDB会自动增长,注意此时事务并未提交;

(2)事务B后执行insert,假设不会被阻塞,那会得到一条(5, ooo)的记录;

此时,并未有什么不妥,但如果,

(3)事务A继续insert:

insert into t(name) values(xxoo);

会得到一条(6, xxoo)的记录。

(4)事务A再select:

select * from t where id>3;

得到的结果是:

4, xxx

6, xxoo

不可能查询到5的记录,再RR的隔离级别下,不可能读取到还未提交事务生成的数据。

咦,这对于事务A来说,就很奇怪了,对于AUTO_INCREMENT的列,连续插入了两条记录,一条是4,接下来一条变成了6,就像莫名其妙的幻影。

三,自增锁(Auto-inc Locks)

自增锁是一种特殊的表级别锁(table-level lock),专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况,如果一个事务正在往表中插入记录,所有其他事务的插入必须等待,以便第一个事务插入的行,是连续的主键值。

与此同时,InnoDB提供了innodb_autoinc_lock_mode配置,可以调节与改变该锁的模式与行为。

共享/排它锁

InnoDB里当然也实现了标准的行级锁(row-level locking),共享/排它锁:

(1)事务拿到某一行记录的共享S锁,才可以读取这一行;

(2)事务拿到某一行记录的排它X锁,才可以修改或者删除这一行;

兼容互斥表如下:

兼容性SX
S兼容互斥
X互斥互斥

即:

(1)多个事务可以拿到一把S锁,读读可以并行;

(2)而只有一个事务可以拿到X锁,写写/读写必须互斥;

共享/排它锁的潜在问题是,不能充分的并行,解决思路是数据多版本

意向锁

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)由于意向锁仅仅表明意向,它其实是比较弱的锁,意向锁之间并不相互互斥,而是可以并行,其兼容互斥表如下:

兼容性ISIX
IS兼容兼容
IX兼容兼容

(5)额,既然意向锁之间都相互兼容,那其意义在哪里呢?它会与共享锁/排它锁互斥,其兼容互斥表如下:

兼容性SX
IS兼容互斥
IX互斥互斥

排它锁是很强的锁,不与其他类型的锁兼容。这也很好理解,修改和删除某一行的时候,必须获得强锁,禁止这一行上的其他并发,以保障数据的一致性。

插入意向锁

对已有数据行的修改与删除,必须加强互斥锁X锁,那对于数据的插入,是否还需要加这么强的锁,来实施互斥呢?插入意向锁,孕育而生。

插入意向锁,是间隙锁(Gap Locks)的一种(所以,也是实施在索引上的),它是专门针对insert操作的。

它的玩法是:多个事务,在同一个索引,同一个范围区间插入记录时,如果插入的位置不冲突,不会阻塞彼此。

在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

思路总结

(1)InnoDB使用共享锁,可以提高读读并发;

(2)为了保证数据强一致,InnoDB使用强互斥锁,保证同一行记录修改与删除的串行性;

(3)InnoDB使用插入意向锁,可以提高插入并发;

记录锁

记录锁,它封锁索引记录,例如:

select * from t where id=1 for update;

它会在id=1的索引记录上加锁,以阻止其他事务插入,更新,删除id=1的这一行。

需要说明的是:

select * from t where id=1;

则是快照读(SnapShot Read),它并不加锁。

间隙锁

间隙锁,它封锁索引记录中的间隔,或者第一条索引记录之前的范围,又或者最后一条索引记录之后的范围。

依然是上面的例子,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=10的记录插入。

为什么要阻止id=10的记录插入?

如果能够插入成功,头一个事务执行相同的SQL语句,会发现结果集多出了一条记录,即幻影数据。

间隙锁的主要目的,就是为了防止其他事务在间隔中插入数据,以导致“幻读”。

如果把事务的隔离级别降级为读提交(Read Committed, RC),间隙锁则会自动失效。

临键锁

临键锁,是记录锁与间隙锁的组合,它的封锁范围,既包含索引记录,又包含索引区间。

更具体的,临键锁会封锁索引记录本身,以及索引记录之前的区间。

如果一个会话占有了索引记录R的共享/排他锁,其他会话不能立刻在R之前的区间插入新的索引记录。

依然是上面的例子,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

PK上潜在的临键锁为:

(-infinity, 1]

(1, 3]

(3, 5]

(5, 9]

(9, +infinity]

临键锁的主要目的,也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC,临键锁则也会失效。