MySQL｜死锁排查

SmellyCat1024
510 阅读7分钟

1 死锁分析

1.1 锁超时配置

相关sql

mysql> show variables like '%lock_wait_timeout%';

+--------------------------+----------+

| Variable_name            | Value    |

+--------------------------+----------+

| innodb_lock_wait_timeout | 50       |

| lock_wait_timeout        | 31536000 |

+--------------------------+----------+

2 rows in set (0.01 sec)

innodb_lock_wait_timeout
innodb的dml操作的行级锁的等待时间, 适用于InnoDB行锁。
最小1， 最大1073741824， 默认50，单位s。

超过时间报错：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

当发生锁等待超时时，当前语句被回滚（而不是整个事务）。

lock_wait_timeout
数据结构ddl操作的锁的等待时间， 指定尝试获取元数据锁的超时时间。
值范围为 1 到 31536000（1年），默认值为 31536000，单位s。

mysql版本

mysql> select version();

1.2 information_schema下的三张表

1.2.1 INNODB_TRX表

当前运行的所有事务

SELECT * FROM information_schema.INNODB_TRX;
  • trx_id：事务ID。
  • trx_state：事务状态，有以下几种状态：RUNNING、LOCK WAIT、ROLLING BACK 和 COMMITTING。
  • trx_started：事务开始时间。
  • trx_requested_lock_id：事务当前正在等待锁的标识，可以和 INNODB_LOCKS 表 JOIN 以得到更多详细信息。
  • trx_wait_started：事务开始等待的时间。
  • trx_weight：事务的权重。
  • trx_mysql_thread_id：事务线程ID，可以和 PROCESSLIST 表 JOIN。
  • trx_query：事务正在执行的 SQL 语句。
  • trx_operation_state：事务当前操作状态。
  • trx_tables_in_use：当前事务执行的 SQL 中使用的表的个数。
  • trx_tables_locked：当前执行 SQL 的行锁数量。
  • trx_lock_structs：事务保留的锁数量。
  • trx_lock_memory_bytes：事务锁住的内存大小，单位为 BYTES。
  • trx_rows_locked：事务锁住的记录数。包含标记为 DELETED，并且已经保存到磁盘但对事务不可见的行。
  • trx_rows_modified：事务更改的行数。
  • trx_concurrency_tickets：事务并发票数。
  • trx_isolation_level：当前事务的隔离级别。
  • trx_unique_checks：是否打开唯一性检查的标识。
  • trx_foreign_key_checks：是否打开外键检查的标识。
  • trx_last_foreign_key_error：最后一次的外键错误信息。
  • trx_adaptive_hash_latched：自适应散列索引是否被当前事务锁住的标识。
  • trx_adaptive_hash_timeout：是否立刻放弃为自适应散列索引搜索 LATCH 的标识。

1.2.2 INNODB_LOCKs表

当前出现的锁

SELECT * FROM information_schema.INNODB_LOCKs;
  • lock_id：锁 ID。
  • lock_trx_id：拥有锁的事务ID。可以和 INNODB_TRX 表 JOIN 得到事务的详细信息。
  • lock_mode：锁的模式。有如下锁类型：行级锁包括：S、X、IS、IX，分别代表：共享锁、排它锁、意向共享锁、意向排它锁。表级锁包括：S_GAP、X_GAP、IS_GAP、IX_GAP 和 AUTO_INC，分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁。
  • lock_type：锁的类型。RECORD 代表行级锁，TABLE 代表表级锁。
  • lock_table：被锁定的或者包含锁定记录的表的名称。
  • lock_index：当 LOCK_TYPE=’RECORD’ 时，表示索引的名称；否则为 NULL。
  • lock_space：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的表空间 ID；否则为 NULL。
  • lock_page：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的页号；否则为 NULL。
  • lock_rec：当 LOCK_TYPE=’RECORD’ 时，表示一堆页面中锁定行的数量，亦即被锁定的记录号；否则为 NULL。
  • lock_data：当 LOCK_TYPE=’RECORD’ 时，表示锁定行的主键；否则为NULL。

1.2.3 INNODB_LOCK_WAITS表

锁等待的对应关系

SELECT * FROM information_schema.INNODB_LOCK_WAITS;
  • requesting_trx_id：请求事务的ID。可以和 INNODB_TRX 表 JOIN。
  • requested_lock_id：请求事务所等待的锁的ID。可以和 INNODB_LOCKS 表 JOIN。
  • blocking_trx_id：阻塞事务的ID。
  • blocking_lock_id：阻塞事务的锁的ID。

1.2.4 表id对应关系

在 INNODB_TRX 表中找到被卡住的事务Id：trx_id

SELECT trx_id FROM INNODB_TRX WHERE trx_state='LOCK WAIT'

根据 trx_id 关联到 INNODB_LOCK_WAITS表

