MySQL存儲過程之事務管理

MySQL存儲過程之事務管理?

ACID:Atomic、Consistent、Isolated、Durable?
存儲程序提供了一個絕佳的機制來定義、封裝和管理事務。?

1，MySQL的事務支持?
MySQL的事務支持不是綁定在MySQL服務器本身，而是與存儲引擎相關：?

Java代碼?

1. MyISAM：不支持事務，用于只讀程序提高性能??
2. InnoDB：支持ACID事務、行級鎖、并發??
3. Berkeley?DB：支持事務??

隔離級別：?
隔離級別決定了一個session中的事務可能對另一個session的影響、并發session對數據庫的操作、一個session中所見數據的一致性?
ANSI標準定義了4個隔離級別，MySQL的InnoDB都支持：?

Java代碼?

1. READ?UNCOMMITTED：最低級別的隔離，通常又稱為dirty?read，它允許一個事務讀取還沒commit的數據，這樣可能會提高性能，但是dirty?read可能不是我們想要的??
2. READ?COMMITTED：在一個事務中只允許已經commit的記錄可見，如果session中select還在查詢中，另一session此時insert一條記錄，則新添加的數據不可見??
3. REPEATABLE?READ：在一個事務開始后，其他session對數據庫的修改在本事務中不可見，直到本事務commit或rollback。在一個事務中重復select的結果一樣，除非本事務中update數據庫。??
4. SERIALIZABLE：最高級別的隔離，只允許事務串行執行。為了達到此目的，數據庫會鎖住每行已經讀取的記錄，其他session不能修改數據直到前一事務結束，事務commit或取消時才釋放鎖。??

可以使用如下語句設置MySQL的session隔離級別：?

Java代碼?

1. SET?TRANSACTION?ISOLATION?LEVEL?{READ?UNCOMMITTED?|?READ?COMMITTED?|?REPEATABLE?READ?|?SERIALIZABLE}??

MySQL默認的隔離級別是REPEATABLE READ，在設置隔離級別為READ UNCOMMITTED或SERIALIZABLE時要小心，READ UNCOMMITTED會導致數據完整性的嚴重問題，而SERIALIZABLE會導致性能問題并增加死鎖的機率?

事務管理語句：?

Java代碼?

1. START?TRANSACTION：開始事務，autocommit設為0，如果已經有一個事務在運行，則會觸發一個隱藏的COMMIT??
2. COMMIT：提交事務，保存更改，釋放鎖??
3. ROLLBACK：回滾本事務對數據庫的所有更改，然后結束事務，釋放鎖??
4. SAVEPOINT?savepoint_name：創建一個savepoint識別符來ROLLBACK?TO?SAVEPOINT??
5. ROLLBACK?TO?SAVEPOINT?savepoint_name：回滾到從savepoint_name開始對數據庫的所有更改，這樣就允許回滾事務中的一部分，保證更改的一個子集被提交??
6. SET?TRANSACTION：允許設置事務的隔離級別??
7. LOCK?TABLES：允許顯式的鎖住一個或多個table，會隱式的關閉當前打開的事務，建議在執行LOCK?TABLES語句之前顯式的commit或rollback。我們一般所以一般在事務代碼里不會使用LOCK?TABLES??

2，定義事務?
MySQL默認的行為是在每條SQL語句執行后執行一個COMMIT語句，從而有效的將每條語句獨立為一個事務。?
在復雜的應用場景下這種方式就不能滿足需求了。?
為了打開事務，允許在COMMIT和ROLLBACK之前多條語句被執行，我們需要做以下兩步：?
1, 設置MySQL的autocommit屬性為0，默認為1?
2，使用START TRANSACTION語句顯式的打開一個事務?

如果已經打開一個事務，則SET autocommit=0不會起作用，因為START TRANSACTION會隱式的提交session中所有當前的更改，結束已有的事務，并打開一個新的事務。?

使用SET AUTOCOMMIT語句的存儲過程例子：?

Java代碼?

