Mysql锁机制 - 锁类型

最新推荐文章于 2023-09-14 17:52:44 发布

勇敢的菜鸡

最新推荐文章于 2023-09-14 17:52:44 发布

阅读量592

点赞数

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_39679639/article/details/127351187

版权

mysql 专栏收录该内容

13 篇文章 1 订阅

订阅专栏

Mysql锁机制 - 锁类型

InnoDB锁类型

共享锁(Shared Locks)和排它锁(Exclusive Locks)

InnoDB实现了两种标准类型的行锁，共享锁(S)和排它锁(X)

共享锁S: 持有某行共享锁的事务，可以读取该行数据
排它锁X: 持有某行排它锁的事务，可以更新或者删除该行数据

如果一个事务T1持有某行数据r的共享锁S，那么另一个事务T2对该行操作的表现为：

事务T2仍然可以获取共享锁S，此时两个事务T1和T2同时拥有r行的共享锁S === 共享锁互相不阻塞
事务T2获取r行的排它锁X将被阻塞 === 共享锁和排它锁互相阻塞

如果一个事务T1持有某行数据r的排它锁X，另一个事务T2既不能得到该行的共享锁，也不能得到排它锁，必须等到事务T1持有的排它锁释放 === 排它锁和共享锁/排它锁互相阻塞

意向锁(Intention Locks)

InnoDB支持行锁row locks和表锁table locks同时存在。比如LOCK TABLES ... WRITE会给指定表增加排它锁(exclusive lock)。为了支持锁住不同的粒度，InnoDB使用意向锁。意向锁是表锁，它表明一个事务待会要获取表中某行的行锁类型（S or X）。意向锁有两类：

意向共享锁(IS: Intention Shared Lock): 表明事务将会给个别行设置共享锁
意向排它锁(IX: Intention Exclusive lock): 表明事务将会给个别行设置排它锁

比如，SELECT ... LOCK IN SHARE MODE会设置意向共享锁IS; SELECT ... FOR UPDATE设置IX

意向锁协议如下：

一个事务获取某行共享锁(S)之前，必须先获取IS锁(或更强的锁)
一个事务获取某行排它锁(X)之前，必须先获取IX锁

表级锁兼容性总结如下：

	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	可共存	冲突	可共存
S	冲突	冲突	可共存	可共存
IS	冲突	可共存	可共存	可共存

意向锁不会阻塞任何事务(block anything)，除非是全表操作LOCK TABLES ... WRITE。意向锁的主要目的是表明谁在锁住某行，或者即将要锁住某行

意向锁在事务数据中的表现为：TABLE LOCK table test.t trx id 10080 lock mode IX

记录锁(Record Locks)

记录锁是一种加在索引上的锁。比如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 会加记录锁，防止其他事务对t.c1 = 10的行做任意插入、更新、或者删除操作

记录锁总是锁住索引记录，即使一张表没有定义任何索引。这种情况下，InnoDB会在聚簇索引clustered index上添加记录锁

记录锁在事务数据中的表现为：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

间隙锁(Gap Locks)

间隙锁加在两条索引记录之间的间隙，或者第一条记录前面的间隙，或者最后一条记录后面的间隙。比如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;语句，会阻塞其他事务插入c1 = 15的数据，不管是否已经存在c1 = 15的行。

间隙锁是保证数据库性能和并发性的中间方案，并且仅存在于某些隔离级别（RR）。

如果某列是唯一索引且正在查询某唯一值，此时不会添加间隙锁。但是，如果查询的是联合唯一索引中的部分列，仍然会添加唯一索引。比如，如果列id存在唯一索引，

SELECT * FROM child WEHRE id = 100 FOR UPDATE

此查询语句只会给id = 100的行添加记录锁，不会影响其他事务在id = 100前的间隙插入数据。如果id没有被索引，或者是非唯一索引，这条语句会在id = 100前的间隙加锁

间隙锁可以共生(co-exist)。事务A在一个间隙上持有间隙锁(gap S-lock)，另一个事务同时可以在这个间隙上同时持有间隙锁(gap X-lock)。间隙锁的唯一目的是防止其他事务往间隙中插入数据，共享间隙锁和排它间隙锁没有差别，互不冲突。

间隙锁可以禁用，使用隔离级别“读已提交(READ COMMITED)”，或者开启参数innodb_locks_unsafe_for_binlog可以显示禁用间隙锁。

临界锁(Next-Key locks)

临界锁是记录锁和该记录之前间隙锁的组合。

当InnoDB在查询或者遍历一个索引时，它会给所有遍历的行添加共享锁或者排它锁，这就是行级锁。因此，行级锁就是记录锁。临界锁(Next-key lock)就是记录锁加上该记录前的间隙锁。如果一个会话在R记录上持有共享锁或者排它锁，另一个会话不能在R之前的间隙插入数据。加入一个索引包含值为10,11,13,20，那么可能得临界锁范围是：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

小括号表示不包含该值，大括号表示包含该值左开右闭。InnoDB默认使用可重复读(REPEATABLE READ)的隔离级别，且使用临界锁防止幻读(Phantom rows)

临界锁在事务数据中的表现为：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

插入意向锁(Insert Intention Locks)

插入意向锁是INSERT语句中，在行写入之前添加的一种间隙锁。多个事务在写入同一个索引间隙时，如果他们插入的不是同一个位置，那么就不会相互阻塞。比如有两个索引记录为4和7，不同的事务分别准备写入数据5和6，他们都会在4-7上增加插入意向锁锁住这个间隙，但是不会相互阻塞，因为他们写入的是不同的行。

下面这个例子表明，事务在获取排它锁之前会先获取插入意向锁。ClientA新建两条行记录90和102：

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

此时ClientA使用排它间隙锁锁住了(100, 102]。ClientB开启一个事务，并尝试插入101。ClientB会被ClientA的间隙阻塞，等待获取排它锁。这个时候，ClientB获取了插入意向锁。

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

插入意向锁在事务数据中的表现为：

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...