MYSQL“Lock wait timeout exceeded”问题分析_在同一事务内先后对同一条数据进行插入和更新操作-CSDN博客

本文详细分析了MySQL中出现LockWaitTimeoutExceeded错误的原因，包括未提交的DML操作、索引设计不当、长事务等。问题集中在表ti_lockdata_mid上，由于多个DDL操作导致锁表。通过实验验证，在无索引情况下，DDL操作确实可能导致锁表。解决方案是为查询条件字段添加联合索引，从而避免锁表并减少锁等待时间。最终，添加索引成功解决了问题。

MYSQL “Lock wait timeout exceeded” 问题分析处理

首先导致锁表原因可能有以下原因：

执行DML操作没有commit，再执行删除操作就会锁表。
在同一事务内先后对同一条数据进行插入和更新操作。
表索引设计不当，导致数据库出现死锁。
长事物，阻塞DDL，继而阻塞所有同表的后续操作。

问题分析处理过程

数据库引擎：

在这里插入图片描述

表结构：

CREATE TABLE `ti_lockdata_mid` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '序号:',
  `CUSTOMER_CODE` varchar(10) DEFAULT NULL COMMENT '客户:',
  `INVOICE_NO` varchar(20) NOT NULL COMMENT '单据号:',
  `LINE_NO` varchar(10) NOT NULL COMMENT '行号:',
  `INVOICE_TYPE` varchar(2) DEFAULT NULL COMMENT '单据类型:10-排序供货;20-订单供货;30-0公里退货;',
  `CUSTOMER_FACTORY` varchar(20) DEFAULT NULL COMMENT '客户工厂:',
  `SN` varchar(20) DEFAULT NULL,
  `REMARK` varchar(1024) DEFAULT NULL COMMENT '备注:',
  `CREATE_TIME` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间:',
  `UPDATE_TIME` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) COMMENT '更新时间:',
  `PRDPLANT_CODE` varchar(4) DEFAULT NULL COMMENT '生产工厂:',
  `SHIPSTORE_CODE` varchar(4) DEFAULT NULL COMMENT '发货库位:',
  `SAP_UNIQUE_NO` varchar(50) DEFAULT NULL COMMENT 'SAP唯一识别编码号',
  `SAP_UNIQUE_LINE_NO` varchar(10) DEFAULT NULL COMMENT 'SAP唯一识别编码行号',
  PRIMARY KEY (`ID`,`INVOICE_NO`,`LINE_NO`) USING BTREE,
  KEY `INVOICE_NO` (`INVOICE_NO`) USING BTREE,
  KEY `LINE_NO` (`LINE_NO`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=39411635 DEFAULT CHARSET=utf8 COMMENT='锁定数据中间表'

问题描述：在多个ddl同时操作表【ti_lockdata_mid】时发生锁表，且发生锁等待超时。

问题背景：我们系统是一个提供开票数据的系统，然后系统所有的操作流程都是通过这个lock表进行数据锁定，即一个流程只能操作同一批数据，发送该问题时ti_lockdata_mid表因为多个流程正在ddl该表，此时就发生了锁表，锁排队的情况，超默认锁等待时长则就抛Error。

那么问题就来了，按道理来讲InnoDB引擎在操作不同记录数据时不会锁表，那为啥这个地方会出现锁表问题呢？数据库默认锁等待时长50s 那为啥锁表这么长时间？

问题时间线：

看日志发现在锁表前后一直在反馈（删除）一批数据，这批数据我看了下大概20w左右，每次2w，但是时不同key删除。

请添加图片描述

问题分析：猜想在mysql中，ddl同一个表时，查询条件没有索引主键，会不会发生锁表？因为删除sql时用的是五个key联合查询，上述表结构也没有联合索引主键！

猜想实践：

插入测试数据

INSERT INTO `shac_rps`.`ti_lockdata_mid` (`ID`, `CUSTOMER_CODE`, `INVOICE_NO`, `LINE_NO`, `INVOICE_TYPE`, `CUSTOMER_FACTORY`, `SN`, `REMARK`, `CREATE_TIME`, `UPDATE_TIME`, `PRDPLANT_CODE`, `SHIPSTORE_CODE`, `SAP_UNIQUE_NO`, `SAP_UNIQUE_LINE_NO`) VALUES (42042080, '10000967', '5947016987', '10', '30', '6200', '15', NULL, '2022-11-11 18:03:01.002376', '2022-11-11 18:03:01.002376', 'HM03', 'HMW4', 'S20221111180511945', '3');
INSERT INTO `shac_rps`.`ti_lockdata_mid` (`ID`, `CUSTOMER_CODE`, `INVOICE_NO`, `LINE_NO`, `INVOICE_TYPE`, `CUSTOMER_FACTORY`, `SN`, `REMARK`, `CREATE_TIME`, `UPDATE_TIME`, `PRDPLANT_CODE`, `SHIPSTORE_CODE`, `SAP_UNIQUE_NO`, `SAP_UNIQUE_LINE_NO`) VALUES (42042081, '10000967', '5947016987', '20', '30', '6200', '15', NULL, '2022-11-11 18:03:01.002376', '2022-11-11 18:03:01.002376', 'HM03', 'HMW4', 'S20221111180511945', '3');
INSERT INTO `shac_rps`.`ti_lockdata_mid` (`ID`, `CUSTOMER_CODE`, `INVOICE_NO`, `LINE_NO`, `INVOICE_TYPE`, `CUSTOMER_FACTORY`, `SN`, `REMARK`, `CREATE_TIME`, `UPDATE_TIME`, `PRDPLANT_CODE`, `SHIPSTORE_CODE`, `SAP_UNIQUE_NO`, `SAP_UNIQUE_LINE_NO`) VALUES (42042082, '10000967', '5947016987', '30', '30', '6200', '15', NULL, '2022-11-11 18:03:01.002376', '2022-11-11 18:03:01.002376', 'HM03', 'HMW4', 'S20221111180511945', '3');

在navicat中新建查询，起一个事物执行删除某条数据，但不提交事物


START transaction ;
-- delete from ti_lockdata_mid where INVOICE_NO = '5947016770'

-- select * FROM ti_lockdata_mid  WHERE (INVOICE_NO,LINE_NO,CUSTOMER_CODE,INVOICE_TYPE,CUSTOMER_FACTORY) IN (('5947016987','10','10000967','30','6200'),('5947016987','20','10000967','30','6200'),('5947016987','30','10000967','30','6200'))
delete FROM ti_lockdata_mid  WHERE (INVOICE_NO,LINE_NO,CUSTOMER_CODE,INVOICE_TYPE,CUSTOMER_FACTORY) IN (('5947016987','10','10000967','30','6200'),('5947016987','20','10000967','30','6200'))

COMMIT;

再新建一个查询执行

DELETE FROM ti_lockdata_mid  WHERE (INVOICE_NO,LINE_NO,CUSTOMER_CODE,INVOICE_TYPE,CUSTOMER_FACTORY) IN (('5947016987','30','10000967','20','6200'))

则结果：
请添加图片描述
得出结论：在innoDB引擎模式中，表在无索引的情况下，同时ddl同一个表会出现锁表情况，且锁等待超默认锁等待时长则就会出现标题Error。问题就是在文章开头中的第四条。

解决方案：给对应查询条件字段加上索引即可。这里是联合条件查询，添加一个联合索引，要注意一定要使这个索引失效就可以了，联合索引满足最左原则即可。

create INDEX lock_union_index
on ti_lockdata_mid(INVOICE_NO,LINE_NO,CUSTOMER_CODE,INVOICE_TYPE,CUSTOMER_FACTORY)

最后问题解决，不会出现锁表。如果出现操作同一批记录数，最多产生行锁。