mysql事务管理

MySQL事务管理模块====MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关的

====开启事务管理（autocommit==0/transaction）后设置回滚点（savepoint/在执行操作语句之前设置）最后自定义异常变量，进行程序与逻辑判断，如有异常则返回回滚点

READ UNCOMMITTED：最低级别的隔离，通常又称为dirty read，它允许一个事务读取还没commit的数据，这样可能会提高性能，但是dirty read可能不是我们想要的  

READ COMMITTED：在一个事务中只允许已经commit的记录可见，如果session中select还在查询中，另一session此时insert一条记录，则新添加的数据不可见  

REPEATABLE READ：在一个事务开始后，其他session对数据库的修改在本事务中不可见，直到本事务commit或rollback。在一个事务中重复select的结果一样，除非本事务中update数据库。  

SERIALIZABLE：最高级别的隔离，只允许事务串行执行。为了达到此目的，数据库会锁住每行已经读取的记录，其他session不能修改数据直到前一事务结束，事务commit或取消时才释放锁。

    可以使用如下语句设置MySQL的session隔离级别：

1. SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE                                                                      总结：MySQL默认的隔离级别是REPEATABLE READ，在设置隔离级别为READ UNCOMMITTED或SERIALIZABLE时要小心，READ UNCOMMITTED会导致数据完整性的严重问题，而SERIALIZABLE会导致性能问题并增加死锁的机率                                                                                                                                                                      

START TRANSACTION：开始事务，autocommit设为0，如果已经有一个事务在运行，则会触发一个隐藏的COMMIT  

COMMIT：提交事务，保存更改，释放锁  

ROLLBACK：回滚本事务对数据库的所有更改，然后结束事务，释放锁  

SAVEPOINT savepoint_name：创建一个savepoint识别符来ROLLBACK TO SAVEPOINT  

ROLLBACK TO SAVEPOINT savepoint_name：回滚到从savepoint_name开始对数据库的所有更改，这样就允许回滚事务中的一部分，保证更改的一个子集被提交  

SET TRANSACTION：允许设置事务的隔离级别  

LOCK TABLES：允许显式的锁住一个或多个table，会隐式的关闭当前打开的事务，建议在执行LOCK TABLES语句之前显式的commit或rollback。我们一般所以一般在事务代码里不会使用LOCK TABLES                                                                                                                                                                                                                                                            

二：定义事务

为了打开事务，允许在COMMIT和ROLLBACK之前多条语句被执行，我们需要做以下两步：
1, 设置MySQL的autocommit属性为0，默认为1
2，使用START TRANSACTION语句显式的打开一个事务 

使用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，所以应该在事务中尽可能少用或注意一下：

三：使用Savepoint

使用savepoint回滚难免有些性能消耗，一般可以用IF改写
savepoint的良好使用的场景之一是“嵌套事务”，你可能希望程序执行一个小的事务，但是不希望回滚外面更大的事务：

[java] view plain copy

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;  

四：事务和锁

事务的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]  

[java] view plain copy

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  

[java] view plain copy

1. <span class="hilite1">mysql</span> > 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;  

[java] view plain copy

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] view plain copy

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$$  

[java] view plain copy

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$$