1. CREATE?PROCEDURE?tfer_funds??
2. ????(from_account?int,?to_account?int,?tfer_amount?numeric(10,2))??
3. BEGIN??
4. ????SET?autocommit=0;??
5. ??
6. ????UPDATE?account_balance?SET?balance=balance-tfer_amount?WHERE?account_id=from_account;??
7. ??
8. ????UPDATE?account_balance?SET?balance=balance+tfer_amount?WHERE?account_id=to_account;??
9. ??
10. ????COMMIT;??
11. END;??

使用START TRANSACITON打開事務的例子：?

Java代碼?

1. CREATE?PROCEDURE?tfer_funds??
2. ????(from_account?int,?to_account?int,?tfer_amount?numeric(10,2))??
3. BEGIN??
4. ????START?TRANSACTION;??
5. ??
6. ????UPDATE?account_balance?SET?balance=balance-tfer_amount?WHERE?account_id=from_account;??
7. ??
8. ????UPDATE?account_balance?SET?balance=balance+tfer_amount?WHERE?account_id=to_account;??
9. ??
10. ????COMMIT;??
11. END;??

通常COMMIT或ROLLBACK語句執行時才完成一個事務，但是有些DDL語句等會隱式觸發COMMIT，所以應該在事務中盡可能少用或注意一下：?

Java代碼?

1. ALTER?FUNCTION??
2. ALTER?PROCEDURE??
3. ALTER?TABLE??
4. BEGIN??
5. CREATE?DATABASE??
6. CREATE?FUNCTION??
7. CREATE?INDEX??
8. CREATE?PROCEDURE??
9. CREATE?TABLE??
10. DROP?DATABASE??
11. DROP?FUNCTION??
12. DROP?INDEX??
13. DROP?PROCEDURE??
14. DROP?TABLE??
15. UNLOCK?TABLES??
16. LOAD?MASTER?DATA??
17. LOCK?TABLES??
18. RENAME?TABLE??
19. TRUNCATE?TABLE??
20. SET?AUTOCOMMIT=1??
21. START?TRANSACTION??

3，使用Savepoint?
使用savepoint回滾難免有些性能消耗，一般可以用IF改寫?
savepoint的良好使用的場景之一是“嵌套事務”，你可能希望程序執行一個小的事務，但是不希望回滾外面更大的事務：?

Java代碼?

1. CREATE?PROCEDURE?nested_tfer_funds??
2. ????(in_from_acct???INTEGER,??
3. ?????in_to_acct?????INTEGER,??
4. ?????in_tfer_amount?DECIMAL(8,2))??
5. BEGIN??
6. ????DECLARE?txn_error?INTEGER?DEFAULT?0;??
7. ??
8. ????DECLARE?CONTINUE?HANDLER?FOR?SQLEXCEPTION?BEGIN??
9. ????????SET?txn_error=1;??
10. ????END??
11. ??
12. ????SAVEPINT?savepint_tfer;??
13. ??
14. ????UPDATE?account_balance??
15. ???????SET?balance=balance-in_tfer_amount??
16. ?????WHERE?account_id=in_from_acct;??
17. ??
18. ????IF?txn_error?THEN??
19. ????????ROLLBACK?TO?savepoint_tfer;??
20. ????????SELECT?'Transfer?aborted';??
21. ????ELSE??
22. ????????UPDATE?account_balance??
23. ???????????SET?balance=balance+in_tfer_amount??
24. ?????????WHERE?account_id=in_to_acct;??
25. ??
26. ????????IF?txn_error?THEN??
27. ????????????ROLLBACK?TO?savepoint_tfer;??
28. ????????????SELECT?'Transfer?aborted';??
29. ??
30. ????????END?IF:??
31. ????END?IF;??
32. END;??

4，事務和鎖?
事務的ACID屬性只能通過限制數據庫的同步更改來實現，從而通過對修改數據加鎖來實現。?
直到事務觸發COMMIT或ROLLBACK語句時鎖才釋放。?
缺點是后面的事務必須等前面的事務完成才能開始執行，吞吐量隨著等待鎖釋放的時間增長而遞減。?
MySQL/InnoDB通過行級鎖來最小化鎖競爭。這樣修改同一table里其他行的數據沒有限制，而且讀數據可以始終沒有等待。?
可以在SELECT語句里使用FOR UPDATE或LOCK IN SHARE MODE語句來加上行級鎖?

