当前位置： 首页 > article >正文

MYSQL 真实高并发下的死锁

article 2024/11/8 17:02:50

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 0
 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     ;;

*** (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 0
 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     ;;

*** (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 0
 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     ;;

*** 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 原理难以了解为啥.我加了丁奇大神的群细细学习里面的锁原理


http://www.kler.cn/a/384651.html

相关文章：

  • 华为交换机Vlan划分
  • Uniapp去除顶部导航栏-小程序、H5、APP适用
  • 人保财险（外包）面试分享
  • oneplus6-build.md
  • 小北的字节跳动青训营与调用模型：调用模型：OpenAI API vs 微调开源Llama2/ChatGLM（持续更新中~~~）
  • aspose如何获取PPT放映页“切换”的“持续时间”值
  • Swift 开发教程系列 - 第5章：集合类型
  • Docker 常用命令详解(详细版)
  • Flutter 中的那些设计模式的写法（持续更新）
  • VSCode 1.82之后的vscode server离线安装
  • ISME Comm | 机器学习和深度学习在微生物组研究中的应用
  • 牛客sql题目总结（1）
  • RK3568平台（基础篇）selinux内核安全
  • JVM基本结构
  • 工业相机常用功能之白平衡及C++代码分享
  • 天锐绿盾加密软件与Ping32全面对比：为您的数据保驾护航，哪款加密工具更适合您？
  • 解析Go切片：为何按值传递时会发生改变？｜得物技术
  • Python OpenCV 傅里叶变换
  • 纯血鸿蒙系统 HarmonyOS NEXT自动化测试实践
  • leetcode 3254 长度为 K 的子数组的能量值 I 中等
  • redhat5与ubuntu上Makefile语法区别
  • DevOps业务价值流：架构设计最佳实践
  • 【K8S问题系列】Kubernetes Pod节点CrashLoopBackOff 状态【已解决】
  • 技术干货丨Inspire Cast 如何避免自动网格划分失败？
  • 汽车免拆诊断案例 | 2017款凯迪拉克XT5车组合仪表上的指针均失灵
  • Git+Jenkins基本使用