mysql锁-从实战中理解 _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 '名称',
【mysql锁-从实战中理解】`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;
id: 1
e_id: 100
name: shijie
create_at: 2022-12-01 00:00:00
update_at: 2023-01-01 05:31:13
id: 2
e_id: 100
name: jianfeng
create_at: 2022-12-02 00:00:00
update_at: 2023-01-01 05:31:13
id: 3
e_id: 100
name: syx
create_at: 2022-12-03 00:00:00
update_at: 2023-01-01 05:31:13
id: 4
e_id: 100
name: stt
create_at: 2022-12-04 00:00:00
update_at: 2023-01-01 05:41:47
select * from epG;
id: 1
e_id: 100
name: shijie
create_at: 2022-12-01 00:00:00
update_at: 2023-01-01 05:31:13
id: 2
e_id: 100
name: jianfeng
create_at: 2022-12-02 00:00:00
update_at: 2023-01-01 05:31:13
id: 3
e_id: 100
name: syx
create_at: 2022-12-03 00:00:00
update_at: 2023-01-01 05:31:13
id: 5
e_id: 100
name: ssd
create_at: 2022-12-04 00:00:00
update_at: 2023-01-01 05:44:46

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的插入的数据就锁等待了。