mysql死锁排查

• 查看正在进行中的事务

SELECT * FROM information_schema.INNODB_TRX;

字段	解释
trx_id	唯一事务id号，只读事务和非锁事务是不会创建id的
trx_state	事务的执行状态，值一般分为：RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.
trx_started	事务的开始时间
trx_requested_lock_id	如果trx_state是lock wait，显示事务当前等待锁的id，不是则为空。想要获取锁的信息，根据该lock_id，以innodb_locks表中lock_id列匹配条件进行查询，获取相关信息。
trx_wait_started	如果trx_state是lock wait，该值代表事务开始等待锁的时间；否则为空。
trx_weight	事务的高度，代表修改的行数（不一定准确）和被事务锁住的行数。为了解决死锁，innodb会选择一个高度最小的事务来当做牺牲品进行回滚。已经被更改的非交易型表的事务权重比其他事务高，即使改变的行和锁住的行比其他事务低。
trx_mysql_thread_id	mysql线程id。想要获取该线程的信息，根据该thread_id，以information_schema.processlist表的id列为匹配条件进行查询。
trx_query	事务正在执行的sql语句。
trx_operation_state	事务当前的操作状态，没有则为空。
trx_tables_in_use	事务在处理当前sql语句使用innodb引擎表的数量。
trx_tables_locked	当前sql语句有行锁的innodb表的数量。（因为只是行锁，不是表锁，表仍然可以被多个事务读和写）
trx_lock_structs	事务保留锁的数量。
trx_lock_memory_bytes	在内存中事务索结构占得空间大小。
trx_rows_locked	事务行锁最准确的数量。这个值可能包括对于事务在物理上存在，实际不可见的删除标记的行。
trx_rows_modified	事务修改和插入的行数
trx_concurrency_tickets	该值代表当前事务在被清掉之前可以多少工作，由 innodb_concurrency_tickets系统变量值指定。
trx_isolation_level	事务隔离等级。
trx_unique_checks	当前事务唯一性检查启用还是禁用。当批量数据导入时，这个参数是关闭的。
trx_foreign_key_checks	当前事务的外键坚持是启用还是禁用。当批量数据导入时，这个参数是关闭的。
trx_last_foreign_key_error	最新一个外键错误信息，没有则为空。
trx_adaptive_hash_latched	自适应哈希索引是否被当前事务阻塞。当自适应哈希索引查找系统分区，一个单独的事务不会阻塞全部的自适应hash索引。自适应hash索引分区通过 innodb_adaptive_hash_index_parts参数控制，默认值为8。
trx_adaptive_hash_timeout	是否为了自适应hash索引立即放弃查询锁，或者通过调用mysql函数保留它。当没有自适应hash索引冲突，该值为0并且语句保持锁直到结束。在冲突过程中，该值被计数为0，每句查询完之后立即释放门闩。当自适应hash索引查询系统被分区（由 innodb_adaptive_hash_index_parts参数控制），值保持为0。
trx_is_read_only	值为1表示事务是read only。
trx_autocommit_non_locking	值为1表示事务是一个select语句，该语句没有使用for update或者shared mode锁，并且执行开启了autocommit，因此事务只包含一个语句。当TRX_AUTOCOMMIT_NON_LOCKING和TRX_IS_READ_ONLY同时为1，innodb通过降低事务开销和改变表数据库来优化事务。

• 查看正在锁的事务

SELECT * FROM information_schema.INNODB_LOCKS;

字段	解释
lock_id	innodb唯一lock id。把他当做一个不透明的字符串。虽然lock_id当前包含trx_id，lock_id的数据格式在任何时间都肯能改变。不要写用于解析lock_id值得应用程序。
lock_trx_id	持有锁的事务id。查询事务信息，与innodb_trx表中trx_id列匹配。
lock_mode	锁请求。该值包括： S, X, IS, IX, GAP, AUTO_INC, and UNKNOWN。锁模式标识符可以组合用于识别特定的锁模式。查看更多信息，点击此处
lock_type	锁类型。行锁为record，表锁为table。
lock_table	被锁的表名，或者包含锁记录的表名。
lock_index	lock_type为行锁时，该值为索引名，否则为空。
lock_space	lock_type为行锁时，该值为锁记录的表空间的id，否则为空。
lock_page	lock_type为行锁时，该值为锁记录页数量，否则为空。
lock_rec	lock_type为行锁时，页内锁记录的堆数，否则为空。
lock_data	与锁相关的数据。如果lock_type为行锁时，该值是锁记录的主键值，否则为空。这列包含锁定行的主键列的值，转化为一个有效的字符串，如果没有主键，lock_data是唯一innodb内部行id号。如果是键值或者范围大于索引的最大值会使用间隙锁，lock_data表示为supremum pseudo-record。当包含锁记录的页不在buffer pool内，innodb不去从磁盘获取页，为了避免不必要的磁盘操作，lock_data为空。

• 查看等待锁的事务

SELECT * FROM information_schema.INNODB_LOCK_WAITS;

字段	解释
requesting_trx_id	请求（阻止）事务的ID。
requested_lock_id	事务正在等待的锁的ID。
blocking_trx_id	阻止事务的ID。
blocking_lock_id	由阻止另一个事务继续进行的事务所持有的锁的ID

• 显示当前被锁定的表的列表

SHOW OPEN TABLES where In_use > 0;

字段	解释
Database	数据库名称
Table	表名称
In_use	表示有多少线程正在使用某张表
Name_locked	表名是否被锁,这一般发生在Drop或Rename命令操作这张表时

