MySQL锁可能是数据库知识篇幅中普遍比较难理解的一个知识点！以前对锁理解的也是停留在八股文的的阶段,经历了这次生产问题之后重新学习了

问题表现：

早上刚到公司还没进入状态，就被拉进一个群（dba找上门了）说数据库有大量锁等待异常。

代码中的sql: delete from
order_point_line_statistics where ep_id = 376330219 and created_at <= '2022-12-15 00:00:00'

Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ### The error may exist in class path resource [MyBatis/mApper/ext/OrderPointLineStatisticsExtMapper.xml]

The error occurred while setting parameters ### SQL: delete from order_point_line_statistics where ep_id = ? and created_at <= ? ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction ; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

事故现场

场景是在并发消费kafka数据的时候，起了一个事务，事务里先插入今天的数据，然后删除数据；

CREATE TABLE `ep` (

`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键',

`ep_id` int(11) NOT NULL COMMENT '企业ID',

`name` varchar(255) NOT NULL COMMENT '名称',

`create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

`update_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',

PRIMARY KEY (`id`),

KEY `idx_ep_id` (`ep_id`) USING BTREE,

KEY `idx_create_at` (`create_at`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

 

这里粘贴一张复现的图：

事故还原

初始化记录

两个session分别执行一条插入后查看的锁记录：

session1执行delete语句的锁记录：

此时session2的锁记录：

可以明显的看出：

表中最大的记录id是6，事务中插入的记录id是15，session1锁住的记录id最大到10，其实当我存量数据id是连续的时候，session1会把我刚插入的id也会锁住，这也是我一直不理解的地方。

google中提了一个问题：
https://stackoverflow.com/questions/74972932/why-does-innodb-lock-more-records-when-range-deletion；大致回答的意思就是说：其实不管大范围还是小范围都一样 在根据ep_id和create_at筛选删除时，如果大范围时，会先挑ep_id=100先筛选 而且是边筛选边变锁，把筛到的结果就要锁住 ep_id=100的筛选结果包含session2的插入的数据 就锁等待了。

思考：

一：当我把delete语句中的时间范围缩小时，还是会锁住刚插入的记录吗？

答案是不会的，只会锁住符合条件的记录。 我想这也是符合预期的锁记录。

二：思考

1）、把插入语句和删除语句调换位置

在事务中先执行删除，虽然说session2也会产生锁等待，但是session1同样可以提交不会报错

2）、删除不能使用二级索引，应尽量使用聚簇索引

3）、如果初始化记录中的id不连续，sessio1执行delete语句的时候是不是不会锁住插入的记录

三：如何解决

先说下最终的解决方式是删除事务和删除动作；删除动作是通过另外的任务去执行。