【mysql】mysql死锁问题解决方案

查询出是被哪个进程给锁住了

执行sql：

select concat('KILL ',id,';') from information_schema.processlist p inner
join information_schema.INNODB_TRX x on p.id=x.trx_mysql_thread_id where db='你的数据库名称';

如果出现死锁的情况下，这个会查询出类似数据
执行查询出来的命令：

KILL 19;
KILL 20;

好了，至此占用锁进程被我们杀掉了

为什么会产生死锁

执行命令查看最近死锁的日志

show engine innodb status;

日志内容：

=====================================
2023-07-05 17:56:28 0x727c INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 25 srv_active, 0 srv_shutdown, 186135 srv_idle
srv_master_thread log flush and writes: 186158
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 39
OS WAIT ARRAY INFO: signal count 37
RW-shared spins 0, rounds 53, OS waits 26
RW-excl spins 0, rounds 97, OS waits 3
RW-sx spins 5, rounds 20, OS waits 0
Spin rounds per wait: 53.00 RW-shared, 97.00 RW-excl, 4.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 6810
Purge done for trx's n:o < 6792 undo n:o < 0 state: running but idle
History list length 28
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283872150190744, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 6809, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 25, OS thread handle 30252, query id 537 localhost 127.0.0.1 root Sending data
/* ApplicationName=DBeaver 23.1.1 - SQLEditor <Script-7.sql> */ SELECT * FROM subject s WHERE `type` = 2 FOR UPDATE
------- TRX HAS BEEN WAITING 11 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 87 page no 4 n bits 72 index PRIMARY of table `dati`.`subject` trx id 6809 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 0
0: len 4; hex 80000001; asc     ;;
1: len 6; hex 000000001a50; asc      P;;
2: len 7; hex ce000001b50110; asc        ;;
3: len 1; hex 81; asc  ;;
4: len 30; hex e5859ae58aa1e5b7a5e4bd9ce88085e5859ae5bbbae79fa5e8af86e4b893; asc                               ; (total 48 bytes);
5: len 30; hex 5b7b226964223a312c227469746c65223a225f5f5f5f5fefbc8ce7acace5; asc [{"id":1,"title":"_____       ; (total 3610 bytes);
6: len 1; hex 81; asc  ;;
7: len 1; hex 80; asc  ;;
8: len 5; hex 99ae50dc42; asc   P B;;
9: len 5; hex 99ae50dc42; asc   P B;;

------------------
---TRANSACTION 6808, ACTIVE 75 sec
12 lock struct(s), heap size 1136, 38 row lock(s)
MySQL thread id 24, OS thread handle 33404, query id 526 localhost 127.0.0.1 root
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
650 OS file reads, 445 OS file writes, 187 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 17393, node heap has 0 buffer(s)
Hash table size 17393, node heap has 0 buffer(s)
Hash table size 17393, node heap has 0 buffer(s)
Hash table size 17393, node heap has 0 buffer(s)
Hash table size 17393, node heap has 0 buffer(s)
Hash table size 17393, node heap has 0 buffer(s)
Hash table size 17393, node heap has 0 buffer(s)
Hash table size 17393, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 4853898
Log flushed up to   4853898
Pages flushed up to 4853898
Last checkpoint at  4853889
0 pending log flushes, 0 pending chkp writes
119 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 68648960
Dictionary memory allocated 372920
Buffer pool size   4096
Free buffers       3562
Database pages     534
Old database pages 210
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 443, created 91, written 271
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 534, 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=17644, Main thread ID=32004, state: sleeping
Number of rows inserted 223, updated 0, deleted 0, read 967
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

日志解析：

1. BACKGROUND THREAD：显示了后台线程的状态，包括活跃的线程数、空闲的线程数和日志刷新和写入的次数。

2. SEMAPHORES：显示了信号量的等待情况，包括共享读写锁的旋转次数和等待次数。

3. TRANSACTIONS：显示了事务的相关信息，包括事务ID计数器、历史事务列表长度和每个会话的事务列表。

4. FILE I/O：显示了文件I/O线程的状态，包括等待状态、待处理的异步I/O读取和写入次数等。

5. INSERT BUFFER AND ADAPTIVE HASH INDEX：显示了插入缓冲区和自适应哈希索引的状态。

6. LOG：显示了日志的序列号、刷新和写入状况。

7. BUFFER POOL AND MEMORY：显示了缓冲池和内存的状态，包括内存分配、缓冲池大小、空闲缓冲区数量等。

8. ROW OPERATIONS：显示了InnoDB引擎中的行操作情况，包括查询数、读取数、插入数、更新数和删除数。

找到问题：

1. 从上面日志34行可以看出，报错是因为这条sql：

 SELECT * FROM subject s WHERE `type` = 2 FOR UPDATE

2. 数据库和表也是有打印出来的：

RECORD LOCKS space id 87 page no 4 n bits 72 index PRIMARY of table `dati`.`subject` trx id 6809 lock_mode X waiting

这里可以看到错误发生在，table `dati`.`subject`。

3. 为什么会报错：
   【重点！！！】为什么报错主要看where后面的用到的字段，在mysql中如果where后面用到的字段不是索引字段，那么在使用FOR UPDATE查询，和 UPDATE 修改的时，事务没结束的情况下都会造成表锁。 我这里查询使用的type并不是索引字段，所以在事务结束前导致了表锁。注意，我这里查询锁表是因为我使用了for update去查询，普通查询是不会锁表的。

解决方案

1. 处理UPDATE导致的锁表，可以考虑先select查询出来后，再改为使用主键去更新。但是有的大公司所使用的ORM框架里面封装了一些追加字段，例如tenant_id、del_status之类的字段，那就需要将这些框架追加的字段添加上索引来解决。
2. 处理使用FOR UPDATE查询导致的锁表，这个就没有其他办法了，将where后面用到的字段全部加上索引。这里只是单纯从sql的角度来解决问题，也可以考虑从业务上进行优化，或者从设计角度进行优化。