Java代碼?

1. SELECT?select_statement?options?[FOR?UPDATE|LOCK?IN?SHARE?MODE]??

FOR UPDATE會鎖住該SELECT語句返回的行，其他SELECT和DML語句必須等待該SELECT語句所在的事務完成?
LOCK IN SHARE MODE同FOR UPDATE，但是允許其他session的SELECT語句執行并允許獲取SHARE MODE鎖?

死鎖：?
死鎖發生于兩個事務相互等待彼此釋放鎖的情景?
當MySQL/InnoDB檢查到死鎖時，它會強制一個事務rollback并觸發一條錯誤消息?
對InnoDB而言，所選擇的rollback的事務是完成工作最少的事務（所修改的行最少）?

Java代碼?

1. mysql?>?CALL?tfer_funds(1,2,300);??
2. ERROR?1213?(40001):?Deadlock?found?when?trying?to?get?lock;?try?restarting?transaction??

死鎖在任何數據庫系統里都可能發生，但是對MySQL/InnoDB這種行級鎖數據庫而言可能性相對較少。?
可以通過使用一致的順序來鎖row或table以及讓事務保持盡可能短來減少死鎖的頻率。?
如果死鎖不容易debug，你可以向你的程序中添加一些邏輯來處理死鎖并重試事務，但這部分代碼多了以后很難維護?
所以，比較好的避免死鎖的方式是在做任何修改之前按一定的順序添加行級鎖，這樣就能避免死鎖:?

Java代碼?

1. CREATE?PROCEDURE?tfer_funds3??
2. ????(from_account?INT,?to_account?INT,?tfer_amount?NUMERIC(10,2))??
3. BEGIN??
4. ????DECLARE?local_account_id?INT;??
5. ????DECLARE?lock_cursor?CURSOR?FOR??
6. ????????SELECT?account_id??
7. ??????????FROM?account_balance??
8. ?????????WHERE?account_id?IN?(from_account,?to_account)??
9. ?????????ORDER?BY?account_id??
10. ???????????FOR?UPDATE;??
11. ??
12. ????START?TRANSACTION;??
13. ??
14. ????OPEN?lock_cursor;??
15. ????FETCH?lock_cursor?INTO?local_account_id;??
16. ??
17. ????UPDATE?account_balance??
18. ???????SET?balance=balance-tfer_amount??
19. ?????WHERE?account_id=from_account;??
20. ??
21. ????UPDATE?account_balance??
22. ???????SET?balance=balance+tfer_amount??
23. ?????WHERE?account_id=to_account;??
24. ??
25. ????CLOSE?lock_cursor;??
26. ??
27. ????COMMIT;??
28. END;??

設置死鎖ttl: innodb_lock_wait_timeout，默認為50秒?
如果你在一個事務中混合使用InnoDB和非InnoDB表，則MySQL不能檢測到死鎖，此時會拋出“lock wait timeuot”1205錯誤?

樂觀所和悲觀鎖策略：?
悲觀鎖：在讀取數據時鎖住那幾行，其他對這幾行的更新需要等到悲觀鎖結束時才能繼續?
樂觀所：讀取數據時不鎖，更新時檢查是否數據已經被更新過，如果是則取消當前更新?
一般在悲觀鎖的等待時間過長而不能接受時我們才會選擇樂觀鎖?
悲觀鎖的例子：?

Java代碼?

