mysql数据库版本:5.7,事务隔离级别是默认的RR(Repeatable-Read),采⽤innodb引擎。
假设存在test表:
CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`a` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `a` (`a`)
)
表的结构很简单,一个主键id,另一个唯一索引a。表里的数据如下:
mysql> select * from test;
+----+------+
| id | a |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 4 | 4 |
+----+------+
3 rows in set (0.00 sec)
出现死锁的操作如下:
步骤 | 事务1 | 事务2 |
---|---|---|
1 | begin | |
2 | delete from test where a = 2; | |
3 | begin | |
4 | delete from test where a = 2; (事务1卡住) | |
5 | 提示出现死锁:ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction | insert into test (id, a) values (10, 2); |
然后我们可以通过 SHOW ENGINE INNODB STATUS; 来查看死锁日志:
<