MySQL存储过程之事务管理

1、MySQL存储过程之事务管理MySQL存储过程之事务管理ACID:Atomic、Consistent、Isolated、Durable存储程序提供了一个绝佳的机制来定义、封装和管理事务。1, MySQL的事务支持MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关：Java代码1 MyISAM:不支持事务，用于只读程序提高性能2 InnoDB:支持ACID事务、行级锁、并发3 BerkeleyDB:支持事务隔离级别：隔离级别决定了一个session中的事务可能对另一个session的影响、并发session对数据库的操作、一个session中所见数据的一致性ANSI标准定义了

2、4个隔离级别，MySQL的InnoDB都支持：Java代码4 READUNCOMMITTED:最低级别的隔离，通常又称为dirtyread,它允许一个事务读取还没commit的数据，这样可能会提高性能，但是dirtyread可能不是我们想要的5 READCOMMITTED:在一个事务中只允许已经commit的记录可见，如果session中select还在查询中，另一session此时insert一条记录，则新添加的数据不可见6 REPEATABLEREAD:在一个事务开始后，其他session对数据库的修改在本事务中不可见，直到本事务commit或rollback。在一个事务中重复select

3、的结果一样，除非本事务中update数据库。7 SERIALIZABLE:最高级别的隔离，只允许事务串行执行。为了达到此目的，数据库会锁住每行已经读取的记录，其他session不能修改数据直到前一事务结束，事务commit或取消时才释放锁。可以使用如下语句设置MySQL的session隔离级别：Java代码只SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED|READCOMMITTED|REPEATABLEREAD|SERIALIZABLEMySQL默认的隔离级别是REPEATABLEREAD,在设置隔离级别为READUNCOMMITTED或SERIALIZ

4、ABLE时要小心，READUNCOMMITTED会导致数据完整性的严重问题，而SERIALIZABLE会导致性能问题并增加死锁的机率事务管理语句：Java代码9 STARTTRANSACTION:开始事务，autocommit设为0,如果已经有一个事务在运行，则会触发一个隐藏的COMMIT10 COMMIT:提交事务，保存更改，释放锁11 ROLLBACK:回滚本事务对数据库的所有更改，然后结束事务，释放锁12 SAVEPOINTsavepoint_name:创建一个savepoint识别符来ROLLBACKTOSAVEPOINT13 ROLLBACKTOSAVEPOINTsavepoint_

5、name:回滚到从savepoint_name开始对数据库的所有更改，这样就允许回滚事务中的一部分，保证更改的一个子集被提交HSETTRANSACTION:允许设置事务的隔离级别15LOCKTABLES:允许显式的锁住一个或多个table,会隐式的关闭当前打开的事务，建议在执行LOCKTABLES语句之前显式的commit或rollback。我们一般所以一般在事务代码里不会使用LOCKTABLES2,定义事务MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句，从而有效的将每条语句独立为一个事务。在复杂的应用场景下这种方式就不能满足需求了。为了打开事务，允许在COMMIT和RO

6、LLBACK之前多条语句被执行，我们需要做以下两步：1,设置MySQL的autocommit属性为0,默认为12,使用STARTTRANSACTION语句显式的打开一个事务如果已经打开一个事务，则SETautocommit=0不会起作用，因为STARTTRANSACTION会隐式的提交session中所有当前的更改，结束已有的事务，并打开一个新的事务。使用SETAUTOCOMMIT语句的存储过程例子：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打开事务的例子: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语句执行时才完成一个事务，但是有些DDL语句等会隐式触发COMMIT,所以应该在事务中尽可能少用或注意一下：Ja

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

10、t使用savepoint回滚难免有些性能消耗，一般可以用IF改写savepoint的良好使用的场景之一是嵌套事务”，你可能希望程序执行一个小的事务，但是不希望回滚外面更大的事务：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,事务和锁事务的ACID属性只能通过限制数据库的同步更改来实现，从而通过对修改数据加锁来实现。直到事务触发COMMIT或ROLLBACK语句时锁才释放。缺点是后面的事务必须等前面的事务完成才能开始执行，吞吐量随着等待锁释放的时间增长而递减。MySQL/InnoDB通过行级锁来最小化锁竞争。这样修改同一table里其他行的数据没有限制，而且读数据可以始终没有等待。可以在SELECT语句里使用FORUPDATE或LOCKINSHAREMODE语句来加上行级锁

13、Java代码91SELECTselect_statementoptionsFORUPDATE|LOCKINSHAREMODEFORUPDATE会锁住该SELECT语句返回的行，其他SELECT和DML语句必须等待该SELECT语句所在的事务完成LOCKINSHAREMODE同FORUPDATE，但是允许其他session的SELECT语句执行并允许获取SHAREMODE锁死锁：死锁发生于两个事务相互等待彼此释放锁的情景当MySQL/InnoDB检查到死锁时，它会强制一个事务rollback并触发一条错误消息对InnoDB而言，所选择的rollback的事务是完成工作最少的事务(所修改的行最少)

14、Java代码mysql>CALLtfer_funds(1,2,300);ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransaction死锁在任何数据库系统里都可能发生，但是对MySQL/InnoDB这种行级锁数据库而言可能性相对较少。可以通过使用一致的顺序来锁row或table以及让事务保持尽可能短来减少死锁的频率。如果死锁不容易debug,你可以向你的程序中添加一些逻辑来处理死锁并重试事务，但这部分代码多了以后很难维护所以，比较好的避免死锁的方式是在做任何修改之前按一定的顺序添加行级锁，这样就能避免死

15、锁：Java代码国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;设置死锁ttl:inno

17、db_lock_wait_timeout,默认为50秒如果你在一个事务中混合使用InnoDB和非InnoDB表，则MySQL不能检测到死锁，此时会抛出"lockwaittimeuot”1205乐观所和悲观锁策略：悲观锁：在读取数据时锁住那几行，其他对这几行的更新需要等到悲观锁结束时才能继续乐观所：读取数据时不锁，更新时检查是否数据已经被更新过，如果是则取消当前更新一般在悲观锁的等待时间过长而不能接受时我们才会选择乐观锁悲观锁的例子：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;乐观锁的例子：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