MySQL過程慎用if not exists寫法

壹頁書發表於2017-02-20
MySql
接前文
http://blog.itpub.net/29254281/viewspace-2133705/

環境MySQL 5.6.14
事務隔離級別 讀提交
事務的開啟和結束由JAVA程式控制.

上次報死鎖的過程,抽象如下

  1. delimitr $$
  2. CREATE PROCEDURE `test_proc`(
  3.     pid int
  4. )
  5. begin
  6.     if not exists (select * from t where id=pid) then
  7.         insert into t(id) values(pid);
  8.     end if;
  9.     
  10.     update t set total=total+1 where id=pid;
  11. end $$
  12. 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判斷,繼續執行！！

實驗準備


  1. CREATE TABLE `t` (
  2.   `id` int(11) NOT NULL,
  3.   `total` int(11) NOT NULL DEFAULT '0',
  4.   PRIMARY KEY (`id`)
  5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

  7. truncate table t;
  8. drop procedure if exists test_proc;
  9. delimiter $$
  10. CREATE PROCEDURE `test_proc`(
  11.     pid int,
  12.     ptotal int
  13. )
  14. begin
  15.     if not exists (select * from t where id=pid) then
  16.         insert into t(id,total) value(pid,ptotal);
  17.         update t set total=ptotal+1 where id=pid;
  18.     end if;
  19.     select ptotal+1;    
  20. end $$
  21. 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應用程式直接回滾了事.


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-2133936/，如需轉載，請註明出處，否則將追究法律責任。

相關文章