mysql锁-从实战中理解
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;
|
Session1 |
Session2 |
Locks |
|
begin; |
begin; |
|
|
insert into ep(e_id, name, create_at) values(100, 'stt', '2022-12-04 00:00:00'); |
|
|
|
|
insert into ep(e_id, name, create_at) values(100, 'ssd', '2022-12-04 00:00:00'); |
|
|
select * from epG; |
select * from epG; |
|
|
delete from ep where e_id=100 and create_at <= '2022-12-03 00:00:00'; |
|
lock waiting&dead lock |
这里粘贴一张复现的图：
事故还原
初始化记录
两个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语句的时候是不是不会锁住插入的记录
|
Session1 |
Session2 |
Locks |
|
begin; |
begin; |
|
|
insert into ep(e_id, name, create_at) values(100, 'stt', '2022-12-04 00:00:00'); |
|
|
|
|
insert into ep(e_id, name, create_at) values(100, 'ssd', '2022-12-04 00:00:00'); |
|
|
select * from epG; |
select * from epG; |
|
|
delete from ep where e_id=100 and create_at <= '2022-12-02 00:00:00'; |
|
no lock |
三：如何解决
先说下最终的解决方式是删除事务和删除动作；删除动作是通过另外的任务去执行。
