Java高级工程师必备知识！为什么你的insert就死锁了

最新推荐文章于 2023-10-07 11:04:11 发布

Android学习小分队

最新推荐文章于 2023-10-07 11:04:11 发布

阅读量648

点赞数

分类专栏：程序员文章标签：后端 java 面试

本文链接：https://blog.csdn.net/m0_56144365/article/details/119391872

版权

程序员专栏收录该内容

937 篇文章 3 订阅

订阅专栏

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

By default, InnoDB operates in REPEATABLE READ transaction isolation level and with the innodb_locks_unsafe_for_binlog system variable disabled. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows。

默认情况下，innodb使用next-key locks来锁定记录。但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

插入意向锁(Insert Intention Locks)

Gap Lock中存在一种插入意向锁（Insert Intention Lock），在insert操作时产生。在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This 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. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

注：插入意向锁并非意向锁，而是一种特殊的间隙锁。

4. 行锁的兼容矩阵⁴

表注：横向是已经持有的锁，纵向是正在请求的锁。

由于S锁和S锁是完全兼容的，因此在判别兼容性时只考虑持有的锁与请求的锁是这三种组合情形：X、S和S、X和X、X。另外，需要提醒注意的是进行兼容判断也只是针对于加锁涉及的行有交集的情形。

分析兼容矩阵可以得出如下几个结论：

INSERT操作之间不会有冲突。
GAP,Next-Key会阻止Insert。
GAP和Record,Next-Key不会冲突
Record和Record、Next-Key之间相互冲突。
已有的Insert锁不阻止任何准备加的锁。

5. 自增锁(AUTO-INC Locks)

AUTO-INC锁是一种特殊的表级锁，发生涉及AUTO_INCREMENT列的事务性插入操作时产生。

官方解释如下³：

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

四、insert加锁过程

官方文档⁶对于insert加锁的描述如下：

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This 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. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.

简单的insert会在insert的行对应的索引记录上加一个排它锁，这是一个record lock，并没有gap，所以并不会阻塞其他session在gap间隙里插入记录。

不过在insert操作之前，还会加一种锁，官方文档称它为insertion intention gap lock，也就是意向的gap锁。这个意向gap锁的作用就是预示着当多事务并发插入相同的gap空隙时，只要插入的记录不是gap间隙中的相同位置，则无需等待其他session就可完成，这样就使得insert操作无须加真正的gap lock。假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

假设发生了一个唯一键冲突错误，那么将会在重复的索引记录上加读锁。当有多个session同时插入相同的行记录时，如果另外一个session已经获得该行的排它锁，那么将会导致死锁。

思考：Insert Intention Locks作用

Insert Intention Locks的引入，我理解是为了提高数据插入的并发能力。如果没有Insert Intention Locks的话，可能就需要使用Gap Locks来代替。

五、insert死锁场景分析

接下来，带大家看几个与insert相关的死锁场景。

1. duplicate key error引发的死锁

这个场景主要发生在两个以上的事务同时进行唯一键值相同的记录插入操作。

表结构

