四个死锁案例分析
背景
死锁发生后，可以直接执行 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
------------
案例三
唯一键sku_id，不同的sku_id可能唯一同一个索引页下，插入时对索引页加锁，产生竞争，导致死锁。
=====================================
2024-02-26 17:44:09 0x7f91af35d700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 12 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 26924360 srv_active, 0 srv_shutdown, 748951 srv_idle
srv_master_thread log flush and writes: 27673311
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 283337601
OS WAIT ARRAY INFO: signal count 841966758
RW-shared spins 0, rounds 836380418, OS waits 259384203
RW-excl spins 0, rounds 2602742518, OS waits 18794647
RW-sx spins 8126738, rounds 47650691, OS waits 473227
Spin rounds per wait: 836380418.00 RW-shared, 2602742518.00 RW-excl, 5.86 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-02-26 17:21:07 0x7f91b6fc1700
*** (1) TRANSACTION:
TRANSACTION 1686018295, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 15 lock struct(s), heap size 1136, 17 row lock(s), undo log entries 11
MySQL thread id 33807999, OS thread handle 140263953069824, query id 12143374525 109.94.107.172 lingyejun updating
UPDATE `sku_inventory`
SET total_inventory=total_inventory - 50,
occupied_inventory=occupied_inventory - 50
,update_time = 1708939267111
WHERE sku_id = 21169
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 363 page no 4 n bits 544 index uniq_sku of table `lingyejun`.`sku_inventory` trx id 1686018295 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 1686018297, ACTIVE 0 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
14 lock struct(s), heap size 1136, 17 row lock(s), undo log entries 11
MySQL thread id 33807191, OS thread handle 140263816959744, query id 12143374554 109.94.103.93 lingyejun updating
UPDATE `sku_inventory`
SET total_inventory=total_inventory - 30,
occupied_inventory=occupied_inventory - 30
,update_time = 1708939267134
WHERE sku_id = 20505
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 363 page no 4 n bits 544 index uniq_sku of table `lingyejun`.`sku_inventory` trx id 1686018297 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 363 page no 4 n bits 544 index uniq_sku of table `lingyejun`.`sku_inventory` trx id 1686018297 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
案例四
循环进行批量插入时，针对表进行取模分表处理，如事务1循环十次分别插入了表a、b、c、d，事务2循环十次分别插入了表b、d、a、c，在同一个表空间下竞争id，引发死锁
*** (1) TRANSACTION:
TRANSACTION 290797679, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 75 lock struct(s), heap size 24784, 484 row lock(s), undo log entries 433
MySQL thread id 10232337, OS thread handle 140065105180416, query id 2741200238 101.94.158.172 lingyejun update
insert into imei_phone_8 (imei, model, agent_full_name,
color, material_code, reg_date,
ship_date, created_at, updated_at
)
values ('861753062000379', 'Vivo C51IN(4+128)', 'India',
'Carbon Black', '631011000651', 1709481315000,
null, 1709482102776, null
)
2024-03-04T00:08:22.910973+08:00 10433832 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1516 page no 10132 n bits 208 index PRIMARY of table `lingyejun_phone`.`imei_phone_8` trx id 290797679 lock_mode X insert intention waiting
2024-03-04T00:08:22.910998+08:00 10433832 [Note] InnoDB: *** (2) TRANSACTION:
TRANSACTION 290797492, ACTIVE 3 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
1245 lock struct(s), heap size 172240, 1298 row lock(s)
MySQL thread id 10433832, OS thread handle 140064929330944, query id 2741200323 101.94.158.172 lingyejun update
insert into imei_phone_9 (imei, model, agent_full_name,
color, material_code, reg_date,
ship_date, created_at, updated_at
)
values ('869947078455153', 'Vivo C53IN(4+128)', 'India',
'Champion Black', '631011001277', 1709223307000,
null, 1709482102908, null
)
2024-03-04T00:08:22.911026+08:00 10433832 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1516 page no 10132 n bits 208 index PRIMARY of table `lingyejun_phone`.`imei_phone_8` trx id 290797492 lock_mode X
2024-03-04T00:08:22.911042+08:00 10433832 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2077 page no 10059 n bits 200 index PRIMARY of table `lingyejun_phone`.`imei_phone_9` trx id 290797492 lock_mode X insert intention waiting
2024-03-04T00:08:22.911062+08:00 10433832 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)
本篇文章如有帮助到您，请给「翎野君」点个赞，感谢您的支持。
出处：http://www.cnblogs.com/lingyejun/
若本文如对您有帮助，不妨点击一下右下角的【推荐】。
如果您喜欢或希望看到更多我的文章，可扫描二维码关注我的微信公众号《翎野君》。
转载文章请务必保留出处和署名，否则保留追究法律责任的权利。
