MySQL鎖可能是數(shù)據(jù)庫(kù)知識(shí)篇幅中普遍比較難理解的一個(gè)知識(shí)點(diǎn)！以前對(duì)鎖理解的也是停留在八股文的的階段,經(jīng)歷了這次生產(chǎn)問(wèn)題之后重新學(xué)習(xí)了
問(wèn)題表現(xiàn)：
早上剛到公司還沒進(jìn)入狀態(tài)，就被拉進(jìn)一個(gè)群（dba找上門了）說(shuō)數(shù)據(jù)庫(kù)有大量鎖等待異常。
代碼中的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
事故現(xiàn)場(chǎng)
場(chǎng)景是在并發(fā)消費(fèi)kafka數(shù)據(jù)的時(shí)候，起了一個(gè)事務(wù)，事務(wù)里先插入今天的數(shù)據(jù)，然后刪除數(shù)據(jù)；
CREATE TABLE `ep` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`ep_id` int(11) NOT NULL COMMENT '企業(yè)ID',
`name` varchar(255) NOT NULL COMMENT '名稱',
`create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
`update_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時(shí)間',
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 |
這里粘貼一張復(fù)現(xiàn)的圖：
事故還原
初始化記錄
兩個(gè)session分別執(zhí)行一條插入后查看的鎖記錄：
session1執(zhí)行delete語(yǔ)句的鎖記錄：
此時(shí)session2的鎖記錄：
可以明顯的看出：
表中最大的記錄id是6，事務(wù)中插入的記錄id是15，session1鎖住的記錄id最大到10，其實(shí)當(dāng)我存量數(shù)據(jù)id是連續(xù)的時(shí)候，session1會(huì)把我剛插入的id也會(huì)鎖住，這也是我一直不理解的地方。
google中提了一個(gè)問(wèn)題：
https://stackoverflow.com/questions/74972932/why-does-innodb-lock-more-records-when-range-deletion；大致回答的意思就是說(shuō)：其實(shí)不管大范圍還是小范圍都一樣 在根據(jù)ep_id和create_at篩選刪除時(shí)，如果大范圍時(shí)，會(huì)先挑ep_id=100先篩選 而且是邊篩選邊變鎖，把篩到的結(jié)果就要鎖住 ep_id=100的篩選結(jié)果包含session2的插入的數(shù)據(jù) 就鎖等待了。
思考：
一：當(dāng)我把delete語(yǔ)句中的時(shí)間范圍縮小時(shí)，還是會(huì)鎖住剛插入的記錄嗎？
答案是不會(huì)的，只會(huì)鎖住符合條件的記錄。 我想這也是符合預(yù)期的鎖記錄。
二：思考
1）、把插入語(yǔ)句和刪除語(yǔ)句調(diào)換位置
在事務(wù)中先執(zhí)行刪除，雖然說(shuō)session2也會(huì)產(chǎn)生鎖等待，但是session1同樣可以提交不會(huì)報(bào)錯(cuò)
2）、刪除不能使用二級(jí)索引，應(yīng)盡量使用聚簇索引
3）、如果初始化記錄中的id不連續(xù)，sessio1執(zhí)行delete語(yǔ)句的時(shí)候是不是不會(huì)鎖住插入的記錄
|
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 |
三：如何解決
先說(shuō)下最終的解決方式是刪除事務(wù)和刪除動(dòng)作；刪除動(dòng)作是通過(guò)另外的任務(wù)去執(zhí)行。
