MySQL存儲(chǔ)過(guò)程之事務(wù)管理

1、MySQL存儲(chǔ)過(guò)程之事務(wù)管理MySQL存儲(chǔ)過(guò)程之事務(wù)管理ACID:Atomic、Consistent、Isolated、Durable存儲(chǔ)程序提供了一個(gè)絕佳的機(jī)制來(lái)定義、封裝和管理事務(wù)。1, MySQL的事務(wù)支持MySQL的事務(wù)支持不是綁定在MySQL服務(wù)器本身，而是與存儲(chǔ)引擎相關(guān)：Java代碼1 MyISAM:不支持事務(wù)，用于只讀程序提高性能2 InnoDB:支持ACID事務(wù)、行級(jí)鎖、并發(fā)3 BerkeleyDB:支持事務(wù)隔離級(jí)別：隔離級(jí)別決定了一個(gè)session中的事務(wù)可能對(duì)另一個(gè)session的影響、并發(fā)session對(duì)數(shù)據(jù)庫(kù)的操作、一個(gè)session中所見(jiàn)數(shù)據(jù)的一致性ANSI標(biāo)準(zhǔn)定義了

2、4個(gè)隔離級(jí)別，MySQL的InnoDB都支持：Java代碼4 READUNCOMMITTED:最低級(jí)別的隔離，通常又稱為dirtyread,它允許一個(gè)事務(wù)讀取還沒(méi)commit的數(shù)據(jù)，這樣可能會(huì)提高性能，但是dirtyread可能不是我們想要的5 READCOMMITTED:在一個(gè)事務(wù)中只允許已經(jīng)commit的記錄可見(jiàn)，如果session中select還在查詢中，另一session此時(shí)insert一條記錄，則新添加的數(shù)據(jù)不可見(jiàn)6 REPEATABLEREAD:在一個(gè)事務(wù)開(kāi)始后，其他session對(duì)數(shù)據(jù)庫(kù)的修改在本事務(wù)中不可見(jiàn)，直到本事務(wù)commit或rollback。在一個(gè)事務(wù)中重復(fù)select

3、的結(jié)果一樣，除非本事務(wù)中update數(shù)據(jù)庫(kù)。7 SERIALIZABLE:最高級(jí)別的隔離，只允許事務(wù)串行執(zhí)行。為了達(dá)到此目的，數(shù)據(jù)庫(kù)會(huì)鎖住每行已經(jīng)讀取的記錄，其他session不能修改數(shù)據(jù)直到前一事務(wù)結(jié)束，事務(wù)commit或取消時(shí)才釋放鎖?？梢允褂萌缦抡Z(yǔ)句設(shè)置MySQL的session隔離級(jí)別：Java代碼只SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED|READCOMMITTED|REPEATABLEREAD|SERIALIZABLEMySQL默認(rèn)的隔離級(jí)別是REPEATABLEREAD,在設(shè)置隔離級(jí)別為READUNCOMMITTED或SERIALIZ

4、ABLE時(shí)要小心，READUNCOMMITTED會(huì)導(dǎo)致數(shù)據(jù)完整性的嚴(yán)重問(wèn)題，而SERIALIZABLE會(huì)導(dǎo)致性能問(wèn)題并增加死鎖的機(jī)率事務(wù)管理語(yǔ)句：Java代碼9 STARTTRANSACTION:開(kāi)始事務(wù)，autocommit設(shè)為0,如果已經(jīng)有一個(gè)事務(wù)在運(yùn)行，則會(huì)觸發(fā)一個(gè)隱藏的COMMIT10 COMMIT:提交事務(wù)，保存更改，釋放鎖11 ROLLBACK:回滾本事務(wù)對(duì)數(shù)據(jù)庫(kù)的所有更改，然后結(jié)束事務(wù)，釋放鎖12 SAVEPOINTsavepoint_name:創(chuàng)建一個(gè)savepoint識(shí)別符來(lái)ROLLBACKTOSAVEPOINT13 ROLLBACKTOSAVEPOINTsavepoint_

