并发条件下update条件不当引发死锁问题

最新推荐文章于 2025-04-13 22:47:59 发布

原创最新推荐文章于 2025-04-13 22:47:59 发布 · 2.4k 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

mysql

项目压力测试中出现数据库死锁问题，通过分析发现可能由于更新条件中包含不等于操作导致。建议引入自增ID作为主键，并调整更新策略以解决此问题。

项目压力测试数据库中发生死锁问题，分析怀疑是更新条件（card_no_hash）中包含不等于，会同时锁定多行记录。
建议增加id int auto_increment作为物理主键（对业务无影响），当前的复合主键作为唯一键，更新时转化为先查询要更新行的主键后根据主键更新。

事务1：
update `user_card_list_07`
set `default_flag` = 'N', `update_time` = SYSDATE(3)
where ((`user_cust_id` = '0929101809390389') AND
(`mer_id` = '880001') AND
(`card_no_hash` != '622c85820558ba719e7ce46ccf0baf85ddd1a6b3194b7fbbf866573bcbafcfed'));

事务2：
update `user_card_list_07`
set `default_flag` = 'N', `update_time` = SYSDATE(3)
where ((`user_cust_id` = '0929101809504124') AND
(`mer_id` = '880001') AND
(`card_no_hash` != 'e728e034c5215b73b09da660c51875ebb8016cfbd31bf4a741a235c8c98d96a8'))

阅读死锁日志：https://www.cnblogs.com/liang545621/p/12613592.html

死锁完整信息
------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-09-29 10:18:09 2b4fb2840700
*** (1) TRANSACTION:
TRANSACTION 283660827, ACTIVE 0.007 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
LOCK BLOCKING MySQL thread id: 235767294 block 218880820
MySQL thread id 218880820, OS thread handle 0x2b5020607700, query id 277909937 100.104.184.65 gpuuklge Searching rows for update
/* 0a1eedad15381282001041801d429c/9.9999.272392175.4.32.84.9.17.39.31.9.9.9.207// *//*DRDS /192.168.17.139/da33866a0401001-4/ */update `user_card_list_07` set `default_flag` = 'N', `update_time` = SYSDATE(3) where ((`user_cust_id` = '0929101809390389') AND (`mer_id` = '880001') AND (`card_no_hash` != '622c85820558ba719e7ce46ccf0baf85ddd1a6b3194b7fbbf866573bcbafcfed'))
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10817 page no 75 n bits 120 index `PRIMARY` of table `tty_perf_wtbt_0001`.`user_card_list_07` trx id 283660827 lock_mode X waiting
Record lock, heap no 41 PHYSICAL RECORD: n_fields 20; compact format; info bits 0
0: len 16; hex 30393239313031383039353034313234; asc 0929101809504124;;
1: len 30; hex 653732386530333463353231356237336230396461363630633531383735; asc e728e034c5215b73b09da660c51875; (total 64 bytes);
2: len 6; hex 000010e8521c; asc R ;;
3: len 7; hex da0000027e0110; asc ~ ;;
4: len 6; hex 383830303031; asc 880001;;
5: len 18; hex 3935353935362a2a2a2a2a2a2a2a37363034; asc 955956********7604;;
6: len 30; hex 23303132426a3671386569652f74397a56757578437242585036676b4d31; asc #012Bj6q8eie/t9zVuuxCrBXP6gkM1; (total 36 bytes);
7: len 11; hex 313231393537322a2a2a2a; asc 1219572****;;
8: len 28; hex 23303132396f44746262335665616555483356535346726650513d3d; asc #0129oDtbb3VeaeUH3VSSFrfPQ==;;
9: len 1; hex 59; asc Y;;
10: len 4; hex 30313033; asc 0103;;
11: len 4; hex 30303133; asc 0013;;
12: len 4; hex 31333031; asc 1301;;
13: len 12; hex 313033313231303335323435; asc 103121035245;;
14: len 30; hex 6d6f636be4b8ade59bbde5869ce4b89ae993b6e8a18ce882a1e4bbbde69c; asc mock ; (total 73 bytes);
15: len 1; hex 50; asc P;;
16: len 1; hex 4e; asc N;;
17: len 20; hex 3230313830393239313031383039373430303139; asc 20180929101809740019;;
18: len 5; hex 99a0faa48a; asc ;;
19: len 5; hex 99a0faa48a; asc ;;

