关于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.思考

评论

发表回复取消回复

更多文章

DDN HPC 存储硬件架构设计深度分析

探秘IO500：从Lustre并行文件系统出发，开启HPC存储性能新征程

苹果iOS打包的ipa应用无法安装？一篇文章带你了解可能的原因及排查方法

图解Golang：从零开始实现简易版过期LRU缓存

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

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

2.思考

评论

发表回复 取消回复

更多文章

DDN HPC 存储硬件架构设计深度分析

探秘IO500：从Lustre并行文件系统出发，开启HPC存储性能新征程

苹果iOS打包的ipa应用无法安装？一篇文章带你了解可能的原因及排查方法

图解Golang：从零开始实现简易版过期LRU缓存

发表回复取消回复