5、name:回滾到從savepoint_name開(kāi)始對(duì)數(shù)據(jù)庫(kù)的所有更改，這樣就允許回滾事務(wù)中的一部分，保證更改的一個(gè)子集被提交HSETTRANSACTION:允許設(shè)置事務(wù)的隔離級(jí)別15LOCKTABLES:允許顯式的鎖住一個(gè)或多個(gè)table,會(huì)隱式的關(guān)閉當(dāng)前打開(kāi)的事務(wù)，建議在執(zhí)行LOCKTABLES語(yǔ)句之前顯式的commit或rollback。我們一般所以一般在事務(wù)代碼里不會(huì)使用LOCKTABLES2,定義事務(wù)MySQL默認(rèn)的行為是在每條SQL語(yǔ)句執(zhí)行后執(zhí)行一個(gè)COMMIT語(yǔ)句，從而有效的將每條語(yǔ)句獨(dú)立為一個(gè)事務(wù)。在復(fù)雜的應(yīng)用場(chǎng)景下這種方式就不能滿足需求了。為了打開(kāi)事務(wù)，允許在COMMIT和RO

6、LLBACK之前多條語(yǔ)句被執(zhí)行，我們需要做以下兩步：1,設(shè)置MySQL的autocommit屬性為0,默認(rèn)為12,使用STARTTRANSACTION語(yǔ)句顯式的打開(kāi)一個(gè)事務(wù)如果已經(jīng)打開(kāi)一個(gè)事務(wù)，則SETautocommit=0不會(huì)起作用，因?yàn)镾TARTTRANSACTION會(huì)隱式的提交session中所有當(dāng)前的更改，結(jié)束已有的事務(wù)，并打開(kāi)一個(gè)新的事務(wù)。使用SETAUTOCOMMIT語(yǔ)句的存儲(chǔ)過(guò)程例子：Java代碼ICREATEPROCEDUREtfer_funds17(from_accountint,to_accountint,tfer_amountnumeric(10,2)i齊BEGIN.l

7、<SETautocommit=0;2D21UPDATEaccount_balanceSETbalance=balance-tfer_amountWHEREaccount_id=from_account;2223UPDATEaccount_balanceSETbalance=balance+tfer_amountWHEREaccount_id=to_account;24COMMIT;二END;使用STARTTRANSACITON打開(kāi)事務(wù)的例子:Java代碼二CREATEPROCEDUREtfer_funds28(from_accountint,to_account刀BEGINW。START

8、TRANSACTION;3132UPDATEaccount_balanceaccount_id=from_account;3334UPDATEaccount_balanceaccount_id=to_account;int,tfer_amountnumeric(10,2)SETbalance=balance-tfer_amountWHERESETbalance=balance+tfer_amountWHERE35S"COMMIT;5END;通常COMMIT或ROLLBACK語(yǔ)句執(zhí)行時(shí)才完成一個(gè)事務(wù)，但是有些DDL語(yǔ)句等會(huì)隱式觸發(fā)COMMIT,所以應(yīng)該在事務(wù)中盡可能少用或注意一下：Ja

9、va代碼ALTERFUNCTIONS?ALTERPROCEDUREALTERTABLE4iBEGIN:二CREATEDATABASESCREATEFUNCTIONMCREATEINDEX"CREATEPROCEDURE:CREATETABLE4：DROPDATABASE有DROPFUNCTIONDROPINDEX、。DROPPROCEDURE工DROPTABLE、二UNLOCKTABLES、3LOADMASTERDATALOCKTABLES:畢RENAMETABLE、TRUNCATETABLE:;SETAUTOCOMMIT=1rSTARTTRANSACTION3,使用Savepoin

10、t使用savepoint回滾難免有些性能消耗，一般可以用IF改寫(xiě)savepoint的良好使用的場(chǎng)景之一是嵌套事務(wù)”，你可能希望程序執(zhí)行一個(gè)小的事務(wù)，但是不希望回滾外面更大的事務(wù)：Java代碼CREATEPROCEDUREnestedtferfunds(in_from_acctINTEGER,in_to_acctINTEGER,in_tfer_amountDECIMAL(8,2)BEGINDECLAREtxn_errorINTEGERDEFAULT0;DECLARECONTINUEHANDLERFORSQLEXCEPTIONBEGINSETtxn_error=1;ENDSAVEPINTsavep

11、int_tfer;UPDATEaccount_balanceSETbalance=balance-in_tfer_amountWHEREaccount_id=in_from_acct;IFtxn_errorTHENROLLBACKTOsavepoint_tfer;SELECT'Transferaborted'59fo616263口656667煞因7071727374757677小為80ELSE凱SETbalance=balance+in_tfer_amountS2WHEREaccount_id=in_to_acct;S384 IFtxn_errorTHEN85 ROLLBACK

12、TOsavepoint_tfer;SELECT'Transferaborted'87送ENDIF:入ENDIF;:。END;4,事務(wù)和鎖事務(wù)的ACID屬性只能通過(guò)限制數(shù)據(jù)庫(kù)的同步更改來(lái)實(shí)現(xiàn)，從而通過(guò)對(duì)修改數(shù)據(jù)加鎖來(lái)實(shí)現(xiàn)。直到事務(wù)觸發(fā)COMMIT或ROLLBACK語(yǔ)句時(shí)鎖才釋放。缺點(diǎn)是后面的事務(wù)必須等前面的事務(wù)完成才能開(kāi)始執(zhí)行，吞吐量隨著等待鎖釋放的時(shí)間增長(zhǎng)而遞減。MySQL/InnoDB通過(guò)行級(jí)鎖來(lái)最小化鎖競(jìng)爭(zhēng)。這樣修改同一table里其他行的數(shù)據(jù)沒(méi)有限制，而且讀數(shù)據(jù)可以始終沒(méi)有等待。可以在SELECT語(yǔ)句里使用FORUPDATE或LOCKINSHAREMODE語(yǔ)句來(lái)加上行級(jí)鎖

13、Java代碼91SELECTselect_statementoptionsFORUPDATE|LOCKINSHAREMODEFORUPDATE會(huì)鎖住該SELECT語(yǔ)句返回的行，其他SELECT和DML語(yǔ)句必須等待該SELECT語(yǔ)句所在的事務(wù)完成LOCKINSHAREMODE同F(xiàn)ORUPDATE，但是允許其他session的SELECT語(yǔ)句執(zhí)行并允許獲取SHAREMODE鎖死鎖：死鎖發(fā)生于兩個(gè)事務(wù)相互等待彼此釋放鎖的情景當(dāng)MySQL/InnoDB檢查到死鎖時(shí)，它會(huì)強(qiáng)制一個(gè)事務(wù)rollback并觸發(fā)一條錯(cuò)誤消息對(duì)InnoDB而言，所選擇的rollback的事務(wù)是完成工作最少的事務(wù)(所修改的行最少)

14、Java代碼mysql>CALLtfer_funds(1,2,300);ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransaction死鎖在任何數(shù)據(jù)庫(kù)系統(tǒng)里都可能發(fā)生，但是對(duì)MySQL/InnoDB這種行級(jí)鎖數(shù)據(jù)庫(kù)而言可能性相對(duì)較少。可以通過(guò)使用一致的順序來(lái)鎖row或table以及讓事務(wù)保持盡可能短來(lái)減少死鎖的頻率。如果死鎖不容易debug,你可以向你的程序中添加一些邏輯來(lái)處理死鎖并重試事務(wù)，但這部分代碼多了以后很難維護(hù)所以，比較好的避免死鎖的方式是在做任何修改之前按一定的順序添加行級(jí)鎖，這樣就能避免死

15、鎖：Java代碼國(guó)CREATEPROCEDUREtfer_funds395(from_accountINT,to_accountINT,tfer_amountNUMERIC(10,2):-BEGIN97DECLARElocal_account_idINT;弟DECLARElock_cursorCURSORFOR的SELECTaccount_idICOFROMaccount_balance101 WHEREaccount_idIN(from_account,to_account)102 ORDERBYaccount_id.1，5FORUPDATE;18IDSTARTTRANSACTION;1C6

16、OPOPENlock_cursor;IOSFETCHlock_cursorINTOlocal_account_id;109110UPDATEaccount_balance,1.1.1SETbalance=balance-tfer_amount112WHEREaccount_id=from_account;113114UPDATEaccount_balance,1.1-SETbalance=balance+tfer_amount116WHEREaccount_id=to_account;117IISCLOSElock_cursor;119一工COMMIT;.r.1END;設(shè)置死鎖ttl:inno

17、db_lock_wait_timeout,默認(rèn)為50秒如果你在一個(gè)事務(wù)中混合使用InnoDB和非InnoDB表，則MySQL不能檢測(cè)到死鎖，此時(shí)會(huì)拋出"lockwaittimeuot”1205樂(lè)觀所和悲觀鎖策略：悲觀鎖：在讀取數(shù)據(jù)時(shí)鎖住那幾行，其他對(duì)這幾行的更新需要等到悲觀鎖結(jié)束時(shí)才能繼續(xù)樂(lè)觀所：讀取數(shù)據(jù)時(shí)不鎖，更新時(shí)檢查是否數(shù)據(jù)已經(jīng)被更新過(guò)，如果是則取消當(dāng)前更新一般在悲觀鎖的等待時(shí)間過(guò)長(zhǎng)而不能接受時(shí)我們才會(huì)選擇樂(lè)觀鎖悲觀鎖的例子：12312412512612712812913013113213313413513613713S139閾141142Java代碼二二CREATEPROCE

18、DUREtferfunds(from_accountINT,to_accountINT,tfer_amountNUMERIC(10,2),OUTstatusINT,OUTmessageVARCHAR(30)BEGINDECLAREfrom_account_balanceNUMERIC(10,2);STARTTRANSACTION;SELECTbalanceINTOfrom_account_balanceFROMaccount_balanceWHEREaccount_id=from_accountFORUPDATE;IFfrom_account_balance>=tfer_amountTH

19、ENUPDATEaccount_balanceSETbalance=balance-tfer_amountWHEREaccount_id=from_account;143UPDATEaccount_balance,1ssSETbalance=balance+tfer_amount145WHEREaccount_id=to_account;COMMIT;147N：SETstatus=0;.1SSETmessage='OK'：ELSEJ'JROLLBACK;JSETstatus=-1;SETmessage='Insufficientfunds'i；4ENDI

20、F;：；心END;樂(lè)觀鎖的例子：Java代碼1、CREATEPROCEDUREtfer_funds10,2),157(from_accountINT,to_accountINT,tfer_amountNUMERIC(NOUTstatusINT,OUTmessageVARCHAR(30)159I：，BEGIN161NUMERIC(8,2);DECLAREfrom_account_balance2DECLAREfrom_account_timestamp1TIMESTAMP;DECLAREfrom_account_timestamp2TIMESTAMP;SELECTaccount_timestam

21、p,balanceINTOfrom_account_timestamp1,from_account_balanceFROMaccount_balanceWHEREaccount_id=from_account;IF(from_account_balance>=tfer_amount)THEN- -Hereweperformsomelongrunningvalidationthat- -mighttakeafewminutes*/CALLlong_running_validation(from_account);STARTTRANSACTION;- -Makesuretheaccountrowhasnotbeenupdatedsince- -ourinitialcheckSELECTaccount_timestamp,balance1631641651661671能1®170171172173174175176177ITS1為ISO1S11S21831S4INTOfrom_account_timestamp2,from_account_balance218518618718S1S9KO191192update1931第1951S619719S19