两个死锁案例分析

背景 

死锁发生后，可以直接执行 show engine innodb status; 查看最近的死锁日志。

案例一：

索引唯一键冲突导致的死锁，解决办法，在进行事务操作前先对数据进行一个排序，降低互相锁冲突的概率。 

=====================================
2024-02-18 15:36:00 0x7f2146991700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 21 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 924090 srv_active, 0 srv_shutdown, 12634562 srv_idle
srv_master_thread log flush and writes: 13558652
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 690256
OS WAIT ARRAY INFO: signal count 2388066
RW-shared spins 0, rounds 2719233, OS waits 585806
RW-excl spins 0, rounds 7997290, OS waits 53784
RW-sx spins 48794, rounds 551200, OS waits 7420
Spin rounds per wait: 2719233.00 RW-shared, 7997290.00 RW-excl, 11.30 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-02-18 14:10:02 0x7f21443ab700
*** (1) TRANSACTION:
TRANSACTION 154050430, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 3
MySQL thread id 5647381, OS thread handle 139780884543232, query id 958859522 198.157.127.157 lingyi update
insert into sku_item_base(sku_id, item_code, sku_code, created_time, updated_time) values
         (  
            201106, '6056289', '130010733683', 1708236602319, 1708236602319
         ),( 
            201106, '631011000846', '130010733683', 1708236602319, 1708236602319
         ),( 
            201106, '631002000636', '130010733683', 1708236602319, 1708236602319
         ),( 
            201106, '631011001805', '130010733683', 1708236602319, 1708236602319
         ) 
        on duplicate key update
        sku_id = values(sku_id),
        item_code = values(item_code)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 366 page no 12 n bits 704 index uniq_inv of table `lingyejun`.`sku_item_base` trx id 154050430 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 154050431, ACTIVE 0 sec inserting, thread declared inside InnoDB 4997
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 5660394, OS thread handle 139780855346944, query id 958859523 198.157.127.157 lingyi update
insert into sku_item_base(sku_id, item_code, sku_code, created_time, updated_time) values
         (  
            201106, '631002000636', '130010733683', 1708236600557, 1708236600557
         ),( 
            201106, '6056289', '130010733683', 1708236600557, 1708236600557
         ),( 
            201106, '631011001805', '130010733683', 1708236600557, 1708236600557
         ),( 
            201106, '631011000846', '130010733683', 1708236600557, 1708236600557
         ) 
        on duplicate key update
        sku_id = values(sku_id),
        item_code = values(item_code)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 366 page no 12 n bits 704 index uniq_inv of table `lingyejun`.`sku_item_base` trx id 154050431 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 366 page no 12 n bits 704 index uniq_inv of table `lingyejun`.`sku_item_base` trx id 154050431 lock_mode X waiting
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------

案例二

id not in导致间隙锁的范围太大，容易造成较大概率的锁冲突，改善方式，将更新变成预先查出要更新的id，直接按照id in的方式进行更新，即可避免此场景下的死锁。

=====================================
2024-02-20 08:54:36 0x7f91be3a3700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 26 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 26376784 srv_active, 0 srv_shutdown, 748203 srv_idle
srv_master_thread log flush and writes: 27124987
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 265694912
OS WAIT ARRAY INFO: signal count 814044187
RW-shared spins 0, rounds 792696423, OS waits 242496494
RW-excl spins 0, rounds 2512671900, OS waits 18197126
RW-sx spins 7800791, rounds 45286872, OS waits 452972
Spin rounds per wait: 792696423.00 RW-shared, 2512671900.00 RW-excl, 5.81 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-02-20 00:14:21 0x7f91be4ed700
*** (1) TRANSACTION:
TRANSACTION 1660683803, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 22 lock struct(s), heap size 1136, 18 row lock(s), undo log entries 8
MySQL thread id 33018945, OS thread handle 140263655167744, query id 11765453967 198.157.127.157 lingyi updating
update lingye_imei
         SET sale_status =2,
            
                validate_desc ='sys activate',
            
            
                update_time =1708359261193,
            
            
                updated_by ='sys' 
         WHERE  id not in
                (
                    3149792
                )
            
            
                and imei in
                (
                    '741977070160711'
                )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 922 page no 114075 n bits 120 index PRIMARY of table `lingyejun`.`lingye_imei` trx id 1660683803 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 1660683800, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
21 lock struct(s), heap size 1136, 16 row lock(s), undo log entries 8
MySQL thread id 33017059, OS thread handle 140263939823360, query id 11765453974 198.157.127.157 lingyi updating
update lingye_imei
         SET sale_status =2,
            
                validate_desc ='sys activate',
            
            
                update_time =1708359261195,
            
            
                updated_by ='sys' 
         WHERE  id not in
                (
                    3149780
                )
            
            
                and imei in
                (
                    '761939970161115'
                )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 922 page no 114075 n bits 120 index PRIMARY of table `lingyejun`.`lingye_imei` trx id 1660683800 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 922 page no 69029 n bits 624 index idx_imei of table `lingyejun`.`lingye_imei` trx id 1660683800 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------

本篇文章如有帮助到您，请给「翎野君」点个赞，感谢您的支持。

首发链接：https://www.cnblogs.com/lingyejun/p/18024223