文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >理解MySQL中的死锁

问理解MySQL中的死锁
EN

Stack Overflow用户
提问于 2019-11-22 09:52:49
回答 1查看 332关注 0票数 1

我是MySQL新手，我以前在工作。在解决应用程序中的死锁方面，我遇到了一些问题。请帮助我理解这个问题。表定义：

代码语言：javascript
复制
CREATE TABLE `APPLICATION` (
  `ID` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `APPLICATION_NUMBER` varchar(35) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `STATUS` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `SUB_STATUS` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `SOURCE_TYPE` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `SOURCE_ID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `SOURCE_CHANNEL` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `PRODUCT_PROGRAM` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `LOAN_TYPE` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `ASSIGNED_TO` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `CREATED_BY` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `CREATION_DATE` datetime DEFAULT NULL,
  `LAST_UPDATE_DT` datetime DEFAULT NULL,
  `LAST_UPDATE_BY` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `LOAN_AMOUNT` decimal(38,0) DEFAULT NULL,
  `INSURANCE_OPT_IN` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `RCU_STATUS` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `TVR_COMMENTS` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `TVR_DECISION` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `MM_PAID_TO` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `PURPOSE_OF_LOAN` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `MARGIN_MONEY` double DEFAULT NULL,
  `PAYMENT_MODE` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `IS_ELIGIBLE` decimal(1,0) DEFAULT NULL,
  `CRM_STATUS` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `CRM_REASON` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `IS_INTERESTED_CLI` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `IS_INTERESTED_CI` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `CRITICAL_ILLNESS` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `CREDIT_LIFE_INSURANCE` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `CREATE_USER` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `CREATE_DATE` datetime DEFAULT NULL,
  `LAST_UPDATE_USER` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `LAST_UPDATE_DATE` datetime DEFAULT NULL,
  `STATUS_ID` bigint(35) DEFAULT NULL,
  `SUBSTATUS_ID` bigint(35) DEFAULT NULL,
  `DOCUMEMNTUPLOAD_COMMENTS` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `DOCUMEMNTUPLOAD_ACK` decimal(1,0) DEFAULT NULL,
  `NO_OF_FINANCIERS_FOR_ALL_ASSET` decimal(3,0) DEFAULT NULL,
  `DUPLICATED_FROM` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE KEY `UK_APPLICATION` (`APPLICATION_NUMBER`),
  KEY `FK_APL_STATID` (`STATUS_ID`) USING BTREE,
  KEY `FK_APL_SUBSTATID` (`SUBSTATUS_ID`) USING BTREE,
  CONSTRAINT `FK_APL_STATID` FOREIGN KEY (`STATUS_ID`) REFERENCES `STATUS` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `FK_APL_SUBSTATID` FOREIGN KEY (`SUBSTATUS_ID`) REFERENCES `SUB_STATUS` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

我的死锁细节来自显示引擎INNODB状态：

代码语言：javascript
复制
------------------------
 LATEST DETECTED DEADLOCK
 ------------------------
 2019-11-22 04:48:06 0x2ad75cf91700
 *** (1) TRANSACTION:
 TRANSACTION 291327, ACTIVE 37 sec fetching rows
 mysql tables in use 1, locked 1
 LOCK WAIT 48 lock struct(s), heap size 8400, 1540 row lock(s), undo log entries 5
 MySQL thread id 14042, OS thread handle 47099630130944, query id 4174847 172.29.24.227 bpapi updating
 UPDATE APPLICATION SET NO_OF_FINANCIERS_FOR_ALL_ASSET = 7 WHERE id >'' AND APPLICATION_NUMBER = '001601'
 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 3803 page no 21 n bits 144 index PRIMARY of table `BAPIDB`.`APPLICATION` trx id 291327 lock_mode X waiting
 Record lock, heap no 72 PHYSICAL RECORD: n_fields 42; compact format; info bits 128
  0: len 30; hex 65646466383861382d633733342d346561332d393665302d396366343234; asc eddf88a8-c734-4ea3-96e0-9cf424; (total 36 bytes);
  1: len 6; hex 000000046e42; asc     nB;;
  2: len 7; hex 02000000fa0518; asc        ;;
  3: len 6; hex 303031353930; asc 001590;;
  4: len 4; hex 31303033; asc 1003;;
  5: len 4; hex 31303033; asc 1003;;
  6: SQL NULL;
  7: SQL NULL;
  8: len 6; hex 506f7274616c; asc Portal;;
  9: SQL NULL;
  10: SQL NULL;
  11: SQL NULL;
  12: len 30; hex 35633131613436612d303963302d313165612d396533372d396165613961; asc 5c11a46a-09c0-11ea-9e37-9aea9a; (total 36 bytes);
  13: SQL NULL;
  14: len 5; hex 99a4ac4b92; asc    K ;;
  15: len 11; hex 427573696e657373415049; asc BusinessAPI;;
  16: SQL NULL;
  17: SQL NULL;
  18: SQL NULL;
  19: SQL NULL;
  20: SQL NULL;
  21: SQL NULL;
  22: SQL NULL;
  23: SQL NULL;
  24: SQL NULL;
  25: SQL NULL;
  26: len 7; hex 53554343455353; asc SUCCESS;;
  27: len 27; hex 5265636f72642055706461746564205375636365737366756c6c79; asc Record Updated Successfully;;
  28: SQL NULL;
  29: SQL NULL;
  30: SQL NULL;
  31: SQL NULL;
  32: SQL NULL;
  33: SQL NULL;
  34: SQL NULL;
  35: SQL NULL;
  36: SQL NULL;
  37: SQL NULL;
  38: SQL NULL;
  39: SQL NULL;
  40: SQL NULL;
  41: SQL NULL;

 *** (2) TRANSACTION:
 TRANSACTION 291891, ACTIVE 4 sec starting index read
 mysql tables in use 1, locked 1
 22 lock struct(s), heap size 1136, 10 row lock(s), undo log entries 8
 MySQL thread id 13972, OS thread handle 47104466163456, query id 4178089 172.29.25.88 bpapi updating
 UPDATE APPLICATION SET APPLICATION_NUMBER=IFNULL('001590', APPLICATION_NUMBER), STATUS=IFNULL('1003', STATUS), SUB_STATUS=IFNULL('1003', SUB_STATUS), CRM_STATUS=IFNULL('SUCCESS', CRM_STATUS), CRM_REASON=IFNULL('Record Created Successfully', CRM_REASON) WHERE ID='eddf88a8-c734-4ea3-96e0-9cf424ced71e'
 *** (2) HOLDS THE LOCK(S):
 RECORD LOCKS space id 3803 page no 21 n bits 144 index PRIMARY of table `BAPIDB`.`APPLICATION` trx id 291891 lock mode S locks rec but not gap
 Record lock, heap no 72 PHYSICAL RECORD: n_fields 42; compact format; info bits 128
  0: len 30; hex 65646466383861382d633733342d346561332d393665302d396366343234; asc eddf88a8-c734-4ea3-96e0-9cf424; (total 36 bytes);
  1: len 6; hex 000000046e42; asc     nB;;
  2: len 7; hex 02000000fa0518; asc        ;;
  3: len 6; hex 303031353930; asc 001590;;
  4: len 4; hex 31303033; asc 1003;;
  5: len 4; hex 31303033; asc 1003;;
  6: SQL NULL;
  7: SQL NULL;
  8: len 6; hex 506f7274616c; asc Portal;;
  9: SQL NULL;
  10: SQL NULL;
  11: SQL NULL;
  12: len 30; hex 35633131613436612d303963302d313165612d396533372d396165613961; asc 5c11a46a-09c0-11ea-9e37-9aea9a; (total 36 bytes);
  13: SQL NULL;
  14: len 5; hex 99a4ac4b92; asc    K ;;
  15: len 11; hex 427573696e657373415049; asc BusinessAPI;;
  16: SQL NULL;
  17: SQL NULL;
  18: SQL NULL;
  19: SQL NULL;
  20: SQL NULL;
  21: SQL NULL;
  22: SQL NULL;
  23: SQL NULL;
  24: SQL NULL;
  25: SQL NULL;
  26: len 7; hex 53554343455353; asc SUCCESS;;
  27: len 27; hex 5265636f72642055706461746564205375636365737366756c6c79; asc Record Updated Successfully;;
  28: SQL NULL;
  29: SQL NULL;
  30: SQL NULL;
  31: SQL NULL;
  32: SQL NULL;
  33: SQL NULL;
  34: SQL NULL;
  35: SQL NULL;
  36: SQL NULL;
  37: SQL NULL;
  38: SQL NULL;
  39: SQL NULL;
  40: SQL NULL;
  41: SQL NULL;

 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 3803 page no 21 n bits 144 index PRIMARY of table `BAPIDB`.`APPLICATION` trx id 291891 lock_mode X locks rec but not gap waiting
 Record lock, heap no 72 PHYSICAL RECORD: n_fields 42; compact format; info bits 128
  0: len 30; hex 65646466383861382d633733342d346561332d393665302d396366343234; asc eddf88a8-c734-4ea3-96e0-9cf424; (total 36 bytes);
  1: len 6; hex 000000046e42; asc     nB;;
  2: len 7; hex 02000000fa0518; asc        ;;
  3: len 6; hex 303031353930; asc 001590;;
  4: len 4; hex 31303033; asc 1003;;
  5: len 4; hex 31303033; asc 1003;;
  6: SQL NULL;
  7: SQL NULL;
  8: len 6; hex 506f7274616c; asc Portal;;
  9: SQL NULL;
  10: SQL NULL;
  11: SQL NULL;
  12: len 30; hex 35633131613436612d303963302d313165612d396533372d396165613961; asc 5c11a46a-09c0-11ea-9e37-9aea9a; (total 36 bytes);
  13: SQL NULL;
  14: len 5; hex 99a4ac4b92; asc    K ;;
  15: len 11; hex 427573696e657373415049; asc BusinessAPI;;
  16: SQL NULL;
  17: SQL NULL;
  18: SQL NULL;
  19: SQL NULL;
  20: SQL NULL;
  21: SQL NULL;
  22: SQL NULL;
  23: SQL NULL;
  24: SQL NULL;
  25: SQL NULL;
  26: len 7; hex 53554343455353; asc SUCCESS;;
  27: len 27; hex 5265636f72642055706461746564205375636365737366756c6c79; asc Record Updated Successfully;;
  28: SQL NULL;
  29: SQL NULL;
  30: SQL NULL;
  31: SQL NULL;
  32: SQL NULL;
  33: SQL NULL;
  34: SQL NULL;
  35: SQL NULL;
  36: SQL NULL;
  37: SQL NULL;
  38: SQL NULL;
  39: SQL NULL;
  40: SQL NULL;
  41: SQL NULL;

 *** WE ROLL BACK TRANSACTION (2)

我的理解：

代码语言：javascript
复制
UPDATE APPLICATION
SET NO_OF_FINANCIERS_FOR_ALL_ASSET = 7
WHERE id >'' AND APPLICATION_NUMBER = '001601';

这个事务正在导致死锁，这正在等待获得lock_mode X。

代码语言：javascript
复制
UPDATE APPLICATION
SET APPLICATION_NUMBER=IFNULL('001590', APPLICATION_NUMBER),
    STATUS=IFNULL('1003', STATUS),
    SUB_STATUS=IFNULL('1003', SUB_STATUS),
    CRM_STATUS=IFNULL('SUCCESS', CRM_STATUS),
    CRM_REASON=IFNULL('Record Created Successfully', CRM_REASON)
WHERE ID='eddf88a8-c734-4ea3-96e0-9cf424ced71e';

保存锁模式S，并试图持有等待第二个更新的lock_mode X回滚。

我的问题是：

  1. 为什么第二次更新保持和lock_mode S？难道lock_mode X不就足够了吗？
  2. 它们正在更新两个不同的行，我认为lock_mode S是罪魁祸首。我说的对吗？
  3. 如何避免这个死锁。
mysql
deadlock
database-deadlocks
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-11-22 11:27:47

  1. update语句可以在辅助索引上设置共享锁，或者在第二个事务中有其他语句在更新之前设置共享锁。正如mysql手册所说：

在插入新的辅助索引记录之前和插入新的辅助索引记录之前执行重复的检查扫描时，UPDATE操作还会对受影响的辅助索引记录进行共享锁。

但是，在InnoDB 所有辅助索引也包括主键。中，副密钥锁也会影响主键。但是老实说，如果它只有一个独占的锁，那就不会改变结果。

  1. 不，你不对。mysql手册在第一个链接中还指出：

锁定读取、更新或删除通常设置在SQL语句处理过程中扫描的每个索引记录上的记录锁。语句中是否存在排除行的条件并不重要。InnoDB不记得确切的位置条件，但只知道被扫描的索引范围。

您在一条评论中写道，在application_number列上添加了唯一的索引后，发布了您的问题。这意味着您的第一个sql语句锁定整个表，因为id >''匹配所有记录，而application_number字段没有被索引。

因此，不管您的第一条sql语句只更新单个记录这一事实，您都成功地锁定了整个表。这就引出了你最后一个问题。

  1. 你不能也绝不能避免死锁:这是解决某些种族状况的一个基本特征。你可以做两件事：

( a)处理死锁错误(重新启动事务、错误消息等)。( b)尽量减少出现僵局的机会。

如何将出现死锁的可能性降到最低？使用适当的索引和标准来最小化语句锁定的记录数量；避免使用长时间运行的事务；尽量减少使用显式锁定(用于更新，用于共享)。

在application_number上添加唯一索引是一个很好的开端，但您必须使用explain验证它是否为mysql所用。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/58991635

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档