• 查看InnoDB存储引擎的状态信息,包括在途事务、锁、缓冲池和日志文件等。

show engine innodb status;

字段	解释
InnoDB	存储引擎
Name
Status	最近一次事务、锁、缓存池的日志信息

• 显示用户正在运行的线程

show processlist;

字段	解释
Id	这个线程的唯一标识，当我们发现这个线程有问题的时候，可以通过 kill 命令，加上这个Id值将这个线程杀掉。
User	指启动这个线程的用户。
Host	记录了发送请求的客户端的 IP 和 端口号。通过这些信息在排查问题的时候，我们可以定位到是哪个客户端的哪个进程发送的请求。
db	当前执行的命令是在哪一个数据库上。如果没有指定数据库，则该值为 NULL 。
Command	指此刻该线程正在执行的命令。
Time	表示该线程处于当前状态的时间。
State	线程的状态，和Command对应。
Info	一般记录的是线程执行的语句。默认只显示前100个字符，也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist。

死锁的内部处理方案：

• 死锁探测机制 innodb_deadlock_detect 默认开启，会自动回滚其中一个事务
• 锁等待超时机制 innodb_lock_wait_timeout 默认50s锁超时自定释放

死锁分析：

表中数据：student

id	name	age
1	linging	20
2	zhangsan	30
3	lisi	15

事务14360	事务14361
begin	begin
①select *from student where id = 2 for update;
	②select *from student where id = 3 for update;
③select *from student where id = 3 for update;
	④select *from student where id = 2 for update;

执行完①②语句时：

查看事务：SELECT * FROM information_schema.INNODB_TRX

接着执行③：

查看正在运行的事务：SELECT * FROM information_schema.INNODB_TRX

查看正在锁的事务：SELECT * FROM information_schema.INNODB_LOCKS

查看等待锁的事务：SELECT * FROM information_schema.INNODB_LOCK_WAITS

显示当前被锁定的表的列表：SHOW OPEN TABLES where In_use > 0

查看线程列表：show processlist;

如果此时什么也不做，那么事务14360的语句③在等待事务14361的语句②释放X锁，等待50s之后，锁超时释放：

这时如果执行事务14361的语句④，那么出现死锁，mysql自动进行死锁检测，检测之后回滚其中的一个事务

查看正在运行的事务:SELECT * FROM information_schema.INNODB_TRX

可以看到，事务14361被回滚了。

查看最近一次死锁的日志：show engine innodb status;

=====================================
2024-01-04 22:14:15 0x7f5b44b28700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 0 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 14 srv_active, 0 srv_shutdown, 3577 srv_idle
srv_master_thread log flush and writes: 3591
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 14
OS WAIT ARRAY INFO: signal count 14
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 13, rounds 25, OS waits 12
RW-excl spins 0, rounds 22543, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 mutex, 1.92 RW-shared, 22543.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-01-04 22:10:57 0x7f5b44ae7700
*** (1) TRANSACTION:
TRANSACTION 14360, ACTIVE 484 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 1, OS thread handle 140029971568384, query id 940 192.168.159.1 root statistics
select *from student where id = 3 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 71 page no 3 n bits 72 index `PRIMARY` of table `ssm`.`student` trx id 14360 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000003; asc         ;;
 1: len 6; hex 000000003808; asc     8 ;;
 2: len 7; hex 260000019214e8; asc &      ;;
 3: len 4; hex 6c697369; asc lisi;;
 4: len 4; hex 8000000f; asc     ;;
 5: len 8; hex 3132333133313233; asc 12313123;;
 6: len 8; hex 7368656e7a68656e; asc shenzhen;;

*** (2) TRANSACTION:
TRANSACTION 14361, ACTIVE 481 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 3, OS thread handle 140029971035904, query id 951 192.168.159.1 root statistics
select *from student where id = 2 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 71 page no 3 n bits 72 index `PRIMARY` of table `ssm`.`student` trx id 14361 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000003; asc         ;;
 1: len 6; hex 000000003808; asc     8 ;;
 2: len 7; hex 260000019214e8; asc &      ;;
 3: len 4; hex 6c697369; asc lisi;;
 4: len 4; hex 8000000f; asc     ;;
 5: len 8; hex 3132333133313233; asc 12313123;;
 6: len 8; hex 7368656e7a68656e; asc shenzhen;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 71 page no 3 n bits 72 index `PRIMARY` of table `ssm`.`student` trx id 14361 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 8000000000000002; asc         ;;
 1: len 6; hex 00000000380a; asc     8 ;;
 2: len 7; hex 27000001932072; asc '     r;;
 3: len 8; hex 7a68616e6773616e; asc zhangsan;;
 4: len 4; hex 8000001e; asc     ;;
 5: len 8; hex 3132333133313233; asc 12313123;;
 6: len 8; hex 7368656e7a68656e; asc shenzhen;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 14362
Purge done for trx's n:o < 14350 undo n:o < 0 state: running but idle
History list length 392
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421505098215608, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421505098214760, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421505098213912, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 14360, ACTIVE 682 sec
3 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 1, OS thread handle 140029971568384, query id 958 192.168.159.1 root cleaning up
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
670 OS file reads, 187 OS file writes, 54 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 2 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 8937219
Log flushed up to   8937219
Pages flushed up to 8937219
Last checkpoint at  8937210
0 pending log flushes, 0 pending chkp writes
38 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137494528
Dictionary memory allocated 361089
Buffer pool size   8191
Free buffers       7785
Database pages     404
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 369, created 35, written 135
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 404, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=140029846894336, state: sleeping
Number of rows inserted 3, updated 3, deleted 0, read 51
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

分析日志：