Aaron Yang

其實看參考或官網就可以了,

但因為是英文所以忘了不想再慢慢看一次,

記著會比較方便使用.

基本的SP架構如下,

DELIMITER |
 
CREATE PROCEDURE DoAndHandleDeadlock ()
BEGIN
    DECLARE TrashVar INTEGER UNSIGNED;
     
    DECLARE EXIT HANDLER
        FOR SQLSTATE '40001' # (ER_LOCK_DEADLOCK) Retry when deadlock occured
        BEGIN
            ROLLBACK AND NO CHAIN; # or COMMIT AND NO CHAIN; 
             
            SELECT SLEEP(FLOOR(RAND() * 5)) INTO TrashVar;
                     
            CALL DoAndHandleDeadlock ();
        END;
 
    SET autocommit := 0;
    START TRANSACTION;
 
        /*取代成該預存程序所需處裡的邏輯部分*/
        SELECT * FROM Test; # Read lock on all rows
        UPDATE Test SET Id = Id + 10; # Trying to upgrade to write lock
 
    COMMIT;
    SET autocommit := 1;
END|
 
DELIMITER ;

額外設定max_sp_recursion_depth = 20; (這是參考文章建議的值

因為重試功能的部分是使用遞迴,如果無限制的重試下去降耗盡系統資源.

鶮設一個上限值.

解釋幾個部分,

1. DECLARE EXIT HANDLER FOR SQLSTATE '40001', 當此SP交易被選定為Deadlock 犧牲者時,觸發HANDLER處理程序.

2. commit/rollback AND NO CHAIN,  指當前交易結束後,不馬上另起一個交易, 等同於 completion_type 變數是預設值(NO CHAIN或0),  

                                                            如果是AND CHAIN則代表馬上另起一個同隔離層級的交易. 還可以設定RELAEASE代表交易結束後,立馬中斷當前使用者的session連線.

3. autocommit := 0/1, 設定mariadb是否自動commit,預設是啟用(1),指在update完資料後盡可能快的將此資料永久的commit到硬碟去.

4. max_sp_recursion_depth = 20, 預設為0,

參考

Deadlock handling in MySQL stored procedures