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;
　　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的插入的数据 就锁等待了。思考：一：当我把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;
　　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-02 00:00:00＂;
　　no lock 三：如何解决
　　先说下最终的解决方式是删除事务和删除动作；删除动作是通过另外的任务去执行。