Mysql8死锁排查

Mysql8死锁排查

• Mysql8 查询死锁的表

 -- 查询死锁表
 select * from performance_schema.data_locks;
 -- 查询死锁等待时间
 select * from performance_schema.data_lock_waits;

• Mysql8之前的版本 查询死锁的表

 -- 查询死锁表
 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
 -- 查询死锁等待时间
 SELECT * FROM information_schema.INNODB_LOCK_waits;

1、准备环境

CREATE TABLE `student` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `number` bigint DEFAULT NULL COMMENT '普通索引编号',
  `unique_number` bigint DEFAULT NULL COMMENT '唯一索引编号',
  `no_index_number` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index2` (`unique_number`),
  KEY `index1` (`number`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb3;

mysql> select * from test.student;
+----+--------+--------+---------------+-----------------+
| id | name   | number | unique_number | no_index_number |
+----+--------+--------+---------------+-----------------+
|  2 | 张三   |      2 |             2 |               2 |
|  5 | 李四   |      5 |             5 |               5 |
|  8 | 王五   |      8 |             8 |               8 |
| 11 | 怀少飞 |     11 |            11 |              11 |
+----+--------+--------+---------------+-----------------+
4 rows in set (0.00 sec)

mysql>

##锁等待超时参数（新的连接生效）,这里设置为5000便于测试.
set global innodb_lock_wait_timeout=5000;  

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>

2、死锁状态模拟

session1	session2
begin;select * from test.student where id = 2 for update;
	begin;select * from test.student where id = 5 for update;
update test.student set name=concat(name, UNIX_TIMESTAMP()) where id = 5;
	update test.student set name=concat(name, UNIX_TIMESTAMP()) where id = 2;

3、排查死锁问题

查询进程

mysql> show processlist;
+----+-----------------+-----------------+--------------------+---------+--------+------------------------+------------------+
| Id | User            | Host            | db                 | Command | Time   | State                  | Info             |
+----+-----------------+-----------------+--------------------+---------+--------+------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL               | Daemon  | 473405 | Waiting on empty queue | NULL             |
| 37 | root            | localhost:44722 | test               | Sleep   |   2453 |                        | NULL             |
| 38 | root            | localhost:44724 | performance_schema | Sleep   |    162 |                        | NULL             |
| 39 | root            | localhost:45996 | NULL               | Query   |      0 | init                   | show processlist |
| 40 | root            | localhost:48414 | test               | Sleep   |   2544 |                        | NULL             |
| 41 | root            | localhost:48441 | test               | Sleep   |   2127 |                        | NULL             |
| 42 | root            | localhost:50596 | performance_schema | Sleep   |    162 |                        | NULL             |
+----+-----------------+-----------------+--------------------+---------+--------+------------------------+------------------+
7 rows in set (0.00 sec)

mysql>

查询死锁表，获取死锁的线程信息

mysql>  select ENGINE_TRANSACTION_ID,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from performance_schema.data_locks;
+-----------------------+-----------+---------------+-------------+-----------+---------------+-------------+-----------+
| ENGINE_TRANSACTION_ID | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------+-----------+---------------+-------------+-----------+---------------+-------------+-----------+
|                672229 |        84 | test          | student     | TABLE     | IX            | GRANTED     | NULL      |
|                672229 |        84 | test          | student     | RECORD    | X,REC_NOT_GAP | GRANTED     | 2         |
|                672229 |        84 | test          | student     | RECORD    | X,REC_NOT_GAP | GRANTED     | 5         |
+-----------------------+-----------+---------------+-------------+-----------+---------------+-------------+-----------+
3 rows in set (0.00 sec)


# 查看当前未提交的事务（如果死锁等待超时,事务可能还没有关闭）
mysql> select trx_id,trx_state,trx_started,trx_tables_locked,trx_rows_locked,trx_mysql_thread_id from information_schema.innodb_trx;
+--------+-----------+---------------------+-------------------+-----------------+---------------------+
| trx_id | trx_state | trx_started         | trx_tables_locked | trx_rows_locked | trx_mysql_thread_id |
+--------+-----------+---------------------+-------------------+-----------------+---------------------+
| 672229 | RUNNING   | 2024-06-22 17:57:07 |                 1 |               2 |                  40 |
+--------+-----------+---------------------+-------------------+-----------------+---------------------+
1 row in set (0.00 sec)


# 杀死进程id（就是上面命令的trx_mysql_thread_id列）
mysql> kill 40;
Query OK, 0 rows affected (0.00 sec)

mysql>

根据线程ID，找到真正执行的SQL语句

mysql> select thread_id,sql_text from performance_schema.events_statements_history where thread_id = 84;
+-----------+--------------------------------------------------------------------------+
| thread_id | sql_text                                                                 |
+-----------+--------------------------------------------------------------------------+
|        84 | select @@version_comment limit 1                                         |
|        84 | SELECT DATABASE()                                                        |
|        84 | NULL                                                                     |
|        84 | select * from test.student                                               |
|        84 | begin                                                                    |
|        84 | select * from test.student where id = 2 for update                       |
|        84 | update test.student set name=concat(name, UNIX_TIMESTAMP()) where id = 5 |
+-----------+--------------------------------------------------------------------------+
7 rows in set (0.00 sec)

mysql>

查看最近一个死锁情况

mysql> show engine innodb status \G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2024-06-22 18:06:14 0x85d4 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 38 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 37 srv_active, 0 srv_shutdown, 279528 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2126
OS WAIT ARRAY INFO: signal count 2078
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-06-22 18:04:22 0x182c
*** (1) TRANSACTION: #开启第一个事务
TRANSACTION 672229, ACTIVE 435 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 40, OS thread handle 18880, query id 896 localhost ::1 root updating

# 更新语句
update test.student set name=concat(name, UNIX_TIMESTAMP()) where id = 5

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 330 page no 4 n bits 72 index PRIMARY of table `test`.`student` trx id 672229 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000002; asc         ;;
 1: len 6; hex 0000000a41c0; asc     A ;;
 2: len 7; hex 80000000000000; asc        ;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 8; hex 8000000000000002; asc         ;;
 5: len 8; hex 8000000000000002; asc         ;;
 6: len 8; hex 8000000000000002; asc         ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 330 page no 4 n bits 72 index PRIMARY of table `test`.`student` trx id 672229 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000005; asc         ;;
 1: len 6; hex 0000000a41c0; asc     A ;;
 2: len 7; hex 80000000000000; asc        ;;
 3: len 8; hex 6b6576696e313131; asc kevin111;;
 4: len 8; hex 8000000000000005; asc         ;;
 5: len 8; hex 8000000000000005; asc         ;;
 6: len 8; hex 8000000000000005; asc         ;;


*** (2) TRANSACTION: #开启第二个事务
TRANSACTION 672230, ACTIVE 425 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 41, OS thread handle 37908, query id 916 localhost ::1 root updating

# 更新语句
update test.student set name=concat(name, UNIX_TIMESTAMP()) where id = 2

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 330 page no 4 n bits 72 index PRIMARY of table `test`.`student` trx id 672230 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000005; asc         ;;
 1: len 6; hex 0000000a41c0; asc     A ;;
 2: len 7; hex 80000000000000; asc        ;;
 3: len 8; hex 6b6576696e313131; asc kevin111;;
 4: len 8; hex 8000000000000005; asc         ;;
 5: len 8; hex 8000000000000005; asc         ;;
 6: len 8; hex 8000000000000005; asc         ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 330 page no 4 n bits 72 index PRIMARY of table `test`.`student` trx id 672230 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000002; asc         ;;
 1: len 6; hex 0000000a41c0; asc     A ;;
 2: len 7; hex 80000000000000; asc        ;;
 3: len 6; hex e5bca0e4b889; asc       ;;
 4: len 8; hex 8000000000000002; asc         ;;
 5: len 8; hex 8000000000000002; asc         ;;
 6: len 8; hex 8000000000000002; asc         ;;

*** WE ROLL BACK TRANSACTION (2)	#第二个事务回滚（此处为什么选择第二个事务？？）


mysql>