文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

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

2023-09-23 06:11

关注

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;

日志内容:

=====================================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_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 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; 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 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 pages     534Old database pages 210Modified db pages  0Pending reads      0Pending 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=32004, 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============================

日志解析:

  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
  1. 数据库和表也是有打印出来的:
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`。
  1. 为什么会报错:
    【重点!!!】为什么报错主要看where后面的用到的字段,在mysql中如果where后面用到的字段不是索引字段,那么在使用FOR UPDATE查询,和 UPDATE 修改的时,事务没结束的情况下都会造成表锁。 我这里查询使用的type并不是索引字段,所以在事务结束前导致了表锁。注意,我这里查询锁表是因为我使用了for update去查询,普通查询是不会锁表的。

解决方案

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

来源地址:https://blog.csdn.net/A_halo/article/details/131560604

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