mysql死锁问题解决方案
查询出是被哪个进程给锁住了为什么会产生死锁日志内容：日志解析：找到问题： 解决方案查询出是被哪个进程给锁住了
执行sql：
select concat('KILL ',id,';') from information_schema.processlist p innerjoin information_schema.INNODB_TRX x on p.id=x.trx_mysql_thread_id where db='你的数据库名称';
如果出现死锁的情况下，这个会查询出类似数据
执行查询出来的命令：
KILL 19;KILL 20;
好了，至此占用锁进程被我们杀掉了
为什么会产生死锁
执行命令查看最近死锁的日志
show engine innodb status;
日志内容：
=====================================-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_idlesrv_master_thread log flush and writes: 186158----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 39OS WAIT ARRAY INFO: signal count 37RW-shared spins 0, rounds 53, OS waits 26RW-excl spins 0, rounds 97, OS waits 3RW-sx spins 5, rounds 20, OS waits 0Spin rounds per wait: 53.00 RW-shared, 97.00 RW-excl, 4.00 RW-sx------------TRANSACTIONS------------Trx id counter 6810Purge done for trx's n:o < 6792 undo n:o < 0 state: running but idleHistory list length 28LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 283872150190744, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 6809, ACTIVE 11 sec starting index readmysql tables in use 1, locked 1LOCK 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 waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 10; compact format; info bits 00: len 4; hex 80000001; asc;;1: len 6; hex 000000001a50; ascP;;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 sec12 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: 0650 OS file reads, 445 OS file writes, 187 OS fsyncs0.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 mergesmerged operations:insert 0, delete mark 0, delete 0discarded operations:insert 0, delete mark 0, delete 0Hash 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 4853898Log flushed up to 4853898Pages flushed up to 4853898Last checkpoint at 48538890 pending log flushes, 0 pending chkp writes119 log i/o's done, 0.00 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total large memory allocated 68648960Dictionary memory allocated 372920Buffer pool size 4096Free buffers 3562Database pages534Old database pages 210Modified db pages 0Pending reads0Pending writes: LRU 0, flush list 0, single page 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 443, created 91, written 2710.00 reads/s, 0.00 creates/s, 0.00 writes/sBuffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 534, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue0 read views open inside InnoDBProcess ID=17644, Main thread ID=3, state: sleepingNumber of rows inserted 223, updated 0, deleted 0, read 9670.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s----------------------------END OF INNODB MONITOR OUTPUT============================
日志解析：
BACKGROUND THREAD：显示了后台线程的状态，包括活跃的线程数、空闲的线程数和日志刷新和写入的次数。
SEMAPHORES：显示了信号量的等待情况，包括共享读写锁的旋转次数和等待次数。
TRANSACTIONS：显示了事务的相关信息，包括事务ID计数器、历史事务列表长度和每个会话的事务列表。
FILE I/O：显示了文件I/O线程的状态，包括等待状态、待处理的异步I/O读取和写入次数等。
INSERT BUFFER AND ADAPTIVE HASH INDEX：显示了插入缓冲区和自适应哈希索引的状态。
LOG：显示了日志的序列号、刷新和写入状况。
BUFFER POOL AND MEMORY：显示了缓冲池和内存的状态，包括内存分配、缓冲池大小、空闲缓冲区数量等。
ROW OPERATIONS：显示了InnoDB引擎中的行操作情况，包括查询数、读取数、插入数、更新数和删除数。
找到问题：
从上面日志34行可以看出，报错是因为这条sql：SELECT * FROM subject s WHERE `type` = 2 FOR UPDATE
数据库和表也是有打印出来的：
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`。
为什么会报错：
【重点！！！】为什么报错主要看where后面的用到的字段，在mysql中如果where后面用到的字段不是索引字段，那么在使用FOR UPDATE查询，和 UPDATE 修改的时，事务没结束的情况下都会造成表锁。 我这里查询使用的type并不是索引字段，所以在事务结束前导致了表锁。注意，我这里查询锁表是因为我使用了for update去查询，普通查询是不会锁表的。
解决方案
处理UPDATE导致的锁表，可以考虑先select查询出来后，再改为使用主键去更新。但是有的大公司所使用的ORM框架里面封装了一些追加字段，例如tenant_id、del_status之类的字段，那就需要将这些框架追加的字段添加上索引来解决。处理使用FOR UPDATE查询导致的锁表，这个就没有其他办法了，将where后面用到的字段全部加上索引。这里只是单纯从sql的角度来解决问题，也可以考虑从业务上进行优化，或者从设计角度进行优化。如果觉得《【mysql】mysql死锁问题解决方案》对你有帮助，请点赞、收藏，并留下你的观点哦！
