VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 编程开发 > Java教程 >
  • 记一次死锁分析过程

 
### 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

有条件的看原文 

没条件的看这 我们只看读提交部分:

    对于UPDATEDELETE语句, 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)
    
第二个UPDATEInnoDB执行 “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
再之后就可以授予对另一个客户端的锁定请求,并从表中删除该行。

我只能说这例子跟我的一毛一样啊,早找到这篇文章我还查个屁的隔离级别。

 

来源:https://www.cnblogs.com/dabaieyangzhijidi/p/14166457.html



相关教程