共計 1499 個字符,預計需要花費 4 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下 MySQL 中使用 if not exists 需要注意什么,希望大家閱讀完這篇文章之后都有所收獲,下面讓我們一起去探討吧!
環境 MySQL 5.6.14
事務隔離級別 讀提交
事務的開啟和結束由 JAVA 程序控制.
上次報死鎖的過程, 抽象如下
delimitr $$
CREATE PROCEDURE `test_proc`(
pid int
)
begin
if not exists (select * from t where id=pid) then
insert into t(id) values(pid);
end if;
update t set total=total+1 where id=pid;
end $$
delimiter ;
死鎖原因已經明白了,就是并發情況下,Insert 遇到排它鎖, 則嘗試加共享鎖。
在最后 Update 的時候, 兩個持有共享鎖的連接, 都嘗試申請排它鎖, 則導致了死鎖.
但是問題是 … 怎么會走到了最后一行的 Update 語句?
另外兩個連接, 不是應該在 Insert 語句時報 Duplicate entry xx for key PRIMARY 錯誤嗎?
問題應該出在這種結構里
if not exists (select * from t where id=pid) then
xxx
end if;
使用 if not exists 模式, 真心要注意啊. 在這種結構里出現的異常, 不會報錯, 而是直接跳出 IF 判斷, 繼續執行!!
實驗準備
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`total` int(11) NOT NULL DEFAULT 0 ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
truncate table t;
drop procedure if exists test_proc;
delimiter $$
CREATE PROCEDURE `test_proc`(
pid int,
ptotal int
)
begin
if not exists (select * from t where id=pid) then
insert into t(id,total) value(pid,ptotal);
update t set total=ptotal+1 where id=pid;
end if;
select ptotal+1;
end $$
delimiter ;
打開三個客戶端,分別執行過程
第一個客戶端執行,并沒有提交.
第二,第三客戶端處于阻塞狀態.
等第一個客戶端提交,
第二個客戶端返回 201
第三個客戶端返回 301
且沒有任何的報錯信息.
三個客戶端都提交之后,查看 T 表信息
只有一個記錄,id 為 1,total 為 101
也就是說, 第二個, 第三個客戶端, 在得到主鍵沖突的異常后, 沒有報錯, 沒有繼續執行 IF 塊內剩下的語句, 而是直接跳出了 IF 塊, 繼續執行 IF 塊外的語句!!
該報錯的地方不報錯, 在大段的存儲過程中, 導致死鎖還是小問題, 就怕引起數據的錯亂, 而不自知.
針對這種情況, 如果有主鍵或者唯一約束, 我覺得干脆改為如下的方式.
delimiter $$
CREATE PROCEDURE `test_proc`(
pid int,
ptotal int
)
begin
insert into t(id,total) value(pid,ptotal);
update t set total=ptotal+1 where id=pid;
select ptotal+1;
end $$
delimiter ;
看完了這篇文章,相信你對“MySQL 中使用 if not exists 需要注意什么”有了一定的了解,如果想了解更多相關知識,歡迎關注丸趣 TV 行業資訊頻道,感謝各位的閱讀!