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， 我想你肯定不想表级锁的吧。