INNODB_LOCK_WAITS.requesting_trx_id = INNODB_TRX.trx_id

根据 INNODB_LOCK_WAITS 表的阻塞事务Id（blocking_trx_id）找到INNODB_TRX中的事务

INNODB_LOCK_WAITS.blocking_trx_id = INNODB_TRX.trx_id

根据 INNODB_TR的trx_mysql_thread_id 关联到 PROCESSLIST表

INNODB_TRX.trx_mysql_thread_id = PROCESSLIST.id

1.3 定位步骤

现象

报错：“Error Code: 1205 - Lock wait timeout exceeded; try restarting transaction”

1.3.1 找到被锁等待的SQL

SELECT * FROM innodb_trx WHERE trx_state='LOCK WAIT'

image.png

当前处于锁等待的事务id值为：11188391835

1.3.2 找到持有锁的事物

SELECT * FROM INNODB_LOCK_WAITS where requesting_trx_id=11188391835

image.png

等待锁的事务id为：11188391835；而当前持有锁的事务id为：11184617003

1.3.3 查看锁的信息

SELECT * FROM INNODB_LOCKS WHERE lock_trx_id IN (11188391835,11184617003)

image.png

可以看到事务11184617003 持有主键上面的 X 锁，而事务11188391835申请 S锁被阻塞。

1.3.4 查看持有锁的线程的mysql线程id

SELECT TIMESTAMPDIFF(SECOND,trx_started,NOW()),innodb_trx.* FROM innodb_trx WHERE trx_id='11184617003'

等待的对应关系 这张表中requesting_trx_id代表了申请锁资源的事务ID，requesting_lock_id代表申请的锁id，blocking_trx_id代表了阻塞事务70E的事务id，blocking_lock_id代表了阻塞事务70E的锁的ID

    SELECT * FROM information_schema.INNODB_LOCK_WAITS

image.png

ID为11184617003的事务，已经运行了 20186秒，而且权重trx_weight(15416)很大，所以不会被回滚。 所以导致线程事务11188391835 被回滚。

1.3.5 kill事务

找到11184617003事务的线程id(trx_mysql_thread_id)141278145,kill 掉。

kill 141278145

1.4 其他命令

1.4.1 查看最近一次死锁日志

命令

show engine innodb status;

可以看到死锁原因，死锁语句，等待时间等信息

2020-04-11 00:35:55 0x243c
*** (1) TRANSACTION:
TRANSACTION 38048, ACTIVE 92 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 53, OS thread handle 2300, query id 2362 localhost ::1 root update
insert into account values(null,'Jay',100)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38048 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 576569; asc Wei;;
1: len 4; hex 80000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 38049, ACTIVE 72 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 52, OS thread handle 9276, query id 2363 localhost ::1 root update
insert into account  values(null,'Yan',100)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 3; hex 576569; asc Wei;;
1: len 4; hex 80000002; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (1)

1.4.2 查看数据库当前的进程

命令

mysql> show processlist ;

image.png

  • id:标识ID。
  • user:发出该语句的mysql用户。
  • host：发出该语句的客户机的主机名(系统用户除外，没有主机)。
  • db：默认数据库。
  • command：线程正在执行的命令的类型。
  • time：线程处于当前状态的时间(以秒为单位)。
  • state：指示线程正在执行的操作、事件或状态。
  • info：线程正在执行的语句，如果没有执行任何语句，则为NULL。

2 死锁处理策略

解决死锁常用的两个方案：超时机制 和 死锁检测

2.1 超时机制

超时机制，即两个事务互相等待时，当一个等待时间超过设置的某一阀值时，其中一个事务回滚，另一个事务继续执行。

SHOW GLOBAL VARIABLES LIKE  "%innodb_lock_wait_timeout%"; -- 查看锁等待时间默认50s
SET GLOBAL  innodb_lock_wait_timeout =40 -- 修改所等待时间

2.2 死锁检测

InnoDB通过锁的信息链表和事务等待链表，构造一张图（wait-for graph），判断是否存在等待回路。如有，则存在死锁，通常选择回滚undo 量最小的事务

SHOW GLOBAL VARIABLES LIKE  "%innodb_deadlock_detect%"; -- 查看死锁检测是否开启，mysql5.7默认开启
SET GLOBAL  innodb_deadlock_detect ='ON' -- 开启死锁检测
SET GLOBAL  innodb_deadlock_detect ='OFF' -- 关闭死锁检测

3 避免死锁

  • 结合业务情况对于有锁业务尽量放到事务后半段执行或进行拆分处理，减少锁时间。
  • 降低隔离级别，避免间隙锁
  • 查询语句尽量不加锁 - 尽量基于primary（主键）或unique key更新数据 - 加锁顺序一致，尽可能一次性锁定所需的数据行
avatar
文章
阅读
粉丝