*** (2) TRANSACTION:
TRANSACTION 283660828, ACTIVE 0.005 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 235767294, OS thread handle 0x2b4fb2840700, query id 277909939 100.104.184.65 gpuuklge Searching rows for update
/* 0a1eedad15381282001041801d429c/9.9999.272392175.4.32.82.9.9.24.25.17.9.9.9.11.48// *//*DRDS /192.168.17.139/da33866a0c01001-4/ */update `user_card_list_07` set `default_flag` = 'N', `update_time` = SYSDATE(3) where ((`user_cust_id` = '0929101809504124') AND (`mer_id` = '880001') AND (`card_no_hash` != 'e728e034c5215b73b09da660c51875ebb8016cfbd31bf4a741a235c8c98d96a8'))
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10817 page no 75 n bits 120 index `PRIMARY` of table `tty_perf_wtbt_0001`.`user_card_list_07` trx id 283660828 lock_mode X locks rec but not gap
Record lock, heap no 41 PHYSICAL RECORD: n_fields 20; compact format; info bits 0
0: len 16; hex 30393239313031383039353034313234; asc 0929101809504124;;
1: len 30; hex 653732386530333463353231356237336230396461363630633531383735; asc e728e034c5215b73b09da660c51875; (total 64 bytes);
2: len 6; hex 000010e8521c; asc R ;;
3: len 7; hex da0000027e0110; asc ~ ;;
4: len 6; hex 383830303031; asc 880001;;
5: len 18; hex 3935353935362a2a2a2a2a2a2a2a37363034; asc 955956********7604;;
6: len 30; hex 23303132426a3671386569652f74397a56757578437242585036676b4d31; asc #012Bj6q8eie/t9zVuuxCrBXP6gkM1; (total 36 bytes);
7: len 11; hex 313231393537322a2a2a2a; asc 1219572****;;
8: len 28; hex 23303132396f44746262335665616555483356535346726650513d3d; asc #0129oDtbb3VeaeUH3VSSFrfPQ==;;
9: len 1; hex 59; asc Y;;
10: len 4; hex 30313033; asc 0103;;
11: len 4; hex 30303133; asc 0013;;
12: len 4; hex 31333031; asc 1301;;
13: len 12; hex 313033313231303335323435; asc 103121035245;;
14: len 30; hex 6d6f636be4b8ade59bbde5869ce4b89ae993b6e8a18ce882a1e4bbbde69c; asc mock ; (total 73 bytes);
15: len 1; hex 50; asc P;;
16: len 1; hex 4e; asc N;;
17: len 20; hex 3230313830393239313031383039373430303139; asc 20180929101809740019;;
18: len 5; hex 99a0faa48a; asc ;;
19: len 5; hex 99a0faa48a; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10817 page no 75 n bits 120 index `PRIMARY` of table `tty_perf_wtbt_0001`.`user_card_list_07` trx id 283660828 lock_mode X waiting
Record lock, heap no 41 PHYSICAL RECORD: n_fields 20; compact format; info bits 0
0: len 16; hex 30393239313031383039353034313234; asc 0929101809504124;;
1: len 30; hex 653732386530333463353231356237336230396461363630633531383735; asc e728e034c5215b73b09da660c51875; (total 64 bytes);
2: len 6; hex 000010e8521c; asc R ;;
3: len 7; hex da0000027e0110; asc ~ ;;
4: len 6; hex 383830303031; asc 880001;;
5: len 18; hex 3935353935362a2a2a2a2a2a2a2a37363034; asc 955956********7604;;
6: len 30; hex 23303132426a3671386569652f74397a56757578437242585036676b4d31; asc #012Bj6q8eie/t9zVuuxCrBXP6gkM1; (total 36 bytes);
7: len 11; hex 313231393537322a2a2a2a; asc 1219572****;;
8: len 28; hex 23303132396f44746262335665616555483356535346726650513d3d; asc #0129oDtbb3VeaeUH3VSSFrfPQ==;;
9: len 1; hex 59; asc Y;;
10: len 4; hex 30313033; asc 0103;;
11: len 4; hex 30303133; asc 0013;;
12: len 4; hex 31333031; asc 1301;;
13: len 12; hex 313033313231303335323435; asc 103121035245;;
14: len 30; hex 6d6f636be4b8ade59bbde5869ce4b89ae993b6e8a18ce882a1e4bbbde69c; asc mock ; (total 73 bytes);
15: len 1; hex 50; asc P;;
16: len 1; hex 4e; asc N;;
17: len 20; hex 3230313830393239313031383039373430303139; asc 20180929101809740019;;
18: len 5; hex 99a0faa48a; asc ;;
19: len 5; hex 99a0faa48a; asc ;;

*** WE ROLL BACK TRANSACTION (2)