关于spring:记一次死锁分析过程

### 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/ref…

 有条件的看原文


没条件的看这 咱们只看读提交局部：对于 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/ref…

 有条件的看原文去

 没条件的持续：以下示例阐明了锁定申请将导致死锁时如何产生谬误。该示例波及两个客户端 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！！！还买什么课啊！！看官网干啥！愣着啊！！

关于spring:记一次死锁分析过程

1. 首先是要理解一些除了增删改查之外的数据库基础知识

2. 思考

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）