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 |
三：如何解決
先說下最終的解決方式是刪除事務和刪除動作；刪除動作是通過另外的任務去執行。
