Mysql事务处理.docx

Mysql事务处理博客分类： 数据库MySQLOracleSQL设计模式一、事务【定义】事务：是指作为单个逻辑工作单元执行的一系列操作 当前mysql的存储引擎中只有innodb和BDB实现了事务的ACID，并且实现机制和oracle是一致的，主要使用了mvcc的实现理论。 mysql的事务分两种，一种是标准的事务，也叫normal transaction ,还有一个叫statement transaction。其中normal transaction是标准的实现ACID的事务，而statement transaction是就是一个语句是一个事务。平时我们可以设置mysql autocommit为true，其实这里是只有statement transaction，没有normal transaction，就是把每个statement transaction当作一个normal transaction。MYSQL的事务处理主要有两种方法： 1、用begin,rollback,commit来实现 begin 开始一个事务 rollback 事务回滚 commit 事务确认 2、直接用set来改变mysql的自动提交模式 MYSQL默认是自动提交的，也就是你提交一个QUERY，它就直接执行！我们可以通过 set autocommit=0 禁止自动提交 set autocommit=1 开启自动提交 来实现事务的处理。 当你用 set autocommit=0 的时候，你以后所有的SQL都将做为事务处理，直到你用commit确认或rollback结束。 注意当你结束这个事务的同时也开启了个新的事务！按第一种方法只将当前的作为一个事务！ 常用的是第一种方法！二、定义事务MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句，从而有效的将每条语句独立为一个事务。在复杂的应用场景下这种方式就不能满足需求了。为了打开事务，允许在COMMIT和ROLLBACK之前多条语句被执行，我们需要做以下两步：1, 设置MySQL的autocommit属性为0，默认为12，使用START TRANSACTION语句显式的打开一个事务如果已经打开一个事务，则SET autocommit=0不会起作用，因为START TRANSACTION会隐式的提交session中所有当前的更改，结束已有的事务，并打开一个新的事务。使用SET AUTOCOMMIT语句的存储过程例子：Java代码1. CREATEPROCEDUREtfer_funds 2. (from_accountint,to_accountint,tfer_amountnumeric(10,2) 3. BEGIN 4. SETautocommit=0; 5. 6. UPDATEaccount_balanceSETbalance=balance-tfer_amountWHEREaccount_id=from_account; 7. 8. UPDATEaccount_balanceSETbalance=balance+tfer_amountWHEREaccount_id=to_account; 9. 10. COMMIT; 11. END;Java代码1. CREATEPROCEDUREtfer_funds 2. (from_accountint,to_accountint,tfer_amountnumeric(10,2) 3. BEGIN 4. SETautocommit=0; 5. 6. UPDATEaccount_balanceSETbalance=balance-tfer_amountWHEREaccount_id=from_account; 7. 8. UPDATEaccount_balanceSETbalance=balance+tfer_amountWHEREaccount_id=to_account; 9. 10. COMMIT; 11. END;使用START TRANSACITON打开事务的例子：Java代码1. CREATEPROCEDUREtfer_funds 2. (from_accountint,to_accountint,tfer_amountnumeric(10,2) 3. BEGIN 4. STARTTRANSACTION; 5. 6. UPDATEaccount_balanceSETbalance=balance-tfer_amountWHEREaccount_id=from_account; 7. 8. UPDATEaccount_balanceSETbalance=balance+tfer_amountWHEREaccount_id=to_account; 9. 10. COMMIT; 11. END;Java代码1. CREATEPROCEDUREtfer_funds 2. (from_accountint,to_accountint,tfer_amountnumeric(10,2) 3. BEGIN 4. STARTTRANSACTION; 5. 6. UPDATEaccount_balanceSETbalance=balance-tfer_amountWHEREaccount_id=from_account; 7. 8. UPDATEaccount_balanceSETbalance=balance+tfer_amountWHEREaccount_id=to_account; 9. 10. COMMIT; 11. END;通常COMMIT或ROLLBACK语句执行时才完成一个事务，但是有些DDL语句等会隐式触发COMMIT，所以应该在事务中尽可能少用或注意一下：Java代码1. ALTERFUNCTION 2. ALTERPROCEDURE 3. ALTERTABLE 4. BEGIN 5. CREATEDATABASE 6. CREATEFUNCTION 7. CREATEINDEX 8. CREATEPROCEDURE 9. CREATETABLE 10. DROPDATABASE 11. DROPFUNCTION 12. DROPINDEX 13. DROPPROCEDURE 14. DROPTABLE 15. UNLOCKTABLES 16. LOADMASTERDATA 17. LOCKTABLES 18. RENAMETABLE 19. TRUNCATETABLE 20. SETAUTOCOMMIT=1 21. STARTTRANSACTIONJava代码1. ALTERFUNCTION 2. ALTERPROCEDURE 3. ALTERTABLE 4. BEGIN 5. CREATEDATABASE 6. CREATEFUNCTION 7. CREATEINDEX 8. CREATEPROCEDURE 9. CREATETABLE 10. DROPDATABASE 11. DROPFUNCTION 12. DROPINDEX 13. DROPPROCEDURE 14. DROPTABLE 15. UNLOCKTABLES 16. LOADMASTERDATA 17. LOCKTABLES 18. RENAMETABLE 19. TRUNCATETABLE 20. SETAUTOCOMMIT=1 21. STARTTRANSACTION3，使用Savepoint使用savepoint回滚难免有些性能消耗，一般可以用IF改写savepoint的良好使用的场景之一是“嵌套事务”，你可能希望程序执行一个小的事务，但是不希望回滚外面更大的事务：Java代码1. CREATEPROCEDUREnested_tfer_funds 2. (in_from_acctINTEGER, 3. in_to_acctINTEGER, 4. in_tfer_amountDECIMAL(8,2) 5. BEGIN 6. DECLAREtxn_errorINTEGERDEFAULT0; 7. 8. DECLARECONTINUEHANDLERFORSQLEXCEPTIONBEGIN 9. SETtxn_error=1; 10. END 11. 12. SAVEPINTsavepint_tfer; 13. 14. UPDATEaccount_balance 15. SETbalance=balance-in_tfer_amount 16. WHEREaccount_id=in_from_acct; 17. 18. IFtxn_errorTHEN 19. ROLLBACKTOsavepoint_tfer; 20. SELECTTransferaborted; 21. ELSE 22. UPDATEaccount_balance 23. SETbalance=balance+in_tfer_amount 24. WHEREaccount_id=in_to_acct; 25. 26. IFtxn_errorTHEN 27. ROLLBACKTOsavepoint_tfer; 28. SELECTTransferaborted; 29. 30. ENDIF: 31. ENDIF; 32. END;Java代码1. CREATEPROCEDUREnested_tfer_funds 2. (in_from_acctINTEGER, 3. in_to_acctINTEGER, 4. in_tfer_amountDECIMAL(8,2) 5. BEGIN 6. DECLAREtxn_errorINTEGERDEFAULT0; 7. 8. DECLARECONTINUEHANDLERFORSQLEXCEPTIONBEGIN 9. SETtxn_error=1; 10. END 11. 12. SAVEPINTsavepint_tfer; 13. 14. UPDATEaccount_balance 15. SETbalance=balance-in_tfer_amount 16. WHEREaccount_id=in_from_acct; 17. 18. IFtxn_errorTHEN 19. ROLLBACKTOsavepoint_tfer; 20. SELECTTransferaborted; 21. ELSE 22. UPDATEaccount_balance 23. SETbalance=balance+in_tfer_amount 24. WHEREaccount_id=in_to_acct; 25. 26. IFtxn_errorTHEN 27. ROLLBACKTOsavepoint_tfer; 28. SELECTTransferaborted; 29. 30. ENDIF: 31. ENDIF; 32. END;4，事务和锁事务的ACID属性只能通过限制数据库的同步更改来实现，从而通过对修改数据加锁来实现。直到事务触发COMMIT或ROLLBACK语句时锁才释放。缺点是后面的事务必须等前面的事务完成才能开始执行，吞吐量随着等待锁释放的时间增长而递减。MySQL/InnoDB通过行级锁来最小化锁竞争。这样修改同一table里其他行的数据没有限制，而且读数据可以始终没有等待。可以在SELECT语句里使用FOR UPDATE或LOCK IN SHARE MODE语句来加上行级锁Java代码1. SELECTselect_statementoptionsFORUPDATE|LOCKINSHAREMODEJava代码1. SELECTselect_statementoptionsFORUPDATE|LOCKINSHAREMODEFOR UPDATE会锁住该SELECT语句返回的行，其他SELECT和DML语句必须等待该SELECT语句所在的事务完成LOCK IN SHARE MODE同FOR UPDATE，但是允许其他session的SELECT语句执行并允许获取SHARE MODE锁死锁：死锁发生于两个事务相互等待彼此释放锁的情景当MySQL/InnoDB检查到死锁时，它会强制一个事务rollback并触发一条错误消息对InnoDB而言，所选择的rollback的事务是完成工作最少的事务（所修改的行最少）Java代码1. mysqlCALLtfer_funds(1,2,300); 2. ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransactionJava代码1. mysqlCALLtfer_funds(1,2,300); 2. ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransaction死锁在任何数据库系统里都可能发生，但是对MySQL/InnoDB这种行级锁数据库而言可能性相对较少。可以通过使用一致的顺序来锁row或table以及让事务保持尽可能短来减少死锁的频率。如果死锁不容易debug，你可以向你的程序中添加一些逻辑来处理死锁并重试事务，但这部分代码多了以后很难维护所以，比较好的避免死锁的方式是在做任何修改之前按一定的顺序添加行级锁，这样就能避免死锁:Java代码1. CREATEPROCEDUREtfer_funds3 2. (from_accountINT,to_accountINT,tfer_amountNUMERIC(10,2) 3. BEGIN 4. DECLARElocal_account_idINT; 5. DECLARElock_cursorCURSORFOR 6. SELECTaccount_id 7. FROMaccount_balance 8. WHEREaccount_idIN(from_account,to_account) 9. ORDERBYaccount_id 10. FORUPDATE; 11. 12. STARTTRANSACTION; 13. 14. OPENlock_cursor; 15. FETCHlock_cursorINTOlocal_account_id; 16. 17. UPDATEaccount_balance 18. SETbalance=balance-tfer_amount 19. WHEREaccount_id=from_account; 20. 21. UPDATEaccount_balance 22. SETbalance=balance+tfer_amount 23. WHEREaccount_id=to_account; 24. 25. CLOSElock_cursor; 26. 27. COMMIT; 28. END;Java代码1. CREATEPROCEDUREtfer_funds3 2. (from_accountINT,to_accountINT,tfer_amountNUMERIC(10,2) 3. BEGIN 4. DECLARElocal_account_idINT; 5. DECLARElock_cursorCURSORFOR 6. SELECTaccount_id 7. FROMaccount_balance 8. WHEREaccount_idIN(from_account,to_account) 9. ORDERBYaccount_id 10. FORUPDATE; 11. 12. STARTTRANSACTION; 13. 14. OPENlock_cursor; 15. FETCHlock_cursorINTOlocal_account_id; 16. 17. UPDATEaccount_balance 18. SETbalance=balance-tfer_amount 19. WHEREaccount_id=from_account; 20. 21. UPDATEaccount_balance 22. SETbalance=balance+tfer_amount 23. WHEREaccount_id=to_account; 24. 25. CLOSElock_cursor; 26. 27. COMMIT; 28. END;设置死锁ttl: innodb_lock_wait_timeout，默认为50秒如果你在一个事务中混合使用InnoDB和非InnoDB表，则MySQL不能检测到死锁，此时会抛出“lock wait timeuot”1205错误乐观所和悲观锁策略：悲观锁：在读取数据时锁住那几行，其他对这几行的更新需要等到悲观锁结束时才能继续乐观所：读取数据时不锁，更新时检查是否数据已经被更新过，如果是则取消当前更新一般在悲观锁的等待时间过长而不能接受时我们才会选择乐观锁悲观锁的例子：Java代码1. CREATEPROCEDUREtfer_funds 2. (from_accountINT,to_accountINT,tfer_amountNUMERIC(10,2), 3. OUTstatusINT,OUTmessageVARCHAR(30) 4. BEGIN 5. DECLAREfrom_account_balanceNUMERIC(10,2); 6. 7. STARTTRANSACTION; 8. 9. 10. SELECTbalance 11. INTOfrom_account_balance 12. FROMaccount_balance 13. WHEREaccount_id=from_account 14. FORUPDATE; 15. 16. IFfrom_account_balance=tfer_amountTHEN 17. 18. UPDATEaccount_balance 19. SETbalance=balance-tfer_amount 20. WHEREaccount_id=from_account; 21. 22. UPDATEaccount_balance 23. SETbalance=balance+tfer_amount 24. WHEREaccount_id=to_account; 25. COMMIT; 26. 27. SETstatus=0; 28. SETmessage=OK; 29. ELSE 30. ROLLBACK; 31. SETstatus=-1; 32. SETmessage=Insufficientfunds; 33. ENDIF; 34. END;Java代码1. CREATEPROCEDUREtfer_funds 2. (from_accountINT,to_accountINT,tfer_amountNUMERIC(10,2), 3. OUTstatusINT,OUTmessageVARCHAR(30) 4. BEGIN 5. DECLAREfrom_account_balanceNUMERIC(10,2); 6. 7. STARTTRANSACTION; 8. 9. 10. SELECTbalance 11. INTOfrom_account_balance 12. FROMaccount_balance 13. WHEREaccount_id=from_account 14. FORUPDATE; 15. 16. IFfrom_account_balance=tfer_amountTHEN 17. 18. UPDATEaccount_balance 19. SETbalance=balance-tfer_amount 20. WHEREaccount_id=from_account; 21. 22. UPDATEaccount_balance 23. SETbalance=balance+tfer_amount 24. WHEREaccount_id=to_account; 25. COMMIT; 26. 27. SETstatus=0; 28. SETmessage=OK; 29. ELSE 30. ROLLBACK; 31. SETstatus=-1; 32. SETmessage=Insufficientfunds; 33. ENDIF; 34. END;乐观锁的例子：Java代码1. CREATEPROCEDUREtfer_funds 2. (from_accountINT,to_accountINT,tfer_amountNUMERIC(10,2), 3. OUTstatusINT,OUTmessageVARCHAR(30) 4. 5. BEGIN 6. 7. DECLAREfrom_account_balanceNUMERIC(8,2); 8. DECLAREfrom_account_balance2NUMERIC(8,2); 9. DECLAREfrom_account_timestamp1TIMESTAMP; 10. DECLAREfrom_account_timestamp2TIMESTAMP; 11. 12. SELECTaccount_timestamp,balance 13. INTOfrom_account_timestamp1,from_account_balance 14. FROMaccount_balance 15. WHEREaccount_id=from_account; 16. 17. IF(from_account_balance=tfer_amount)THEN 18. 19. -Hereweperformsomelongrunningvalidationthat 20. -mighttakeafewminutes*/ 21. CALLlong_running_validation(from_account); 22. 23. STARTTRANSACTION; 24. 25. -Makesuretheaccountrowhasnotbeenupdatedsince 26. -ourinitialcheck 27. SELECTaccount_timestamp,balance 28. INTOfrom_account_timestamp2,from_account_balance2 29. FROMaccount_balance 30. WHEREaccount_id=from_account 31. FORUPDATE; 32. 33. IF(from_account_timestamp1from_account_timestamp2OR 34. from_account_balancefrom_account_balance2)THEN 35. ROLLBACK; 36. SETstatus=-1; 37. SETmessage=CONCAT(Transactioncancelledduetoconcurrentupdate, 38. ofaccount,from_account); 39. ELSE 40. UPDATEaccount_balance 41. SETbalance=balance-tfer_amount 42. WHEREaccount_id=from_account; 43. 44. UPDATEaccount_balance 45. SETbalance=balance+tfer_amount 46. WHEREaccount_id=to_account; 47. 48. COMMIT; 49. 50. SETstatus=0; 51. SETmessage=OK; 52. ENDIF; 53. 54. ELSE 55. ROLLBACK; 56. SETstatus=-1; 57. SETmessage=Insufficientfunds; 58. ENDIF; 59. END$Java代码1. CREATEPROCEDUREtfer_funds 2. (from_accountINT,to_accountINT,tfer_amountNUMERIC(10,2), 3. OUTstatusINT,OUTmessageVARCHAR(30) 4. 5. BEGIN 6. 7. DECLAREfrom_account_balanceNUMERIC(8,2); 8. DECLAREfrom_account_balance2NUMERIC(8,2); 9. DECLAREfrom_account_timestamp1TIMESTAMP; 10. DECLAREfrom_account_timestamp2TIMESTAMP; 11. 12. SELECTaccount_timestamp,balance 13. INTOfrom_account_timestamp1,from_account_balance 14. FROMaccount_balance 15. WHEREaccount_id=from_a