记一次死锁分析过程
### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may involve com.cgd.order.dao.OrderSaleMapper.updateSaleOrderStatus-Inline
### The error occurred while setting parameters
### SQL: update t_order set ORDER_STATUS = ? where ORDER_ID = ?
小盆友，如果你在日志里看到这个是不是像我一样会有很多问号？？
我一个只会写增删改查sql的低层次程序员有了满奶子问号。
但是我相信啊：只要功夫深，李白碰到的老婆婆就能把铁杵磨成针。
1.首先是要了解一些除了增删改查之外的数据库基础知识
从极客上找了门MySQL实战，如果你也想买，请联系我，推荐人买有返现的。
这门课我是觉得很值，这两天为了解决这个死锁又读了一遍有关加锁的章节，发现了一条命令啊，这个命令会输出很多信息，有一节 LATESTDETECTED DEADLOCK，就是记录的最后一次死锁信息。
show engine innodb status;
我就试着执行了下，好巧不巧跟那天死锁日志里的sql一样，不过也说明这死锁问题挺频繁的
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-12-17 11:20:09 7fbe339f7700
//第一个事务
*** (1) TRANSACTION:
TRANSACTION 116609954, ACTIVE 1.214 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
LOCK BLOCKING MySQL thread id: 72830 block 64282
MySQL thread id 64282, OS thread handle 0x7fbe01f7d700, query id 1042364621 172.16.21.10 prod_orderdb updating
//以上乱七八糟的有事务的基础信息 大小 行数等等
//下边一行是死锁的其中一方的sql 解析一下ORDER_ID是表的主键
/* 276aedd616081752079448345e2ac7/0.1.3// */
update t_order set ORDER_STATUS = 4 where ORDER_ID = 234
//表示这个事务在等待的锁信息
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
//当前在等待表t_order上的 X锁， 主键为 8de0b6b3a773935b
RECORD LOCKS space id 1252 page no 10646 n bits 112 index `PRIMARY` of table `t_order` trx id 116609954 lock_mode X locks rec but not gap waiting
//n_fields 115 表示记录有115列
Record lock, heap no 10 PHYSICAL RECORD: n_fields 115; compact format; info bits 0
//第一列 基本就是主键了
0: len 8; hex 8de0b6b3a773935b; asc s [;;
1: len 6; hex 000006ef0a51; asc Q;;
2: len 7; hex 76000006f723b0; asc v # ;;
3: len 16; hex 32303230313231323030333139363331; asc 2020121200319631;;
4: len 4; hex 80000000; asc ;;
5: SQL NULL;
6: len 4; hex 80000002; asc ;;
7: SQL NULL;
8: len 4; hex 8000000b; asc ;;
.
..省略1xx行
.
//第二个事务信息
*** (2) TRANSACTION:
TRANSACTION 116609912, ACTIVE 7.886 sec fetching rows
mysql tables in use 1, locked 1
36461 lock struct(s), heap size 3241512, 306688 row lock(s), undo log entries 12
MySQL thread id 72830, OS thread handle 0x7fbe339f7700, query id 1042364593 172.16.21.0 prod_orderdb Searching rows for update
//同理第二个sql
update t_order set ORDER_STATUS = 4 where EXT_ID = '232SA'
//当前事务持有的锁
*** (2) HOLDS THE LOCK(S):
//持有N个S锁
RECORD LOCKS space id 1252 page no 10646 n bits 112 index `PRIMARY` of table `t_order` trx id 116609912 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 115; compact format; info bits 0
0: len 8; hex 8de0b6b3a7739342; asc s B;;
1: len 6; hex 000006f1aeff; asc ;;
2: len 7; hex 64000006ce20bb; asc d ;;
3: len 16; hex 32303230313231323030333139363036; asc 2020121200319606;;
4: len 4; hex 80000000; asc ;;
5: SQL NULL;
6: len 4; hex 80000002; asc ;;
7: SQL NULL;
.
.
.
//++++++++++
Record lock, heap no 10 PHYSICAL RECORD: n_fields 115; compact format; info bits 0
0: len 8; hex 8de0b6b3a773935b; asc s [;;
1: len 6; hex 000006ef0a51; asc Q;;
2: len 7; hex 76000006f723b0; asc v # ;;
3: len 16; hex 32303230313231323030333139363331; asc 2020121200319631;;
4: len 4; hex 80000000; asc ;;
5: SQL NULL;
.
.
.
//++++++++++
此处还省略N多 //++++++++++之间的行，同时持有很多行的锁
//等待的锁
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
//同样是等待X锁 主键为 8de0b6b3a773935b
RECORD LOCKS space id 1252 page no 10646 n bits 112 index `PRIMARY` of table `t_order` trx id 116609912 lock_mode X locks rec but not gap waiting
Record lock, heap no 10 PHYSICAL RECORD: n_fields 115; compact format; info bits 0
0: len 8; hex 8de0b6b3a773935b; asc s [;;
1: len 6; hex 000006ef0a51; asc Q;;
2: len 7; hex 76000006f723b0; asc v # ;;
3: len 16; hex 32303230313231323030333139363331; asc 2020121200319631;;
4: len 4; hex 80000000; asc ;;
5: SQL NULL;
.
.
.
//数据库选择回滚成本最小的一个事务进行回滚
*** WE ROLL BACK TRANSACTION (1)
2.思考
两条sql，对应两条数据，八竿子打不着的两条数据在更新的时候发生了死锁
update t_order set ORDER_STATUS = 4 where ORDER_ID = 234
update t_order set ORDER_STATUS = 4 where EXT_ID = '232SA'
当前问题所处环境整理：
- 数据库事务隔离级别：读提交（READ COMMITTED）
- 表：t_order（ORDER_ID ：主键； EXT_ID ：非主键，无索引，但唯一）
- 两条sql都是通过唯一条件筛选数据，但不是同一条数据
极客的课程肯定细致不到官网，所以我去官网搜了搜，因为英语不是很好，就搜了Lock关键字，把搜出来的文章挨个看了下，先看这个
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
有条件的看原文
没条件的看这 我们只看读提交部分：
对于UPDATE或 DELETE语句， InnoDB仅对其更新或删除的行持有锁。
MySQL评估WHERE条件后，将释放不匹配行的记录锁。
这大大降低了死锁的可能性，但是仍然可以发生。
对于UPDATE语句，如果某行已被锁定，则InnoDB 执行“semi-consistent”读取，
将最新的提交版本返回给MySQL，以便MySQL可以确定该行是否符合 WHERE条件 UPDATE。
如果该行匹配（必须更新），则MySQL会再次读取该行，这一次将InnoDB其锁定或等待对其进行锁定。
官网例子
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
在这种情况下，表没有索引，因此搜索和索引扫描使用隐藏的聚集索引进行记录锁定（请参见第15.6.2.1节“聚集索引和二级索引”），而不是使用索引列。
假设一个会话UPDATE使用以下语句执行 ：
# Session A
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3;
还假设第二个会话 UPDATE通过在第一个会话的语句之后执行以下语句来执行：
# Session B
UPDATE t SET b = 4 WHERE b = 2;
在InnoDB执行UPDATE，它首先为每一行获取一个排他锁，然后确定是否对其进行修改。如果InnoDB不修改该行，则释放锁。否则，InnoDB保留该锁直到事务结束。这会影响事务处理，如下所示。
使用默认REPEATABLE READ 隔离级别时:
第一个UPDATE将在其读取的每一行上获得一个写（x-lock）锁，并且不会释放其中的任何一个：
x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock
第二UPDATE个尝试获取任何锁的块（因为第一个更新在所有行上都保留了锁），并且直到第一个UPDATE提交或回滚时才继续执行：
x-lock(1,2); block and wait for first UPDATE to commit or roll back
使用READ COMMITTED则有不同：
第一个UPDATE将在读取的每一行上获取一个写（x-lock）锁，并为未修改的行释放x锁：
x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)
第二个UPDATE， InnoDB执行 “semi-consistent”读取，将读取的每一行的最新提交版本返回给MySQL，以便MySQL可以确定该行是否符合以下 WHERE条件 UPDATE：
x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lock
但是，如果WHERE条件包括索引列并InnoDB使用索引，则在获取和保留记录锁时仅考虑索引列。
看完了例子：虽然"semi-consistent读取"的解释还有点迷糊，但是我的死锁问题已经大致有了眉目。
我的表EXT_ID就是没有索引啊，explain了，确实是全表查的，也就是存在这个一个逐行加锁并释放锁的过程。
当然后来又发现了另一篇文档
https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlock-example.html
有条件的看原文去
没条件的继续 ：
以下示例说明了锁定请求将导致死锁时如何发生错误。该示例涉及两个客户端A和B。
首先，客户端A创建一个包含一行的表，然后开始事务。
在事务中，A通过S lock 下选择行来获得对行的锁定：
mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)
mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE i = 1 FOR SHARE;
+------+
| i |
+------+
| 1 |
+------+
接下来，客户端B开始事务并尝试从表中删除该行：
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM t WHERE i = 1;
删除操作需要一个X锁，但是它无法获得X锁，因为A目前持有S锁，两个锁不兼容 ，因此该请求进入针对行和客户端B块的锁请求队列中。
最后，客户端A还尝试从表中删除该行：
mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction
此处发生死锁是因为客户端A需要X锁才能删除该行。
但是，不能授予它X锁，因为客户端B已经有一个X锁定请求，并且正在等待客户端A释放其S锁定。
由于B事先要求锁，所以A持有的S 锁也不能升级X锁。
结果， InnoDB为其中一个客户端生成错误并释放其锁。客户端返回此错误：
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction
再之后就可以授予对另一个客户端的锁定请求，并从表中删除该行。
我只能说这例子跟我的一毛一样啊，早找到这篇文章我还查个屁的隔离级别。
官方文档还是牛B！！！还买什么课啊！！看官网干啥！愣着啊！！
本文由博客一文多发平台 OpenWrite 发布！
