mysql两个事务同时修改同一条记录引发死锁，求解答

Question

业务是从上游系统中下载数据文件，并对数据文件进行处理，有两个表：

• 对账单表
• 对账单详情表

对账单表中记录对账单的基本信息，还有为了提高统计速度而设计了一个统计字段，如正在下载的个数、下载成功/失败的个数、正在处理的个数、处理成功/失败的个数。需要处理的文件保存在对账单详情表中，当某文件的状态变化时，同时修改对账单详情表中的统计数据（加1或减1）。
在多线程处理的过程中，当两个线程同时修改同一个对账单记录时，会发生死锁。我尝试过事务的隔离级别，在RC、RR和SERIALIZABLE中都会有死锁发生。死锁日志如下：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-09-06 05:35:09 0x7f9d8efff700
*** (1) TRANSACTION:
TRANSACTION 1292232, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 2
MariaDB thread id 135, OS thread handle 140314685732608, query id 1327193 172.17.0.1 root Updating
UPDATE bill SET processing=processing-1, process_fail=process_fail+1 WHERE id=621
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 253 page no 3 n bits 24 index PRIMARY of table `ziwu2_ds`.`bill` trx id 1292232 lock_mode X locks rec but not gap waiting
Record lock, heap no 11 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
 0: len 4; hex 0000026d; asc    m;;
 1: len 6; hex 00000013b7bf; asc       ;;
 2: len 7; hex 4d000001b70ed6; asc M      ;;
 3: len 1; hex 01; asc  ;;
 4: len 1; hex 01; asc  ;;
 5: SQL NULL;
 6: len 5; hex 99a7b20000; asc      ;;
 7: SQL NULL;
 8: len 5; hex 99aa8c5261; asc    Ra;;
 9: SQL NULL;
 10: len 1; hex 01; asc  ;;
 11: len 4; hex 80000ad7; asc     ;;
 12: len 4; hex 80000004; asc     ;;
 13: len 4; hex 8000053a; asc    :;;
 14: len 4; hex 80000000; asc     ;;
 15: len 4; hex 80000001; asc     ;;
 16: len 4; hex 80000000; asc     ;;
 17: len 4; hex 80000537; asc    7;;
 18: len 4; hex 90289ab9; asc  (  ;;

*** CONFLICTING WITH:
RECORD LOCKS space id 253 page no 3 n bits 24 index PRIMARY of table `ziwu2_ds`.`bill` trx id 1292232 lock mode S locks rec but not gap
Record lock, heap no 11 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
 0: len 4; hex 0000026d; asc    m;;
 1: len 6; hex 00000013b7bf; asc       ;;
 2: len 7; hex 4d000001b70ed6; asc M      ;;
 3: len 1; hex 01; asc  ;;
 4: len 1; hex 01; asc  ;;
 5: SQL NULL;
 6: len 5; hex 99a7b20000; asc      ;;
 7: SQL NULL;
 8: len 5; hex 99aa8c5261; asc    Ra;;
 9: SQL NULL;
 10: len 1; hex 01; asc  ;;
 11: len 4; hex 80000ad7; asc     ;;
 12: len 4; hex 80000004; asc     ;;
 13: len 4; hex 8000053a; asc    :;;
 14: len 4; hex 80000000; asc     ;;
 15: len 4; hex 80000001; asc     ;;
 16: len 4; hex 80000000; asc     ;;
 17: len 4; hex 80000537; asc    7;;
 18: len 4; hex 90289ab9; asc  (  ;;


*** (2) TRANSACTION:
TRANSACTION 1292231, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MariaDB thread id 152, OS thread handle 140314907342592, query id 1327189 172.17.0.1 root Updating
UPDATE bill SET downloading=downloading+1 WHERE id=621
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 253 page no 3 n bits 24 index PRIMARY of table `ziwu2_ds`.`bill` trx id 1292231 lock_mode X locks rec but not gap waiting
Record lock, heap no 11 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
 0: len 4; hex 0000026d; asc    m;;
 1: len 6; hex 00000013b7bf; asc       ;;
 2: len 7; hex 4d000001b70ed6; asc M      ;;
 3: len 1; hex 01; asc  ;;
 4: len 1; hex 01; asc  ;;
 5: SQL NULL;
 6: len 5; hex 99a7b20000; asc      ;;
 7: SQL NULL;
 8: len 5; hex 99aa8c5261; asc    Ra;;
 9: SQL NULL;
 10: len 1; hex 01; asc  ;;
 11: len 4; hex 80000ad7; asc     ;;
 12: len 4; hex 80000004; asc     ;;
 13: len 4; hex 8000053a; asc    :;;
 14: len 4; hex 80000000; asc     ;;
 15: len 4; hex 80000001; asc     ;;
 16: len 4; hex 80000000; asc     ;;
 17: len 4; hex 80000537; asc    7;;
 18: len 4; hex 90289ab9; asc  (  ;;

*** CONFLICTING WITH:
RECORD LOCKS space id 253 page no 3 n bits 24 index PRIMARY of table `ziwu2_ds`.`bill` trx id 1292232 lock mode S locks rec but not gap
Record lock, heap no 11 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
 0: len 4; hex 0000026d; asc    m;;
 1: len 6; hex 00000013b7bf; asc       ;;
 2: len 7; hex 4d000001b70ed6; asc M      ;;
 3: len 1; hex 01; asc  ;;
 4: len 1; hex 01; asc  ;;
 5: SQL NULL;
 6: len 5; hex 99a7b20000; asc      ;;
 7: SQL NULL;
 8: len 5; hex 99aa8c5261; asc    Ra;;
 9: SQL NULL;
 10: len 1; hex 01; asc  ;;
 11: len 4; hex 80000ad7; asc     ;;
 12: len 4; hex 80000004; asc     ;;
 13: len 4; hex 8000053a; asc    :;;
 14: len 4; hex 80000000; asc     ;;
 15: len 4; hex 80000001; asc     ;;
 16: len 4; hex 80000000; asc     ;;
 17: len 4; hex 80000537; asc    7;;
 18: len 4; hex 90289ab9; asc  (  ;;

*** WE ROLL BACK TRANSACTION (0)

对此日志，我有几方面不理解：

1. 事务1292232和事务1292231都在同时等待id为621的对账单记录锁，这个可以排队处理，为什么会造成死锁？
2. 这两个事务冲突的S锁是怎么来的？UPDATE bill SET processing=processing-1, process_fail=process_fail+1 WHERE id=621的过程会先加S锁再加X锁吗？
3. 如果事务1330630已经取得了S锁，再加X锁应该不会有冲突吧？

Answer 1

2个都拿到了 S锁，然后要拿到X锁，但都拿不到。
我觉得肯定是S无法升级到X。 如果其它的S是读取，等它读完了，S就释放了，可惜都不释放。
建议可以自己设置行级锁 for update， 我想你肯定不想表级锁的吧。

Answer 2

for update的方式我试过，效果一样，我现在就是不知道这个S锁是怎么来的。

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-09-06 08:00:34 0x7f9d9c276700
*** (1) TRANSACTION:
TRANSACTION 1330630, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 2
MariaDB thread id 246, OS thread handle 140314906420992, query id 1402508 192.168.1.200 root Statistics
SELECT * FROM bill WHERE id=616 FOR UPDATE
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 343 page no 3 n bits 24 index PRIMARY of table `ziwu2_ds`.`bill` trx id 1330630 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
 0: len 4; hex 00000268; asc    h;;
 1: len 6; hex 000000144dc1; asc     M ;;
 2: len 7; hex 070000014227b4; asc     B' ;;
 3: len 1; hex 01; asc  ;;
 4: len 1; hex 01; asc  ;;
 5: SQL NULL;
 6: len 5; hex 99a7680000; asc   h  ;;
 7: SQL NULL;
 8: len 5; hex 99aa8c7ee7; asc    ~ ;;
 9: SQL NULL;
 10: len 1; hex 01; asc  ;;
 11: len 4; hex 8000004e; asc    N;;
 12: len 4; hex 80000005; asc     ;;
 13: len 4; hex 80000037; asc    7;;
 14: len 4; hex 80000000; asc     ;;
 15: len 4; hex 80000001; asc     ;;
 16: len 4; hex 80000000; asc     ;;
 17: len 4; hex 80000036; asc    6;;
 18: len 4; hex 80090561; asc    a;;

*** CONFLICTING WITH:
RECORD LOCKS space id 343 page no 3 n bits 24 index PRIMARY of table `ziwu2_ds`.`bill` trx id 1330630 lock mode S locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
 0: len 4; hex 00000268; asc    h;;
 1: len 6; hex 000000144dc1; asc     M ;;
 2: len 7; hex 070000014227b4; asc     B' ;;
 3: len 1; hex 01; asc  ;;
 4: len 1; hex 01; asc  ;;
 5: SQL NULL;
 6: len 5; hex 99a7680000; asc   h  ;;
 7: SQL NULL;
 8: len 5; hex 99aa8c7ee7; asc    ~ ;;
 9: SQL NULL;
 10: len 1; hex 01; asc  ;;
 11: len 4; hex 8000004e; asc    N;;
 12: len 4; hex 80000005; asc     ;;
 13: len 4; hex 80000037; asc    7;;
 14: len 4; hex 80000000; asc     ;;
 15: len 4; hex 80000001; asc     ;;
 16: len 4; hex 80000000; asc     ;;
 17: len 4; hex 80000036; asc    6;;
 18: len 4; hex 80090561; asc    a;;


*** (2) TRANSACTION:
TRANSACTION 1330622, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MariaDB thread id 248, OS thread handle 140315241711360, query id 1402505 192.168.1.200 root Statistics
SELECT * FROM bill WHERE id=616 FOR UPDATE
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 343 page no 3 n bits 24 index PRIMARY of table `ziwu2_ds`.`bill` trx id 1330622 lock_mode X locks rec but not gap waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
 0: len 4; hex 00000268; asc    h;;
 1: len 6; hex 000000144dc1; asc     M ;;
 2: len 7; hex 070000014227b4; asc     B' ;;
 3: len 1; hex 01; asc  ;;
 4: len 1; hex 01; asc  ;;
 5: SQL NULL;
 6: len 5; hex 99a7680000; asc   h  ;;
 7: SQL NULL;
 8: len 5; hex 99aa8c7ee7; asc    ~ ;;
 9: SQL NULL;
 10: len 1; hex 01; asc  ;;
 11: len 4; hex 8000004e; asc    N;;
 12: len 4; hex 80000005; asc     ;;
 13: len 4; hex 80000037; asc    7;;
 14: len 4; hex 80000000; asc     ;;
 15: len 4; hex 80000001; asc     ;;
 16: len 4; hex 80000000; asc     ;;
 17: len 4; hex 80000036; asc    6;;
 18: len 4; hex 80090561; asc    a;;

*** CONFLICTING WITH:
RECORD LOCKS space id 343 page no 3 n bits 24 index PRIMARY of table `ziwu2_ds`.`bill` trx id 1330630 lock mode S locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
 0: len 4; hex 00000268; asc    h;;
 1: len 6; hex 000000144dc1; asc     M ;;
 2: len 7; hex 070000014227b4; asc     B' ;;
 3: len 1; hex 01; asc  ;;
 4: len 1; hex 01; asc  ;;
 5: SQL NULL;
 6: len 5; hex 99a7680000; asc   h  ;;
 7: SQL NULL;
 8: len 5; hex 99aa8c7ee7; asc    ~ ;;
 9: SQL NULL;
 10: len 1; hex 01; asc  ;;
 11: len 4; hex 8000004e; asc    N;;
 12: len 4; hex 80000005; asc     ;;
 13: len 4; hex 80000037; asc    7;;
 14: len 4; hex 80000000; asc     ;;
 15: len 4; hex 80000001; asc     ;;
 16: len 4; hex 80000000; asc     ;;
 17: len 4; hex 80000036; asc    6;;
 18: len 4; hex 80090561; asc    a;;

*** WE ROLL BACK TRANSACTION (0)

Answer 3

两个都拿到S锁，然后需要获取X锁，在争抢的情况下为什么会都拿不到？理论上应该至少存在一个事务能够争抢到S锁吧？

Answer 4

看到最后一行 *** WE ROLL BACK TRANSACTION (0) , 那应该是不止两个事务在执行 , 因为这个日志有多个事务也只打两个
日志中只看到了事务在等待什么锁 , 但是没看到事务持有什么锁 , 建议把各个事务里的SQL拿出来分析一下

Answer 5

我这里面的事务确实挺多，有7种：

1. 添加任务事务：
   向对账单详情表中添加记录：INSERT INTO workflow(data_id, bill_id, url, file_name, priority, collector_id) VALUES(...)
   更新对账单表中的计划数：UPDATE bill SET plan=plan+1 WHERE id=%s
2. 开始下载事务：
   修改对账单详情表中的状态：UPDATE workflow SET status='下载中', download_start=NOW(), download_end=NULL WHERE id=%s
   修改对账单中的正在下载数：UPDATE bill SET downloading=downloading+1 WHERE id=%s
3. 下载成功事务：
   修改对账单详情表中的状态：UPDATE workflow SET status='下载成功', download_end=NOW() WHERE id=%s
   修改对账单中的正在下载数：UPDATE bill SET downloading=downloading-1, download_success=download_success+1, download_size=download_size+%s WHERE id=%s
4. 下载失败事务：
   修改对账单详情表中的状态：UPDATE workflow SET status='下载失败', download_end=NOW() WHERE id=%s
   修改对账单中的正在下载数：UPDATE bill SET downloading=downloading-1, download_fail=download_fail+1 WHERE id=%s
5. 开始处理事务：
   修改对账单详情表中的状态：UPDATE workflow SET status='处理中', process_start=NOW(), process_end=NULL WHERE id=%s
   修改对账单中的正在下载数：UPDATE bill SET processing=processing+1 WHERE id=%s
6. 处理成功事务：
   修改对账单详情表中的状态：UPDATE workflow SET status='处理成功', process_end=NOW() WHERE id=%s
   修改对账单中的正在下载数：UPDATE bill SET processing=processing-1, process_success=process_success+1 WHERE id=%s
7. 处理失败事务：
   修改对账单详情表中的状态：UPDATE workflow SET status='处理失败', process_end=NOW() WHERE id=%s
   修改对账单中的正在下载数：UPDATE bill SET processing=processing-1, process_fail=process_fail+1 WHERE id=%s

Answer 6

博主找到问题原因了吗？为什么会死锁，我最近也遇到了这种问题