MySQL事务控制语句

MySQL事务控制语句 
        在mysql命令行的默认下，事务都是自动提交的，sql语句提交后马上会执行commit操作。因此开启一个事务必须使用begin，start transaction，或者执行 set autocommit=0;
 可以使用的事务控制语句

start transction | begin : 显示的开启一个事务
 commit （commit work）
    commit work与completion_type的关系，commit work是用来控制事务结束后的行为，是chain还是release的，可以通过参数completion_type来控制，默认为0（或者NO_CHAIN），表示没有任何操作 与commit效果一样。当completion_type=1的时候

 

 

[sql]   view plain  copy  print ?

1. mysql> set names utf8;  
2. Query OK, 0 rows affected (0.00 sec)  
3.   
4. mysql> create table t(a int, primary key (a))engine=innodb;  
5. Query OK, 0 rows affected (0.29 sec)  
6.   
7. mysql> select @@autocommit;  
8. +--------------+  
9. | @@autocommit |  
10. +--------------+  
11. |            1 |  
12. +--------------+  
13. 1 row in set (0.00 sec)  
14.   
15. mysql> set @@completion_type=1;  
16. Query OK, 0 rows affected (0.00 sec)  
17.   
18. mysql> begin;  
19. Query OK, 0 rows affected (0.00 sec)  
20.   
21. mysql> insert into t select 1;  
22. Query OK, 1 row affected (0.00 sec)  
23. Records: 1  Duplicates: 0  Warnings: 0  
24.   
25. mysql> commit work;  
26. Query OK, 0 rows affected (0.00 sec)  
27.   
28. mysql> insert into t select 2;  
29. Query OK, 1 row affected (0.00 sec)  
30. Records: 1  Duplicates: 0  Warnings: 0  
31.   
32. mysql> insert into t select 2;  
33. ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'  
34. mysql> rollback;  
35. Query OK, 0 rows affected (0.00 sec)  
36.   
37. # 回滚之后只有1这个记录，而没有2这个记录  
38. mysql> select * from t;  
39. +---+  
40. | a |  
41. +---+  
42. | 1 |  
43. +---+  
44. 1 row in set (0.00 sec)  

 

测试中，将completion_type设置成1，第一次通过commit work来insert这条记录。之后insert 2的时候并没有启用begin（start transaction）来开启一个事务，之后再插入一条重复的记录2，这时会抛出异常rollback后，最后发现只有1这样一条记录，2并没有被insert进去。因为completion_type为1的时候，commit work会开启另外一个事务，因此2个insert语句是在同一个事务里面的，所以回滚后就没有insert进去。

参数completion_type为2时，commit work等同于commit and release。当事务提交时候会自动断开与db的连接，如下：

[sql]   view plain  copy  print ?

1. mysql> set @@completion_type=2;  
2. Query OK, 0 rows affected (0.00 sec)  
3.   
4. mysql> begin  
5.     -> ;  
6. Query OK, 0 rows affected (0.00 sec)  
7.   
8. mysql> insert into t select 3;  
9. Query OK, 1 row affected (0.00 sec)  
10. Records: 1  Duplicates: 0  Warnings: 0  
11.   
12. mysql> commit work;  
13. Query OK, 0 rows affected (0.00 sec)  
14.   
15. mysql> select @@versison;  
16. ERROR 2006 (HY000): MySQL server has gone away  
17. No connection. Trying to reconnect...  
18. Connection id:    205656  
19. Current database: test  
20.   
21. ERROR 1193 (HY000): Unknown system variable 'versison'  
22. mysql>   

 

通过上面的测试发现，completion_type设置成2时，commit work之后，再通过select获取db服务器版本信息的时候出现2006的error，说明以及断开了与db的连接。

rollback，rollback work与commit，commit work的工作原理一样。

 rollback（rollback work）
 savepoint identifier：在事务中创建一个保存点，一个事务允许有多个保存点
 release savepoint identifier：删除事务中的保存点，当时一个保存点也没有时执行这个命令，会报错抛出一个异常，如下所示：

[sql]   view plain  copy  print ?

1. mysql> begin;  
2. Query OK, 0 rows affected (0.00 sec)  
3.   
4. mysql> rollback to savepoint t1;  
5. ERROR 1305 (42000): SAVEPOINT t1 does not exist  
6. mysql>   

innodb存储引擎中的事务都是原子性的，说明以下2种情况：
构成事务的每条语句都会commit，否则事务的每条语句都会rollback，这种保护还会涉及到单调的语句。一条语句要不完成成功要么完全回滚，
但是一条语句失败并不会导致前一条执行的语句自动回滚，他们的工作会保留，需要你手动commit或者rollback。如下：

[sql]   view plain  copy  print ?

1. mysql>  create table t(a int, primary key (a))engine=innodb;  
2. Query OK, 0 rows affected (0.24 sec)  
3.   
4. mysql> begin  
5.     -> ;  
6. Query OK, 0 rows affected (0.00 sec)  
7.   
8. mysql> insert into t select 1;  
9. Query OK, 1 row affected (0.00 sec)  
10. Records: 1  Duplicates: 0  Warnings: 0  
11.   
12. mysql> insert into t select 1;  
13. ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'  
14. mysql> select * from t;  
15. +---+  
16. | a |  
17. +---+  
18. | 1 |  
19. +---+  
20. 1 row in set (0.00 sec)  
21.   
22. mysql>   
23. 可以看到，插入第二条记录的时候，db抛出了1062错误，但是并没有自动回滚，能查出前一条insert的记录，这个时候需要我们手动commit或者rollback  

 

 rollback to [savepoint] identifier：与savepoint一起使用，可以把事务回滚到标记点，而不回滚在此标记点之前的任何工作。
 
 set transaction：设置事务的隔离级别，4种事务隔离级别：read uncommitted，read committed，repeatable read，serializable。
 start transaction与begin都可以在mysql命令行下显示的开启一个事务，但是在存储过程中MySQL会自动将begin识别成begin ... end。
 因此在存储过程中，只能用start transaction。