1. CREATE?PROCEDURE?tfer_funds??
2. ???????(from_account?INT,?to_account?INT,tfer_amount?NUMERIC(10,2),??
3. ????????OUT?status?INT,?OUT?message?VARCHAR(30))??
4. BEGIN??
5. ????DECLARE?from_account_balance?NUMERIC(10,2);??
6. ??
7. ????START?TRANSACTION;??
8. ??
9. ??
10. ????SELECT?balance??
11. ??????INTO?from_account_balance??
12. ??????FROM?account_balance??
13. ?????WHERE?account_id=from_account??
14. ???????FOR?UPDATE;??
15. ??
16. ????IF?from_account_balance>=tfer_amount?THEN??
17. ??
18. ?????????UPDATE?account_balance??
19. ????????????SET?balance=balance-tfer_amount??
20. ??????????WHERE?account_id=from_account;??
21. ??
22. ?????????UPDATE?account_balance??
23. ????????????SET?balance=balance+tfer_amount??
24. ??????????WHERE?account_id=to_account;??
25. ?????????COMMIT;??
26. ??
27. ?????????SET?status=0;??
28. ?????????SET?message='OK';??
29. ????ELSE??
30. ?????????ROLLBACK;??
31. ?????????SET?status=-1;??
32. ?????????SET?message='Insufficient?funds';??
33. ????END?IF;??
34. END;??

樂觀鎖的例子：?

Java代碼?

1. CREATE?PROCEDURE?tfer_funds??
2. ????(from_account?INT,?to_account?INT,?tfer_amount?NUMERIC(10,2),??
3. ????????OUT?status?INT,?OUT?message?VARCHAR(30)?)??
4. ??
5. BEGIN??
6. ??
7. ????DECLARE?from_account_balance????NUMERIC(8,2);??
8. ????DECLARE?from_account_balance2???NUMERIC(8,2);??
9. ????DECLARE?from_account_timestamp1?TIMESTAMP;??
10. ????DECLARE?from_account_timestamp2?TIMESTAMP;??
11. ??
12. ????SELECT?account_timestamp,balance??
13. ????????INTO?from_account_timestamp1,from_account_balance??
14. ????????????FROM?account_balance??
15. ????????????WHERE?account_id=from_account;??
16. ??
17. ????IF?(from_account_balance>=tfer_amount)?THEN??
18. ??
19. ????????--?Here?we?perform?some?long?running?validation?that??
20. ????????--?might?take?a?few?minutes?*/??
21. ????????CALL?long_running_validation(from_account);??
22. ??
23. ????????START?TRANSACTION;??
24. ??
25. ????????--?Make?sure?the?account?row?has?not?been?updated?since??
26. ????????--?our?initial?check??
27. ????????SELECT?account_timestamp,?balance??
28. ????????????INTO?from_account_timestamp2,from_account_balance2??
29. ????????????FROM?account_balance??
30. ????????????WHERE?account_id=from_account??
31. ????????????FOR?UPDATE;??
32. ??
33. ????????IF?(from_account_timestamp1?<>?from_account_timestamp2?OR??
34. ????????????from_account_balance????<>?from_account_balance2)??THEN??
35. ????????????ROLLBACK;??
36. ????????????SET?status=-1;??
37. ????????????SET?message=CONCAT("Transaction?cancelled?due?to?concurrent?update",??
38. ????????????????"?of?account"??,from_account);??
39. ????????ELSE??
40. ????????????UPDATE?account_balance??
41. ????????????????SET?balance=balance-tfer_amount??
42. ????????????????WHERE?account_id=from_account;??
43. ??
44. ????????????UPDATE?account_balance??
45. ????????????????SET?balance=balance+tfer_amount??
46. ????????????????WHERE?account_id=to_account;??
47. ??
48. ????????????COMMIT;??
49. ??
50. ????????????SET?status=0;??
51. ????????????SET?message="OK";??
52. ????????END?IF;??
53. ??
54. ????ELSE??
55. ????????ROLLBACK;??
56. ????????SET?status=-1;??
57. ????????SET?message="Insufficient?funds";??
58. ????END?IF;??
59. END$$??

5，事務設計指南?

Java代碼?

1. 1，保持事務短小??
2. 2，盡量避免事務中rollback??
3. 3，盡量避免savepoint??
4. 4，默認情況下，依賴于悲觀鎖??
5. 5，為吞吐量要求苛刻的事務考慮樂觀鎖??
6. 6，顯示聲明打開事務??
7. 7，鎖的行越少越好，鎖的時間越短越好 ?