Mysql事务处理.docx

1、Mysql 事务处 理博客分类： 数据库MySQLOracleSQL 设计模式一、事务【定义】事务：是指作为单个逻辑工作单元执行的一系列操作当前 mysql 的存储引擎中只有 innodb 和 BDB 实现了事务的 ACID，并且实现机制和oracle 是一致的，主要使用了 mvcc 的实现理论。mysql 的事 务分两种，一种是标准的事务，也叫 normal transaction ,还有一个叫statement transaction。其中 normal transaction 是标准的实现 ACID 的事务，而 statement transaction 是就是一个语句是一个事务。平 时

2、我们可以设 置 mysql autocommit 为 true，其 实这里是只有 statement transaction，没有 normal transaction，就是把每个 statement transaction 当作一个 normal transaction。MYSQL 的事务处 理主要有两 种方法： 1、用 begin,rollback,commit 来实现 begin 开始一个事务 rollback 事务回滚 commit 事务确认 2、直接用 set 来改变 mysql 的自动提交模式 MYSQL 默认是自动提交的，也就是你提交一个 QUERY，它就直接执行！我们可以通过

3、set autocommit=0 禁止自动提交 set autocommit=1 开启自动提交 来实现事务的处理。 当你用 set autocommit=0 的时候，你以后所有的 SQL 都将做为事务处理，直到你用commit 确认或 rollback 结束。 注意当你结束这个事务的同 时也开启了个新的事务！按第一种方法只将当前的作为一个事务！ 常用的是第一种方法！二、定义事务 MySQL 默认的行为是在每条 SQL 语句执行后执行一个 COMMIT 语句，从而有效的将每条语句独立为一个事务。 在复杂的应用场景下这种方式就不能满足需求了。 为了打开事务，允许在 COMMIT 和 ROLLBAC

4、K 之前多条语句被执行，我们需要做以下两步： 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

5、, 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; Java 代码 1. CREATE PROCEDURE tfe

6、r_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; 1

7、1. 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

8、 balance=balance+tfer_amount WHERE account_id=to_account; 9. 10. COMMIT; 11. END; 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_a

9、ccount; 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 FUN

10、CTION 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 Java 代码 1. ALTER FUNCTI

11、ON 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. TRU

12、NCATE 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. BEG

13、IN 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. ROLLBA

14、CK 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; Java 代码 1. CREA

15、TE 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_bal

16、ance 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

17、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 里其他行的数据没有限制，而且读数据可以始终没有等待。 可以在 S

18、ELECT 语句里使用 FOR UPDATE 或 LOCK IN SHARE MODE 语句来加上行级锁Java 代码1. SELECT select_statement options 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，但是

19、允许其他 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 tra

20、nsaction 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，你可以向你的程序中添加一些逻辑 来处理死锁并重试事务，但 这部分代码多了以后很难维护

21、所以，比较好的避免死锁的方式是在做任何修改之前按一定的顺序添加行级锁， 这样就能避免死锁: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

22、_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

23、balance=balance+tfer_amount 23. WHERE account_id=to_account; 24. 25. CLOSE lock_cursor; 26. 27. COMMIT; 28. END; 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. SELE

24、CT 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. WHER

25、E 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”120

26、5 错误 乐观所和悲观锁策略： 悲观锁：在读取数据时锁住那几行，其他 对这几行的更新需要等到悲 观锁结束时才能继续 乐观所：读取数据时不锁，更新 时检查是否数据已经被更新 过，如果是 则取消当前更新 一般在悲观锁的等待时间过长而不能接受时我们才会选择乐观锁 悲观锁的例子：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. DEC

27、LARE 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=balan

28、ce-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

29、. 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. 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 ac

30、count_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_

31、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,

32、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_tim

33、estamp1,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 TRAN

34、SACTION; 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

35、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_

36、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$ Java 代码 1. CREATE PROCEDURE tfer_funds 2.

37、(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 TIM

38、ESTAMP; 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 min

39、utes */ 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 ac

40、count_id=from_account 31. FOR UPDATE; 32. 33. IF (from_account_timestamp1 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=balanc

41、e-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，事务设计指南1. 保持事务短小 2. 尽量避免事务中 rollback 3. 尽量避免 savepoint 4. 默认情况下，依 赖于悲观锁 5. 为吞吐量要求苛刻的事务考虑乐观锁 6. 显示声明打开事务 7. 锁的行越少越好， 锁的时间越短越好