MYSQL 真实高并发下的死锁

https://pan.baidu.com/s/1nM3VQdbkNZhnK-wWboEYxA?pwd=vwu6

下面是风控更新语句
 

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-08-04 01:00:10 140188779017984
*** (1) TRANSACTION:
TRANSACTION 895271870, 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
MySQL thread id 1066127, OS thread handle 140189153339136, query id 470904886 172.16.51.14 sharkdb_app updating
//使用4个锁结构,占用1128字节,2个行锁,UNDO日志1个实例.update risk_control_collect_summary
SET sum_success_order_number = sum_success_order_number + 1,sum_success_order_fee = sum_success_order_fee + 0.00,sum_success_order_amount = sum_success_order_amount + 10000.00,update_time = now(),    normal_amount_count = normal_amount_count + 1 
where merchant_no = '010449'and batch_date = '2023-08-04 00:00:00'*** (1) HOLDS THE LOCK(S)://持有锁 3658表空间,第16页好,第112位,主键索引上,共享S REC记录锁
RECORD LOCKS space id 3658 page no 16 n bits 112 index PRIMARY of table `sharkdb`.`risk_control_collect_summary` trx id 895271870 lock mode S locks rec but not gap
Record lock, heap no 43 PHYSICAL RECORD: n_fields 24; compact format; info bits 0
//0 是主键ID,1是事务ID,2 是UNDO地址 3是字段0: len 6; hex 303130343439; asc 010449;;1: len 5; hex 99b0c80000; asc      ;;2: len 6; hex 0000355cc3bc; asc   5\  ;;3: len 7; hex 82000001400146; asc     @ F;;4: len 4; hex 35383135; asc 5815;;5: len 25; hex 50542e20476c6f62616c2047616d6520496e7465726e757361; asc PT. Global Game Internusa;;6: len 4; hex 80000000; asc     ;;7: len 14; hex 8000000000000000000000000000; asc               ;;8: len 14; hex 8000000000000000000000000000; asc               ;;9: len 9; hex 800000000000000000; asc          ;;10: len 9; hex 800000000000000000; asc          ;;11: len 9; hex 800000000000000000; asc          ;;12: len 4; hex 80000001; asc     ;;13: len 14; hex 8000000000000000000000000000; asc               ;;14: len 14; hex 800000000000000000000aae6000; asc             ` ;;15: len 9; hex 800000000000000000; asc          ;;16: len 9; hex 800000000000000000; asc          ;;17: len 9; hex 800000000000000000; asc          ;;18: len 5; hex 99b0c8000a; asc      ;;19: len 5; hex 99b0c8000a; asc      ;;20: len 4; hex 80000000; asc     ;;21: len 4; hex 80000000; asc     ;;22: len 4; hex 80000000; asc     ;;23: len 4; hex 80000001; asc     ;;*** (1) WAITING FOR THIS LOCK TO BE GRANTED: //等待 主键索引 X REC锁
RECORD LOCKS space id 3658 page no 16 n bits 112 index PRIMARY of table `sharkdb`.`risk_control_collect_summary` trx id 895271870 lock_mode X locks rec but not gap waiting
Record lock, heap no 43 PHYSICAL RECORD: n_fields 24; compact format; info bits 00: len 6; hex 303130343439; asc 010449;;1: len 5; hex 99b0c80000; asc      ;;2: len 6; hex 0000355cc3bc; asc   5\  ;;3: len 7; hex 82000001400146; asc     @ F;;4: len 4; hex 35383135; asc 5815;;5: len 25; hex 50542e20476c6f62616c2047616d6520496e7465726e757361; asc PT. Global Game Internusa;;6: len 4; hex 80000000; asc     ;;7: len 14; hex 8000000000000000000000000000; asc               ;;8: len 14; hex 8000000000000000000000000000; asc               ;;9: len 9; hex 800000000000000000; asc          ;;10: len 9; hex 800000000000000000; asc          ;;11: len 9; hex 800000000000000000; asc          ;;12: len 4; hex 80000001; asc     ;;13: len 14; hex 8000000000000000000000000000; asc               ;;14: len 14; hex 800000000000000000000aae6000; asc             ` ;;15: len 9; hex 800000000000000000; asc          ;;16: len 9; hex 800000000000000000; asc          ;;17: len 9; hex 800000000000000000; asc          ;;18: len 5; hex 99b0c8000a; asc      ;;19: len 5; hex 99b0c8000a; asc      ;;20: len 4; hex 80000000; asc     ;;21: len 4; hex 80000000; asc     ;;22: len 4; hex 80000000; asc     ;;23: len 4; hex 80000001; asc     ;;*** (2) TRANSACTION:
TRANSACTION 895271869, 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
MySQL thread id 1066512, OS thread handle 140188740466432, query id 470904887 172.16.51.14 sharkdb_app updating
update risk_control_collect_summary
SET sum_success_order_number = sum_success_order_number + 1,          sum_success_order_fee = sum_success_order_fee + 0.00,            sum_success_order_amount = sum_success_order_amount + 100000.00,      update_time = now(),                        normal_amount_count = normal_amount_count + 1 
where merchant_no = '010449'and batch_date = '2023-08-04 00:00:00'*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3658 page no 16 n bits 112 index PRIMARY of table `sharkdb`.`risk_control_collect_summary` trx id 895271869 lock mode S locks rec but not gap
Record lock, heap no 43 PHYSICAL RECORD: n_fields 24; compact format; info bits 00: len 6; hex 303130343439; asc 010449;;1: len 5; hex 99b0c80000; asc      ;;2: len 6; hex 0000355cc3bc; asc   5\  ;;3: len 7; hex 82000001400146; asc     @ F;;4: len 4; hex 35383135; asc 5815;;5: len 25; hex 50542e20476c6f62616c2047616d6520496e7465726e757361; asc PT. Global Game Internusa;;6: len 4; hex 80000000; asc     ;;7: len 14; hex 8000000000000000000000000000; asc               ;;8: len 14; hex 8000000000000000000000000000; asc               ;;9: len 9; hex 800000000000000000; asc          ;;10: len 9; hex 800000000000000000; asc          ;;11: len 9; hex 800000000000000000; asc          ;;12: len 4; hex 80000001; asc     ;;13: len 14; hex 8000000000000000000000000000; asc               ;;14: len 14; hex 800000000000000000000aae6000; asc             ` ;;15: len 9; hex 800000000000000000; asc          ;;16: len 9; hex 800000000000000000; asc          ;;17: len 9; hex 800000000000000000; asc          ;;18: len 5; hex 99b0c8000a; asc      ;;19: len 5; hex 99b0c8000a; asc      ;;20: len 4; hex 80000000; asc     ;;21: len 4; hex 80000000; asc     ;;22: len 4; hex 80000000; asc     ;;23: len 4; hex 80000001; asc     ;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3658 page no 16 n bits 112 index PRIMARY of table `sharkdb`.`risk_control_collect_summary` trx id 895271869 lock_mode X locks rec but not gap waiting
Record lock, heap no 43 PHYSICAL RECORD: n_fields 24; compact format; info bits 00: len 6; hex 303130343439; asc 010449;;1: len 5; hex 99b0c80000; asc      ;;2: len 6; hex 0000355cc3bc; asc   5\  ;;3: len 7; hex 82000001400146; asc     @ F;;4: len 4; hex 35383135; asc 5815;;5: len 25; hex 50542e20476c6f62616c2047616d6520496e7465726e757361; asc PT. Global Game Internusa;;6: len 4; hex 80000000; asc     ;;7: len 14; hex 8000000000000000000000000000; asc               ;;8: len 14; hex 8000000000000000000000000000; asc               ;;9: len 9; hex 800000000000000000; asc          ;;10: len 9; hex 800000000000000000; asc          ;;11: len 9; hex 800000000000000000; asc          ;;12: len 4; hex 80000001; asc     ;;13: len 14; hex 8000000000000000000000000000; asc               ;;14: len 14; hex 800000000000000000000aae6000; asc             ` ;;15: len 9; hex 800000000000000000; asc          ;;16: len 9; hex 800000000000000000; asc          ;;17: len 9; hex 800000000000000000; asc          ;;18: len 5; hex 99b0c8000a; asc      ;;19: len 5; hex 99b0c8000a; asc      ;;20: len 4; hex 80000000; asc     ;;21: len 4; hex 80000000; asc     ;;22: len 4; hex 80000000; asc     ;;23: len 4; hex 80000001; asc     ;;*** WE ROLL BACK TRANSACTION (2)

发现事务1 和事务2 都是一样的SQL,而且更新的值也是一样的.与开发人员了解下,得知之所以相同,是该010499执行多笔一样的值更新.

高并发下 UPDATE  A SET MONEY=MONEY+1 WHERE ID=001; 如果该语句,主要是该ID是一样的,会导致相互死锁. 一条更新语句需要对主键同时上S + X 也就是共享+排他锁.

有时候搞不懂为啥对主键索引要加S,然后再加X. 多此一举啊?
还搞不懂得的是,为啥两个事务,加锁顺序为什么不一样, 应该按先加S,然后再加X. 难道加不了S,就加X,再等加S.这不就是故意埋坑吗? 
开发通过 REDIS分布式锁来解决.

虽然MYSQL 原理难以了解为啥.我加了丁奇大神的群细细学习里面的锁原理