提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
文章目录
- 案例1----简单insert
- 案例2----- on duplicate key update
- 案例3 ----- Replace into![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/4a87502e9b327bf91c131edef0d7db2c.png)
- 案例4-----间隙锁导致死锁
案例1----简单insert
- 简单insert之间的死锁
表结构
CREATE TABLE `emp1` (
`id` int(16) NOT NULL,
`name` varchar(16) DEFAULT NULL,
`age` int(16) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
设置环境
- 常看当前数据库的事务隔离级别: RR级别
SHOW VARIABLES LIKE ‘tx_isolation’;
- 设置: 手动提交事务
set autocommit = 0
- 打开3个会话
会话1,执行insert,不提交
BEGIN;
INSERT INTO emp1 (id,NAME,age) VALUES (4,'rose',28);
会话2,执行insert,由于id=4已被会话1上了行锁,所以被堵塞
BEGIN;
INSERT INTO emp1 (id,NAME,age) VALUES (4,'rose',28);
会话3,执行insert,由于id=4已被会话1上了行锁,所以被堵塞
BEGIN;
INSERT INTO emp1 (id,NAME,age) VALUES (4,'rose',28);
此时的事务锁等待信息:
SHOW VARIABLES LIKE ‘%innodb%’;
SET GLOBAL innodb_status_output_locks=ON;
SHOW ENGINE INNODB STATUS
继续------会话1执行rollback
会话2,显示insert成功
会话3,显示出现死锁
Deadlock found when trying to get lock; try restarting transaction
insert 插入前,会话需要先去拿S读锁,检查唯一性
分析死锁原因
- 会话1持有id=4的X锁 写锁;
- 会话2插入前,需要拿S锁检查唯一性,但是id=4已经上了X锁,所以等待;
- 会话3插入前,需要拿S锁检查唯一性,但是id=4已经上了X锁,所以等待;
- 当会话1回滚,id=4上的X锁释放,那么会话2和会话3同时获得了S锁,判断唯一性;
- 由于会话2和会话3都拿着id=4的读锁,所以各自都无法在id=4上面获取X锁,死锁出现。
有两个需要注意的点:
- 只有拿到行锁,才能检查唯一约束,例如当会话1拿着X锁时,会话2就没办法进行唯一约束判断。
- 死锁信息中,或者show engine innodb status\G的事务信息中,不一定会把事务持有锁的情况都显示,例如上面的死锁信息就没有显示第一个事务持有S锁,但实际是有的;
那么为什么最后 会显示会话2 插入成功了呢?
- Innodb检测到死锁之后,判断会话3 事务较小,让其回滚,释放S锁
- 此时会话2,拿到X写锁,插入成功
案例2----- on duplicate key update
表结构
mysql> select * from sam;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
+----+------+------+
3 rows in set (0.00 sec)
mysql> desc sam;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| col1 | int(11) | YES | UNI | NULL | |
| col2 | int(11) | YES | MUL | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
会话1
- 执行insert into sam values (4,3,3) on duplicate key update col1=4,不提交事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into sam values (4,3,3) on duplicate key update col1=4;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from sam;
+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 4 | 3 |
+----+------+------+
3 rows in set (0.00 sec)
会话2
- 执行insert into sam values (4,4,3) on duplicate key update col1=5,被会话1堵塞
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into sam values (4,4,3) on duplicate key update col1=5;
会话3
- 执行insert into sam values (5,4,3) on duplicate key update col1=5,被会话2堵塞
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into sam values (5,4,3) on duplicate key update col1=5;
此时的事务锁等待信息:
---TRANSACTION 69999, ACTIVE 4 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 140302983333632, query id 262 localhost sam update
insert into sam values (5,4,3) on duplicate key update col1=5
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 5 n bits 80 index uk_c1 of table `sam`.`sam` trx id 70007 lock_mode X waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 4; hex 80000003; asc ;;
------------------
---TRANSACTION 69998, ACTIVE 9 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 140302983603968, query id 260 localhost sam update
insert into sam values (4,4,3) on duplicate key update col1=5
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 5 n bits 80 index uk_c1 of table `sam`.`sam` trx id 70006 lock_mode X waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 4; hex 80000003; asc ;;
------------------
会话1执行rollback
- 会话1执行rollback
- 会话2,显示insert成功
- 会话3,显示出现死锁,
事务被回滚ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
死锁信息:
LATEST DETECTED DEADLOCK
------------------------
2020-04-28 22:47:21 0x7f9ad57b8700
*** (1) TRANSACTION:
TRANSACTION 69998, ACTIVE 40 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 140302983603968, query id 250 localhost sam update
insert into sam values (4,4,3) on duplicate key update col1=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 5 n bits 80 index uk_c1 of table `sam`.`sam` trx id 69998 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 69999, ACTIVE 9 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 140302983333632, query id 252 localhost sam update
insert into sam values (5,4,3) on duplicate key update col1=5
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 76 page no 5 n bits 80 index uk_c1 of table `sam`.`sam` trx id 69999 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;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 5 n bits 80 index uk_c1 of table `sam`.`sam` trx id 69999 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)
分析死锁过程
- 会话1持有主键上id=3的X锁,col1上唯一索引的id=3和id=4的X锁;
- 会话2在等col1上唯一索引col1=4的next-key锁;
- 会话3在等col1上唯一索引col1=4的next-key锁;
- 会话1回滚,所有X锁释放;
- 本来会话2和会话3都要拿到col1唯一索引上(3,4]这一个next-key锁,但会话1回滚后,col1=4就没了,所以会话2和会话3的锁就扩大了,变成(3,+∞]
- 会话2和会话3各自的next-key锁堵塞了各自的insert操作
这里也是需要注意两点:
- 死锁信息同样显示不全,应该是两个事务都持有lock_mode X,即(3,+∞]的next-key锁,但死锁信息只显示一个事务持有;
- insert into sam values (5,4,3) on duplicate key update col1=5,为什么在insert之前,不需要在col1的唯一索引上先拿S锁来检测唯一性呢?这里我猜由于这种insert语句比较特殊,本身就带有on duplicate来处理唯一键冲突,所以直接拿X锁在执行过程中来将唯一冲突处理掉。
案例3 ----- Replace into
案例4-----间隙锁导致死锁