接前文
http://blog.itpub.net/29254281/viewspace-2133705/

環境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 ;

簡單,粗暴,易懂...反正主鍵衝突了,JAVA應用程式直接回滾了事.

MySQL過程慎用if not exists寫法

相關文章