``CREATE TABLEaa(idint(10) unsigned NOT NULL COMMENT ‘主键’,namevarchar(20) NOT NULL DEFAULT ‘’ COMMENT ‘姓名’,ageint(11) NOT NULL DEFAULT ‘0’ COMMENT ‘年龄’,stageint(11) NOT NULL DEFAULT ‘0’ COMMENT ‘关卡数’,PRIMARY KEY (id),UNIQUE KEYudx_name(name),KEYidx_stage(stage)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表数据

``CREATE TABLE `aa` (`id` int(10) unsigned NOT NULL COMMENT '主键',`name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',`stage` int(11) NOT NULL DEFAULT '0' COMMENT '关卡数',PRIMARY KEY (`id`),UNIQUE KEY `udx_name` (`name`),KEY `idx_stage` (`stage`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

事务执行时序表

如果T1未rollback，而是commit的话，T2和T3会报唯一键冲突：ERROR 1062 (23000): Duplicate entry ‘6’ for key ‘PRIMARY’

事务锁占用情况

T1 rollback前，各事务锁占用情况：

mysql> select * from information_schema.innodb_locks;
+--------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| 36729:24:3:7 | 36729 | S | RECORD | `test`.`aa` | PRIMARY | 24 | 3 | 7 | 6 |
| 36727:24:3:7 | 36727 | X | RECORD | `test`.`aa` | PRIMARY | 24 | 3 | 7 | 6 |
| 36728:24:3:7 | 36728 | S | RECORD | `test`.`aa` | PRIMARY | 24 | 3 | 7 | 6 |
+--------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+

注：mysql有自己的一套规则来决定T2与T3哪个进行回滚，本文不做讨论。

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-07-21 19:34:23 700000a3f000
*** (1) TRANSACTION:
TRANSACTION 36728, ACTIVE 199 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 13, OS thread handle 0x700000b0b000, query id 590 localhost root update
insert into aa values(6, 'test', 12, 3)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 3 n bits 80 index `PRIMARY` of table `test`.`aa` trx id 36728 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 36729, ACTIVE 196 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 14, OS thread handle 0x700000a3f000, query id 591 localhost root update
insert into aa values(6, 'test', 12, 3)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 24 page no 3 n bits 80 index `PRIMARY` of table `test`.`aa` trx id 36729 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 3 n bits 80 index `PRIMARY` of table `test`.`aa` trx id 36729 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)

死锁成因

T1回滚释放索引id=6上的排他记录锁(LOCK_X | LOCK_REC_NOT_GAP)，T2和T3都要请求索引id=6上的排他记录锁(LOCK_X | LOCK_REC_NOT_GAP)。由于X锁与S锁互斥，T2和T3都等待对方释放S锁。于是，死锁便产生了。

如果此场景下，只有两个事务T1与T2或者T1与T3，则不会引发如上死锁情况产生。

思考

为什么发现重复主键冲突的时候，要将事务请求的X锁转成S锁？ (比较牵强的)个人理解，跟插入意向锁类型，也是为了提高插入的并发效率。
插入前请求插入意向锁的作用？个人认为，通过兼容矩阵来分析，Insert Intention Locks是为了减少插入时的锁冲突。

2. GAP与Insert Intention冲突引发的死锁

表结构

``CREATE TABLE `t` (`a` int(11) NOT NULL,`b` int(11) DEFAULT NULL,PRIMARY KEY (`a`),KEY `idx_b` (`b`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表数据

``mysql> select * from t;
+----+------+
| a | b |
+----+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 11 | 22 |
+----+------+

事务执行时序表

事务锁占用情况

T2 insert前，各事务锁占用情况：

mysql> select * from information_schema.innodb_locks;
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 36831:25:4:5 | 36831 | X,GAP | RECORD | `test`.`t` | idx_b | 25 | 4 | 5 | 22, 11 |
| 36832:25:4:5 | 36832 | X,GAP | RECORD | `test`.`t` | idx_b | 25 | 4 | 5 | 22, 11 |
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+

死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-07-28 12:28:34 700000a3f000
*** (1) TRANSACTION:
TRANSACTION 36831, ACTIVE 17 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 38, OS thread handle 0x700000b0b000, query id 953 localhost root update
insert into t values (4,5)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25 page no 4 n bits 72 index `idx_b` of table `test`.`t` trx id 36831 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000016; asc ;;
1: len 4; hex 8000000b; asc ;;
*** (2) TRANSACTION:
TRANSACTION 36832, ACTIVE 13 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 39, OS thread handle 0x700000a3f000, query id 954 localhost root update
insert into t values (4,5)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 25 page no 4 n bits 72 index `idx_b` of table `test`.`t` trx id 36832 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000016; asc ;;
1: len 4; hex 8000000b; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 36832 lock mode S locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 6; hex 000000008fdf; asc ;;
2: len 7; hex 8d000001d00110; asc ;;
3: len 4; hex 80000005; asc ;;
*** WE ROLL BACK TRANSACTION (2)

死锁成因

事务T1执行查询语句，在索引b=6上加排他Next-key锁(LOCK_X | LOCK_ORDINARY)，会锁住idx_b索引范围(4, 22)。事务T2执行查询语句，在索引b=8上加排他Next-key锁(LOCK_X | LOCK_ORDINARY)，会锁住idx_b索引范围(4, 22)。由于请求的GAP与已持有的GAP是兼容的，因此，事务T2在idx_b索引范围(4, 22)也能加锁成功。

事务T1执行插入语句，会先加排他Insert Intention锁。由于请求的Insert Intention锁与已有的GAP锁不兼容，则事务T1等待T2释放GAP锁。事务T2执行插入语句，也会等待T1释放GAP锁。于是，死锁便产生了。

注：LOCK_ORDINARY拥有LOCK_GAP一部分特性。

思考：Insert Intention锁在加哪级索引上？

这个排他锁加在PK上，还是二级索引上？

六、课后思考

无主键的加锁过程无PK时，会创建一个隐式聚簇索引。加锁在这个隐式聚簇索引会有什么不同？
复合索引加锁过程
多条件(where condition)加锁过程
隐式锁与显式锁，隐式锁什么情况下会转换成显式锁
如果插入意向锁不阻止任何锁，这个锁还有必要存在吗？目前看到的作用是，通过加锁的方式来唤醒等待线程。但这并不意味着，被唤醒后可以直接做插入操作了。需要再次判断是否有锁冲突。

七、补充知识

1. 查看事务隔离级别

SELECT @@global.tx_isolation; SELECT @@session.tx_isolation; SELECT @@tx_isolation;

最后

金三银四到了，送上一个小福利！

资料领取方式：点击这里蓝色传送门

专题+大厂.jpg

@@session.tx_isolation; SELECT @@tx_isolation;

最后

金三银四到了，送上一个小福利！

资料领取方式：点击这里蓝色传送门

[外链图片转存中…(img-ycGg7s0k-1628077965458)]

[外链图片转存中…(img-4jvCjNYq-1628077965460)]

[外链图片转存中…(img-HSsM4N5d-1628077965461)]

Android学习小分队

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
Java高级工程师必备知识！为什么你的insert就死锁了

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.By default, InnoDB operates in REPEATABLE READ transaction isolation level and with the innodb_locks_unsafe_for_binlog system variable.
复制链接

扫一扫