为了故事的顺利发展,我们需要建一个表:
CREATE TABLE hero (
id INT,
name VARCHAR(100),
country varchar(100),
PRIMARY KEY (id),
KEY idx_name (name)
) Engine=InnoDB CHARSET=utf8;
我们为hero表的id列创建了聚簇索引,为name列创建了一个二级索引。这个hero表主要是为了存储三国时的一些英雄,我们向表中插入一些记录:
INSERT INTO hero VALUES
(1, 'l刘备', '蜀'),
(3, 'z诸葛亮', '蜀'),
(8, 'c曹操', '魏'),
(15, 'x荀彧', '魏'),
(20, 's孙权', '吴');
现在表中的数据就是这样的:
mysql> SELECT * FROM hero;
+----+------------+---------+
| id | name | country |
+----+------------+---------+
| 1 | l刘备 | 蜀 |
| 3 | z诸葛亮 | 蜀 |
| 8 | c曹操 | 魏 |
| 15 | x荀彧 | 魏 |
| 20 | s孙权 | 吴 |
+----+------------+---------+
5 rows in set (0.00 sec)
准备工作就做完了。
我们先创建一个发生死锁的情景,在Session A
和Session B
中分别执行两个事务,具体情况如下:
我们分析一下:
Session A
中的事务先对hero
表聚簇索引的id
值为1的记录加了一个X型正经记录锁
。Session B
中的事务对hero
表聚簇索引的id
值为3的记录加了一个X型正经记录锁
。Session A
中的事务接着想对hero
表聚簇索引的id
值为3的记录也加了一个X型正经记录锁
,但是与第④步中Session B
中的事务加的锁冲突,所以Session A
进入阻塞状态,等待获取锁。Session B
中的事务想对hero
表聚簇索引的id
值为1的记录加了一个X型正经记录锁
,但是与第③步中Session A
中的事务加的锁冲突,而此时Session A
和Session B
中的事务循环等待对方持有的锁,死锁发生,被MySQL
服务器的死锁检测机制检测到了,所以选择了一个事务进行回滚,并向客户端发送一条消息:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction以上是我们从语句加了什么锁的角度出发来进行死锁情况分析的,但是实际应用中我们可能压根儿不知道到底是哪几条语句产生了死锁,我们需要根据MySQL
在死锁发生时产生的死锁日志来逆向定位一下到底是什么语句产生了死锁,从而再优化我们的业务。
设计InnoDB
的大叔给我们提供了SHOW ENGINE INNODB STATUS
命令来查看关于InnoDB存储引擎的一些状态信息,其中就包括了系统最近一次发生死锁时的加锁情况。在上边例子中的死锁发生时,我们运行一下这个命令:
mysql> SHOW ENGINE INNODB STATUS\G
...省略了好多其他信息
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-06-20 13:39:19 0x70000697e000
*** (1) TRANSACTION:
TRANSACTION 30477, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s)
MySQL thread id 2, OS thread handle 123145412648960, query id 46 localhost 127.0.0.1 root statistics
select * from hero where id = 3 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30477 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000000007517; asc u ;;
2: len 7; hex 80000001d0011d; asc ;;
3: len 10; hex 7ae8afb8e8919be4baae; asc z ;;
4: len 3; hex e89c80; asc ;;
*** (2) TRANSACTION:
TRANSACTION 30478, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1160, 2 row lock(s)
MySQL thread id 3, OS thread handle 123145412927488, query id 47 localhost 127.0.0.1 root statistics
select * from hero where id = 1 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30478 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000000007517; asc u ;;
2: len 7; hex 80000001d0011d; asc ;;
3: len 10; hex 7ae8afb8e8919be4baae; asc z ;;
4: len 3; hex e89c80; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30478 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000007517; asc u ;;
2: len 7; hex 80000001d00110; asc ;;
3: len 7; hex 6ce58898e5a487; asc l ;;
4: len 3; hex e89c80; asc ;;
*** WE ROLL BACK TRANSACTION (2)
------------
...省略了好多其他信息
我们只关心最近发生的死锁信息,所以就把以LATEST DETECTED DEADLOCK
这一部分给单独提出来分析一下。下边我们就逐行看一下这个输出的死锁日志都是什么意思:
0x70000697e000
表示的操作系统为当前session分配的线程的线程id。Session A
中的事务为2条记录生成了锁结构,但是其中有一条记录上的X型正经记录锁
(rec but not gap)并没有获取到,没有获取到锁的这条记录的位置是:表空间ID为151,页号为3,heap_no为2。当然,设计InnoDB
的大叔还贴心的给出了这条记录的详细情况,它的主键值为80000003
,这其实是InnoDB内部存储使用的格式,其实就代表数字3
,也就是该事务在等待获取hero
表聚簇索引主键值为3
的那条记录的X型
正经记录锁。Session B
中的事务获取了hero
表聚簇索引主键值为3
的记录的X型正经记录锁
,等待获取hero
表聚簇索引主键值为1
的记录的X型正经记录锁
(隐含的意思就是这个hero
表聚簇索引主键值为1
的记录的X型正经记录锁
已经被SESSION A
中的事务获取到了)。Session B
中的那个事务。SESSION A
发生阻塞的语句是:
select * from hero where id = 3 for update
SESSION B
发生阻塞的语句是:
select * from hero where id = 1 for update
然后切记:到自己的业务代码中找出这两条语句所在事务的其他语句。SESSION A
获取了hero
表聚簇索引id
值为1的记录的X型正经记录锁
(这其实是从SESSION B
正在等待的锁中获取的),查看SESSION A
中的语句,发现是下边这个语句造成的(对照着语句加锁分析那三篇文章):
select * from hero where id = 1 for update;
还有SESSION B
获取了hero
表聚簇索引id
值为3的记录的X型正经记录锁
,查看SESSION B
中的语句,发现是下边这个语句造成的(对照着语句加锁分析那三篇文章):
select * from hero where id = 3 for update;
然后看SESSION A
正在等待hero
表聚簇索引id
值为3的记录的X型正经记录锁
,这个是由于下边这个语句造成的:
select * from hero where id = 3 for update;
然后看SESSION B
正在等待hero
表聚簇索引id
值为1的记录的X型正经记录锁
,这个是由于下边这个语句造成的:
select * from hero where id = 1 for update;
然后整个死锁形成过程就根据死锁日志给还原出来了。