当前位置：Gxlcms > 数据库问题 > MySQL一次死锁排查过程分析（双update）

MySQL一次死锁排查过程分析（双update）

时间：2021-07-01 10:21:17 帮助过：31人阅读

| int(11) unsigned | NO | PRI | NULL | auto_increment | | config_id | int(11) | NO | | NULL | | | place_code | varchar(16) | NO | | | | | stock_date | date | NO | | NULL | | | start_time | time | NO | | NULL | | | end_time | time | NO | | NULL | | | a_amount | int(11) | NO | | 0 | | | t_amount | int(11) | NO | | 0 | | | show_apps | varchar(512) | YES | | NULL | | | update_time | timestamp | YES | | NULL | | | create_time | timestamp | NO | | 0000-00-00 00:00:00 | | +------------------+------------------+------+-----+---------------------+----------------+

3.死锁日志分析

3.1 死锁日志如下

技术图片 
  1. <span style="color: #800080;">2020</span>-<span style="color: #800080;">07</span>-07T23:<span style="color: #800080;">36</span>:<span style="color: #800080;">18</span>.057866Z <span style="color: #800080;">2210220</span><span style="color: #000000;"> [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
  2. </span><span style="color: #800080;">2020</span>-<span style="color: #800080;">07</span>-07T23:<span style="color: #800080;">36</span>:<span style="color: #800080;">18</span>.057932Z <span style="color: #800080;">2210220</span><span style="color: #000000;"> [Note] InnoDB: 
  3. </span>*** (<span style="color: #800080;">1</span><span style="color: #000000;">) TRANSACTION:
  4. TRANSACTION </span><span style="color: #800080;">182336318</span>, ACTIVE <span style="color: #800080;">1</span><span style="color: #000000;"> sec fetching rows
  5. mysql tables </span><span style="color: #0000ff;">in</span> use <span style="color: #800080;">1</span>, locked <span style="color: #800080;">1</span><span style="color: #000000;">
  6. LOCK WAIT </span><span style="color: #800080;">133</span> lock struct(s), heap size <span style="color: #800080;">24784</span>, <span style="color: #800080;">32399</span> row lock(s), undo log entries <span style="color: #800080;">1</span><span style="color: #000000;">
  7. MySQL thread </span><span style="color: #0000ff;">id</span> <span style="color: #800080;">2210217</span>, OS thread handle <span style="color: #800080;">139920667432704</span>, query <span style="color: #0000ff;">id</span> <span style="color: #800080;">420859658</span> <span style="color: #800080;">10.11</span>.<span style="color: #800080;">100.12</span><span style="color: #000000;"> tc updating
  8. UPDATE c_place_time_stock SET a_amount</span>=(c_place_time_stock.a_amount - <span style="color: #800080;">2</span>), update_time=now() WHERE c_place_time_stock.place_code = <span style="color: #800000;">‘</span><span style="color: #800000;">A02</span><span style="color: #800000;">‘</span> AND c_place_time_stock.stock_date = <span style="color: #800000;">‘</span><span style="color: #800000;">2020-07-08</span><span style="color: #800000;">‘</span> AND c_place_time_stock.start_time <= <span style="color: #800000;">‘</span><span style="color: #800000;">08:00:00</span><span style="color: #800000;">‘</span> AND c_place_time_stock.end_time > <span style="color: #800000;">‘</span><span style="color: #800000;">08:00:00</span><span style="color: #800000;">‘</span>
  9. <span style="color: #800080;">2020</span>-<span style="color: #800080;">07</span>-07T23:<span style="color: #800080;">36</span>:<span style="color: #800080;">18</span>.058022Z <span style="color: #800080;">2210220</span> [Note] InnoDB: *** (<span style="color: #800080;">1</span><span style="color: #000000;">) WAITING FOR THIS LOCK TO BE GRANTED:
  10. RECORD LOCKS space </span><span style="color: #0000ff;">id</span> <span style="color: #800080;">207</span> page no <span style="color: #800080;">239</span> n bits <span style="color: #800080;">344</span> index PRIMARY of table `tcenter`.`c_place_time_stock` trx <span style="color: #0000ff;">id</span> <span style="color: #800080;">182336318</span><span style="color: #000000;"> lock_mode X waiting
  11. Record lock, heap no </span><span style="color: #800080;">228</span> PHYSICAL RECORD: n_fields <span style="color: #800080;">13</span>; compact format; <span style="color: #0000ff;">info</span> bits <span style="color: #800080;">0</span>
  12.  <span style="color: #800080;">0</span>: len <span style="color: #800080;">4</span><span style="color: #000000;">; hex 0000a188; asc     ;;
  13.  </span><span style="color: #800080;">1</span>: len <span style="color: #800080;">6</span>; hex 00000ade3b3d; asc     ;=<span style="color: #000000;">;;
  14.  </span><span style="color: #800080;">2</span>: len <span style="color: #800080;">7</span><span style="color: #000000;">; hex 76000018b80612; asc v      ;;
  15.  </span><span style="color: #800080;">3</span>: len <span style="color: #800080;">4</span><span style="color: #000000;">; hex 800002b4; asc     ;;
  16.  </span><span style="color: #800080;">4</span>: len <span style="color: #800080;">3</span>; hex <span style="color: #800080;">413237</span><span style="color: #000000;">; asc A27;;
  17.  </span><span style="color: #800080;">5</span>: len <span style="color: #800080;">3</span><span style="color: #000000;">; hex 8fc8e8; asc    ;;
  18.  </span><span style="color: #800080;">6</span>: len <span style="color: #800080;">3</span>; hex <span style="color: #800080;">808780</span><span style="color: #000000;">; asc    ;;
  19.  </span><span style="color: #800080;">7</span>: len <span style="color: #800080;">3</span><span style="color: #000000;">; hex 80f780; asc    ;;
  20.  </span><span style="color: #800080;">8</span>: len <span style="color: #800080;">4</span>; hex 800026b0; asc   &<span style="color: #000000;"> ;;
  21.  </span><span style="color: #800080;">9</span>: len <span style="color: #800080;">4</span>; hex <span style="color: #800080;">80002710</span>; asc   <span style="color: #800000;">‘</span><span style="color: #800000;"> ;;</span>
  22.  <span style="color: #800080;">10</span>: len <span style="color: #800080;">30</span>; hex 3631303136332c3630303031362c3631303136382c3634303036382c3631; asc <span style="color: #800080;">610163</span>,<span style="color: #800080;">600016</span>,<span style="color: #800080;">610168</span>,<span style="color: #800080;">640068</span>,<span style="color: #800080;">61</span>; (total <span style="color: #800080;">48</span><span style="color: #000000;"> bytes);
  23.  </span><span style="color: #800080;">11</span>: len <span style="color: #800080;">4</span><span style="color: #000000;">; hex 5f0506f1; asc _   ;;
  24.  </span><span style="color: #800080;">12</span>: len <span style="color: #800080;">4</span>; hex 5efe174a; asc ^<span style="color: #000000;">  J;;
  25. </span><span style="color: #800080;">2020</span>-<span style="color: #800080;">07</span>-07T23:<span style="color: #800080;">36</span>:<span style="color: #800080;">18</span>.059281Z <span style="color: #800080;">2210220</span> [Note] InnoDB: *** (<span style="color: #800080;">2</span><span style="color: #000000;">) TRANSACTION:
  26. TRANSACTION </span><span style="color: #800080;">182336317</span>, ACTIVE <span style="color: #800080;">1</span><span style="color: #000000;"> sec starting index read
  27. mysql tables </span><span style="color: #0000ff;">in</span> use <span style="color: #800080;">1</span>, locked <span style="color: #800080;">1</span>
  28. <span style="color: #800080;">9</span> lock struct(s), heap size <span style="color: #800080;">1136</span>, <span style="color: #800080;">28</span> row lock(s), undo log entries <span style="color: #800080;">16</span><span style="color: #000000;">
  29. MySQL thread </span><span style="color: #0000ff;">id</span> <span style="color: #800080;">2210220</span>, OS thread handle <span style="color: #800080;">139920666892032</span>, query <span style="color: #0000ff;">id</span> <span style="color: #800080;">420859667</span> <span style="color: #800080;">10.11</span>.<span style="color: #800080;">100.12</span><span style="color: #000000;"> tc updating
  30. UPDATE c_place_time_stock SET a_amount</span>=<span style="color: #800080;">2168</span>, update_time=now() WHERE c_place_time_stock.<span style="color: #0000ff;">id</span> = <span style="color: #800080;">41334</span>
  31. <span style="color: #800080;">2020</span>-<span style="color: #800080;">07</span>-07T23:<span style="color: #800080;">36</span>:<span style="color: #800080;">18</span>.059373Z <span style="color: #800080;">2210220</span> [Note] InnoDB: *** (<span style="color: #800080;">2</span><span style="color: #000000;">) HOLDS THE LOCK(S):
  32. RECORD LOCKS space </span><span style="color: #0000ff;">id</span> <span style="color: #800080;">207</span> page no <span style="color: #800080;">239</span> n bits <span style="color: #800080;">344</span> index PRIMARY of table `tcenter`.`c_place_time_stock` trx <span style="color: #0000ff;">id</span> <span style="color: #800080;">182336317</span><span style="color: #000000;"> lock_mode X locks rec but not gap
  33. Record lock, heap no </span><span style="color: #800080;">228</span> PHYSICAL RECORD: n_fields <span style="color: #800080;">13</span>; compact format; <span style="color: #0000ff;">info</span> bits <span style="color: #800080;">0</span>
  34.  <span style="color: #800080;">0</span>: len <span style="color: #800080;">4</span><span style="color: #000000;">; hex 0000a188; asc     ;;
  35.  </span><span style="color: #800080;">1</span>: len <span style="color: #800080;">6</span>; hex 00000ade3b3d; asc     ;=<span style="color: #000000;">;;
  36.  </span><span style="color: #800080;">2</span>: len <span style="color: #800080;">7</span><span style="color: #000000;">; hex 76000018b80612; asc v      ;;
  37.  </span><span style="color: #800080;">3</span>: len <span style="color: #800080;">4</span><span style="color: #000000;">; hex 800002b4; asc     ;;
  38.  </span><span style="color: #800080;">4</span>: len <span style="color: #800080;">3</span>; hex <span style="color: #800080;">413237</span><span style="color: #000000;">; asc A27;;
  39.  </span><span style="color: #800080;">5</span>: len <span style="color: #800080;">3</span><span style="color: #000000;">; hex 8fc8e8; asc    ;;
  40.  </span><span style="color: #800080;">6</span>: len <span style="color: #800080;">3</span>; hex <span style="color: #800080;">808780</span><span style="color: #000000;">; asc    ;;
  41.  </span><span style="color: #800080;">7</span>: len <span style="color: #800080;">3</span><span style="color: #000000;">; hex 80f780; asc    ;;
  42.  </span><span style="color: #800080;">8</span>: len <span style="color: #800080;">4</span>; hex 800026b0; asc   &<span style="color: #000000;"> ;;
  43.  </span><span style="color: #800080;">9</span>: len <span style="color: #800080;">4</span>; hex <span style="color: #800080;">80002710</span>; asc   <span style="color: #800000;">‘</span><span style="color: #800000;"> ;;</span>
  44.  <span style="color: #800080;">10</span>: len <span style="color: #800080;">30</span>; hex 3631303136332c3630303031362c3631303136382c3634303036382c3631; asc <span style="color: #800080;">610163</span>,<span style="color: #800080;">600016</span>,<span style="color: #800080;">610168</span>,<span style="color: #800080;">640068</span>,<span style="color: #800080;">61</span>; (total <span style="color: #800080;">48</span><span style="color: #000000;"> bytes);
  45.  </span><span style="color: #800080;">11</span>: len <span style="color: #800080;">4</span><span style="color: #000000;">; hex 5f0506f1; asc _   ;;
  46.  </span><span style="color: #800080;">12</span>: len <span style="color: #800080;">4</span>; hex 5efe174a; asc ^<span style="color: #000000;">  J;;
  47. </span><span style="color: #800080;">2020</span>-<span style="color: #800080;">07</span>-07T23:<span style="color: #800080;">36</span>:<span style="color: #800080;">18</span>.060674Z <span style="color: #800080;">2210220</span> [Note] InnoDB: *** (<span style="color: #800080;">2</span><span style="color: #000000;">) WAITING FOR THIS LOCK TO BE GRANTED:
  48. RECORD LOCKS space </span><span style="color: #0000ff;">id</span> <span style="color: #800080;">207</span> page no <span style="color: #800080;">239</span> n bits <span style="color: #800080;">344</span> index PRIMARY of table `tcenter`.`c_place_time_stock` trx <span style="color: #0000ff;">id</span> <span style="color: #800080;">182336317</span><span style="color: #000000;"> lock_mode X locks rec but not gap waiting
  49. Record lock, heap no </span><span style="color: #800080;">214</span> PHYSICAL RECORD: n_fields <span style="color: #800080;">13</span>; compact format; <span style="color: #0000ff;">info</span> bits <span style="color: #800080;">0</span>
  50.  <span style="color: #800080;">0</span>: len <span style="color: #800080;">4</span><span style="color: #000000;">; hex 0000a176; asc    v;;
  51.  </span><span style="color: #800080;">1</span>: len <span style="color: #800080;">6</span><span style="color: #000000;">; hex 00000ade3afa; asc     : ;;
  52.  </span><span style="color: #800080;">2</span>: len <span style="color: #800080;">7</span><span style="color: #000000;">; hex 50000017711ef0; asc P   q  ;;
  53.  </span><span style="color: #800080;">3</span>: len <span style="color: #800080;">4</span>; hex <span style="color: #800080;">80000257</span><span style="color: #000000;">; asc    W;;
  54.  </span><span style="color: #800080;">4</span>: len <span style="color: #800080;">3</span>; hex <span style="color: #800080;">413031</span><span style="color: #000000;">; asc A01;;
  55.  </span><span style="color: #800080;">5</span>: len <span style="color: #800080;">3</span><span style="color: #000000;">; hex 8fc8e8; asc    ;;
  56.  </span><span style="color: #800080;">6</span>: len <span style="color: #800080;">3</span><span style="color: #000000;">; hex 80d780; asc    ;;
  57.  </span><span style="color: #800080;">7</span>: len <span style="color: #800080;">3</span><span style="color: #000000;">; hex 80e780; asc    ;;
  58.  </span><span style="color: #800080;">8</span>: len <span style="color: #800080;">4</span>; hex <span style="color: #800080;">80000877</span>; asc    <span style="color: #0000ff;">w</span><span style="color: #000000;">;;
  59.  </span><span style="color: #800080;">9</span>: len <span style="color: #800080;">4</span>; hex <span style="color: #800080;">80000823</span><span style="color: #000000;">; asc    #;;
  60.  </span><span style="color: #800080;">10</span>: len <span style="color: #800080;">30</span>; hex 3631303136332c3630303031362c3631303136382c3634303036382c3631; asc <span style="color: #800080;">610163</span>,<span style="color: #800080;">600016</span>,<span style="color: #800080;">610168</span>,<span style="color: #800080;">640068</span>,<span style="color: #800080;">61</span>; (total <span style="color: #800080;">62</span><span style="color: #000000;"> bytes);
  61.  </span><span style="color: #800080;">11</span>: len <span style="color: #800080;">4</span><span style="color: #000000;">; hex 5f0506d5; asc _   ;;
  62.  </span><span style="color: #800080;">12</span>: len <span style="color: #800080;">4</span>; hex 5efe1749; asc ^<span style="color: #000000;">  I;;
  63. </span><span style="color: #800080;">2020</span>-<span style="color: #800080;">07</span>-07T23:<span style="color: #800080;">36</span>:<span style="color: #800080;">18</span>.061845Z <span style="color: #800080;">2210220</span> [Note] InnoDB: *** WE ROLL BACK TRANSACTION (<span style="color: #800080;">2</span>)
View Code

 

3.2 事务分析：将编号小的视为事务一（TRANSACTION 182336317）；编号大的视为事务二（TRANSACTION 182336318）

事务一的信息

  1. <span style="color: #000000;">事务一的SQL语句：
  2.         UPDATE c_place_time_stock SET a_amount=2168, update_time=now() WHERE c_place_time_stock.id = 41334
  3. 持有的锁信息（InnoDB: *** (2) HOLDS THE LOCK(S):）
  4.         --表示持有哪些锁，相关信息展示在下边
  5.         index PRIMARY of table `tcenter`.`c_place_time_stock`  --表示锁是加在表c_place_time_stock的索引PRIMARY上
  6.         lock_mode X locks    --表示锁的类型为X排他锁
  7.         n_fields 13;    --表示这个记录是13列
  8.         0: len 4; hex 0000a188; asc     ;;     --第一个字段，也就是主键字段ID，从十六进制转换为10进制，得到的值为41352，可知该事务持有的行锁的行ID为41352 
  9.         ......
  10.         每行均可通过进制转换得到对应的值<br>　　　　</span>RECORD LOCKS space id 207 page no 239 n bits 344 index PRIMARY of table `tcenter`.`c_place_time_stock` trx id 182336317 lock_mode X locks rec but not gap waiting<br><em>　　　　Record lock, heap no 214 PHYSICAL RECORD: n_fields 13; compact format; info bits 0<br>　　　　　　-</em>-结合起来表示在主键上的page <span class="attribute">num=<span class="attribute-value">239上已经持有一个X锁(not gap waiting)，ID=41352</span></span><em><br></em>
  1. <span style="color: #000000;">        结合前边的28 row lock(s)  ，可以知道现有28行记录锁      
  2.     
  3. 在等待的锁信息（InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:）<br>　　　　  --表示等待锁的信息展示在下边
  4.         index PRIMARY of table `tcenter`.`c_place_time_stock`    --表示在等待的是表c_place_time_stock的索引PRIMARY上面的锁
  5.         lock_mode X locks rec but not gap waiting    --表示需要加一个排他锁（写锁），当前的状态是等待中
  6.         Record lock    --表示这是一个记录行锁
  7.         n_fields 13    --表示索引PRIMYRAY的记录是13列， 根据表的结构，依次为id、config_id、place_code ......
  8.         0: len 4; hex 0000a176; asc    v;;    --进制转换后得到id值为41334，可知该事务等待在</span>主键上加一个ID=41334的X锁<span>（语句可以通过开启genaral日志获取） ...... <br><br>所以在等待事务二的：PRIMARY: record lock: (id=</span>41334）

 事务二的信息

 

  1. <span style="color: #000000;">事务二的SQL语句
  2.  
  3. UPDATE c_place_time_stock SET a_amount</span>=(c_place_time_stock.a_amount - <span style="color: #800080;">2</span>), update_time=now() WHERE c_place_time_stock.place_code = <span style="color: #800000;">‘</span><span style="color: #800000;">A02</span><span style="color: #800000;">‘</span> AND c_place_time_stock.stock_date = <span style="color: #800000;">‘</span><span style="color: #800000;">2020-07-08</span><span style="color: #800000;">‘</span> AND c_place_time_stock.start_time <= <span style="color: #800000;">‘</span><span style="color: #800000;">08:00:00</span><span style="color: #800000;">‘</span> AND c_place_time_stock.end_time > <span style="color: #800000;">‘</span><span style="color: #800000;">08:00:00</span><span style="color: #800000;">‘</span><span style="color: #000000;">
  4.   
  5. 持有的锁信息
  6.       根据事务一在等待事务二上表c_place_time_stock的PRIMARY上的锁，且ID</span>=<span style="color: #800080;">41334</span><span style="color: #000000;">，所以推导出事务二持有表c_place_time_stock 上PRIMARY的锁
  7.  
  8.  所以事务二正在申请持有(ID</span>=<span style="color: #800080;">41334</span>)的X锁: PRIMAEY : record lock: (ID=<span style="color: #800080;">41334</span><span style="color: #000000;">)
  9.  
  10.  
  11.  在等待的锁信息（InnoDB: </span>*** (<span style="color: #800080;">1</span><span style="color: #000000;">) WAITING FOR THIS LOCK TO BE GRANTED:）
  12.  
  13.       index PRIMARY of table `sbtest`.`t1` </span>--<span style="color: #000000;">表示在等的是表t1 的主键索引 上面的锁
  14.       lock_mode X locks rec but not gap waiting </span>--<span style="color: #000000;">表示需要加一个排他锁（写锁），当前的状态是等待中
  15.       Record lock </span>--<span style="color: #000000;">是一个记录锁
  16.  
  17.       </span><span style="color: #800080;">0</span>: len <span style="color: #800080;">4</span>; hex 0000a188; asc     ;; --<span style="color: #000000;">是第一个字段，也就是主键字段 ID, 值为41352
  18.       ......(13条记录与事务一持有锁描述相同)
  19.       所以在事务二在等待事务一的: PRIMARY: record lock：(ID</span>=<span style="color: #800080;">41352</span>) 。

3.2 加锁规则和死锁成因

时间点 事务一 事务二
T1 持有主键索引ID=41352的记录锁，模式为排他锁  
T2   申请主键为ID=41334的记录锁，模式为排他锁
T3   在等待主键索引ID=41352的记录锁
T4 申请并等待主键索引为ID=41334的记录锁  
T3被T1阻塞，T4被T2阻塞

业务逻辑：

时间点 事务一 事务二
 

查阅general_log，可以发现在事务一的业务逻辑中，有多个UPDATE，每个都是先select出ID，然后运算后进行UPDATE，在多个UPDATE完成后统一commit；先执行的update如下：

UPDATE c_place_time_stock SET a_amount=9902, update_time=now() WHERE c_place_time_stock.id = 41352

  
T1 持有主键索引ID=41352的X锁  
    UPDATE c_place_time_stock SET a_amount=(c_place_time_stock.a_amount - 2), update_time=now() WHERE c_place_time_stock.place_code = ‘A02‘ AND c_place_time_stock.stock_date = ‘2020-07-08‘ AND c_place_time_stock.start_time <= ‘08:00:00‘ AND c_place_time_stock.end_time > ‘08:00:00‘
T2   申请范围包括ID=41334和41352的记录锁，模式为排他锁
T3   等待主键索引ID=41352的记录锁释放,进入等待队列
  UPDATE c_place_time_stock SET a_amount=2168, update_time=now() WHERE c_place_time_stock.id = 41334  
T4 本身持有主键索引ID=41352的X锁，改为申请ID=41334的X锁，进入等待队列，等待T3的释放申请。  

T3被T1阻塞，T4被T3阻塞，同时T4所在事务未完成，无法释放T1，行程死锁环路。最终回归了代价最小的事务一

 4.解决方式

事务一的逻辑中每次update均commit;

5. 日志说明

锁的几种属性

  1. <span style="color: #000000;">LOCK_REC_NOT_GAP        （锁记录）
  2. LOCK_GAP                         （锁记录前的GAP）
  3. LOCK_ORDINARY              （同时锁记录</span>+<span style="color: #000000;">记录前的GAP ，也就是Next Key锁）
  4. LOCK_INSERT_INTENTION（插入意向锁，其实是特殊的GAP锁）</span>

锁的属性可以与锁模式任意组合

  1. lock-><span style="color: #000000;">type_mode       可以是Lock_X 或者Lock_S 
  2. locks gap before rec  表示为gap锁：lock</span>->type_mode &<span style="color: #000000;"> LOCK_GAP
  3. locks rec but not gap 表示为记录锁，非gap锁：lock</span>->type_mode &<span style="color: #000000;"> LOCK_REC_NOT_GAP
  4. insert intention           表示为插入意向锁：lock</span>->type_mode &<span style="color: #000000;"> LOCK_INSERT_INTENTION
  5. waiting                       表示锁等待：lock</span>->type_mode & LOCK_WAIT

 

参考1：https://mp.weixin.qq.com/s?__biz=MzI4NjExMDA4NQ==&mid=2648450273&idx=1&sn=2836db9eb1086a9fca91748dd233a234&chksm=f3c97c0bc4bef51d6c92e9a79b959c7f7e41367d5429ec80b5881d165978f311a4d8f0452634&scene=21#wechat_redirect

参考2：https://cloud.tencent.com/developer/article/1650865

MySQL一次死锁排查过程分析（双update）

标签：就是   wait   tps   行锁   ide   opened   运算   hid   row   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！