Mysql死锁日志阅读

本是古典何须时尚 2023-02-24 14:26 1阅读 0赞

### Mysql死锁日志阅读 ###

*  死锁日志案例
 *  常见事物状态
 *  锁组合
 *   *  锁模式
     *  锁
     *  锁组合

# 死锁日志案例 #

2020-07-08 22:02:10 0x7fe014c18700
    
    *** (1) TRANSACTION:
    # TRANSACTION 1690883715 表示事务编号为 1690883715，ACTIVE 0 sec 表示活跃0秒，starting index read 表示事务状态为根据索引读取数据
    TRANSACTION 1690883715, ACTIVE 0 sec starting index read
    #mysql tables in use 1 表示有一个表被使用，locked 1 表示表上有一个表锁。
    mysql tables in use 1, locked 1
    # LOCK WAIT 表示事务正在等待锁，2 lock struct(s) 表示该事务的锁链表的长度为 2，每个链表节点代表该事务持有的一个锁结构，包括表锁，记录锁以及 autoinc 锁等。heap size 1136 为事务分配的锁堆内存大小。
    # 1 row lock(s) 表示当前事务持有的行锁个数，可以通过遍历上面提到的 2 个锁结构，找出其中类型为 LOCK_REC 的记录数。
    # undo log entries 15 (如果有的话)表示当前事务有 15 个 undo log 记录，因为二级索引不记 undo log，说明该事务已经更新了 15 条聚集索引记录。（大多数情况下，每个事务都不止一条 SQL 语句）
    LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
    # 表示 事务的线程信息，以及数据库 IP 地址和数据库名
    MySQL thread id 30424924, OS thread handle 140604429420288, query id 33395216608 10.7.93.122 ssoservi_3b77 updating
    # 显示的是正在等待锁的 SQL 语句，死锁日志里每个事务都只显示一条 SQL 语句（不会显示整个事物的所有SQL）
    delete from `policy` where `uid`=2904506 and `res_id`='crmagent' and `type`=1 and version < 223129845
    
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    # 正在等待 RECORD LOCKS 表示记录锁，space id 为 1052，page no 为 570660，n bits 512 表示这个记录锁结构上留有 512 个 bit 位（该 page 上的记录数 + 64）。ux_uid_resId_type of table `ssoservice`.`policy`代表什么表上的什么索引，trx id 1690883715 表示事物Id, lock_mode X 代表锁模式为排他锁，locks rec but not gap 代表锁类型为记录锁，waiting代表锁状态为等待
    RECORD LOCKS space id 1052 page no 570660 n bits 512 index ux_uid_resId_type of table `ssoservice`.`policy` trx id 1690883715 lock_mode X locks rec but not gap waiting
    
    *** (2) TRANSACTION:
    
    TRANSACTION 1690883714, ACTIVE 0 sec starting index read
    
    mysql tables in use 1, locked 1
    
    3 lock struct(s), heap size 1136, 2 row lock(s)
    
    MySQL thread id 30425884, OS thread handle 140600397629184, query id 33395216610 10.27.124.111 ssoservi_3b77 updating
    
    update `policy` SET uid=2904506,res_id='crmagent',type=1,cond='',version=223129930,cts='2020-07-08 22:02:10.387',uts='2020-07-08 22:02:10.387' where `uid`=2904506 and `res_id`='crmagent' and `type`=1
    
    *** (2) HOLDS THE LOCK(S):
    # 持有共享记录锁
    RECORD LOCKS space id 1052 page no 570660 n bits 512 index ux_uid_resId_type of table `ssoservice`.`policy` trx id 1690883714 lock mode S
    
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    # 等待排他记录锁
    RECORD LOCKS space id 1052 page no 570660 n bits 512 index ux_uid_resId_type of table `ssoservice`.`policy` trx id 1690883714 lock_mode X locks rec but not gap waiting
    
    *** WE ROLL BACK TRANSACTION (1)

# 常见事物状态 #

<table> 
 <thead> 
  <tr> 
   <th align="center">状态</th> 
   <th align="right">含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">starting index read</td> 
   <td align="right">根据索引读取数据</td> 
  </tr> 
  <tr> 
   <td align="center">updating</td> 
   <td align="right">事务已经真正进入了Update的函数逻辑(row_update_for_mysql)</td> 
  </tr> 
  <tr> 
   <td align="center">deleting</td> 
   <td align="right">事务已经真正进入了delete的函数逻辑(row_update_for_mysql)</td> 
  </tr> 
  <tr> 
   <td align="center">inserting</td> 
   <td align="right">事务已经真正进入了Insert的函数逻辑(row_update_for_mysql)</td> 
  </tr> 
  <tr> 
   <td align="center">thread declared inside InnoDB</td> 
   <td align="right">说明事务已经进入innodb层。通常而言 不在innodb层的事务大部分是会被回滚的。</td> 
  </tr> 
  <tr> 
   <td align="center">fetching rows</td> 
   <td align="right">正在获取记录（row_search_for_mysql）</td> 
  </tr> 
 </tbody> 
</table>

# 锁组合 #

锁模式+锁

## 锁模式 ##

## 锁 ##

<table> 
 <thead> 
  <tr> 
   <th align="center">锁</th> 
   <th align="right">标记</th> 
   <th align="center">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">LOCK_REC_NOT_GAP</td> 
   <td align="right">locks rec but not gap</td> 
   <td align="center">记录锁</td> 
  </tr> 
  <tr> 
   <td align="center">LOCK_GAP</td> 
   <td align="right">locks gap before rec</td> 
   <td align="center">间隙锁</td> 
  </tr> 
  <tr> 
   <td align="center">LOCK_ORDINARY</td> 
   <td align="right"></td> 
   <td align="center">Next Key锁</td> 
  </tr> 
  <tr> 
   <td align="center">LOCK_INSERT_INTENTION</td> 
   <td align="right">insert intention</td> 
   <td align="center">插入意向锁，其实是特殊的GAP锁</td> 
  </tr> 
 </tbody> 
</table>

## 锁组合 ##

锁可以与锁模式任意组合：Lock\_mode Lock

<table> 
 <thead> 
  <tr> 
   <th align="center">组合</th> 
   <th align="right">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">lock_mode X locks rec but not gap</td> 
   <td align="right">记录锁（LOCK_REC_NOT_GAP）</td> 
  </tr> 
  <tr> 
   <td align="center">lock_mode X locks gap before rec</td> 
   <td align="right">间隙锁（LOCK_GAP）</td> 
  </tr> 
  <tr> 
   <td align="center">lock_mode X</td> 
   <td align="right">Next-key 锁（LOCK_ORNIDARY）</td> 
  </tr> 
  <tr> 
   <td align="center">lock_mode X locks gap before rec insert intention</td> 
   <td align="right">插入意向锁（LOCK_INSERT_INTENTION）</td> 
  </tr> 
 </tbody> 
</table>

这里有一点要注意的是，并不是在日志里看到 lock\_mode X 就认为这是 Next-key 锁，因为还有一个例外：如果在 supremum record 上加锁，locks gap before rec 会省略掉，间隙锁会显示成 lock\_mode X，插入意向锁会显示成 lock\_mode X insert intention。譬如下面这个：

RECORD LOCKS space id 0 page no 307 n bits 72 index `PRIMARY` of table `test`.`test` trx id 50F lock_mode X
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

看起来像是 Next-key 锁，但是看下面的 heap no 1 表示这个记录是 supremum record（另外，infimum record 的 heap no 为 0），所以这个锁应该看作是一个间隙锁。