mysql的事务控制

xuxiaohui9216

　　要显式地开启一个事务须使用命令BEGIN或START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，以禁用当前会话的自动提交。
　　事务控制语句：
　　命令
　　含义
　　开启事务
　　BEGIN ①
　　不能用于存储过程
　　显示地开启事务
　　BEGIN  WORK
　　START  TRANSACTION
　　提交事务
　　COMMIT
　　提交事务，并使已对数据库进行的所有修改称为永久性，COMMIT与COMMIT WORK几乎是等价的。
　　COMMIT  WORK②
　　回滚事务
　　ROLLBACK
　　回滚用户的事务，并撤销正在进行的所有未提交的事务。ROLLBACK 与ROLLBACK WORK几乎是等价的。
　　ROLLBACK  WORK
　　保存点
　　SAVEPOINT  identifier③
　　一个事务中可以有多个SAVEPOINT
　　删除事务保存点

　　RELEASE  SAVEPOINT>　　删除一个事务的保存点，当没有一个保存点执行这句语句时，会抛出一个错误。
　　回滚事务到标记点

　　ROLLBACK  TO [SAVEPOINT]>　　与SAVEPOINT命令一起使用，可以把事务回滚到标记点，而不会滚在此标记点之前的任何工作。
　　设置事务隔离级别
　　SET TRANSACTION
　　INNODB存储引擎提供的事务隔离级别READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLLE
　　①在存储过程中，mysql数据库的分析器会自动将BEGIN识别为BEGIN...END，因此在存储过程中只能使用START TRANSACTION语句来开启一个事务。
　　②COMMIT和COMMIT WORK语句基本上是一致的。不同之处在于COMMIT WORK用来控制事务结束后的行为是CHAIN还是RELEASE。
　　Value
　　Description
　　NO_CHAIN(or 0)
　　COMMIT and ROLLBACK are unaffected. This is the default value.
　　CHAIN (or 1)
　　COMMIT and ROLLBACK are equivalent to COMMIT AND CHAIN and ROLLBACK AND CHAIN, respectively. (A new transaction starts  immediately with the same isolation level as the just-terminated  transaction.)
　　RELEASE(or 2)
　　COMMIT and ROLLBACK are equivalent to COMMIT> and ROLLBACK>, respectively. (The server disconnects  after terminating the transaction.)
　　（1）set completion_type=0
　　可以通过参数completion_type来进行控制，default为0或NO_CHAIN，表示没有任何操作。
　　mysql>show variables like 'completion_type';
　　+-----------------+----------+
　　|Variable_name   | Value    |
　　+-----------------+----------+
　　|completion_type | NO_CHAIN |
　　+-----------------+----------+
　　1 row inset (0.00 sec)
　　（2）set completion_type=1
　　mysql> select @@autocommit;
　　+--------------+
　　| @@autocommit |
　　+--------------+
　　|            1 |
　　+--------------+
　　1 row in set (0.00 sec)
　　创建表test
　　droptable if exists t;
　　create tablet(id int primary key)engine=innodb;
　　setcompletion_type=1;
　　begin;
　　insertinto t select 1;
　　commitwork;
　　insertinto t select 2;
　　insertinto t select 2;
　　ERROR1062 (23000): Duplicate entry '2' for key 'PRIMARY'
　　mysql>rollback;
　　Query OK,0 rows affected (0.00 sec)
　　mysql>select * from t;
　　+----+

　　|>　　+----+
　　|  1 |
　　+----+
　　1 row inset (0.00 sec)
　　分析：completion_type=1时，第一次通过commit work来插入1这个记录，之后并没有显式地开始一个事务，当抛出异常时，执行rollback操作，只有一条记录插入，另一条没有插入。这是因为当completion_type=1时，commit work自动开启了一个链事务，直到rollback操作都属于一个事务，因此在回滚后，后面插入的记录都没有存在表中。
　　（2）set completion_type=2

　　当setcompletion_type=2时，COMMIT WORK等同于COMMIT>　　mysql>set completion_type=2;
　　Query OK,0 rows affected (0.00 sec)
　　mysql>begin;
　　Query OK,0 rows affected (0.00 sec)
　　mysql>insert into t select 3;
　　Query OK,1 row affected (0.00 sec)
　　Records:1  Duplicates: 0  Warnings: 0
　　mysql>commit work;
　　Query OK,0 rows affected (0.01 sec)
　　mysql>select schema();
　　ERROR2006 (HY000): MySQL server has gone away
　　Noconnection. Trying to reconnect...
　　Connectionid:    96
　　Currentdatabase: tpch
　　+----------+
　　|schema() |
　　+----------+
　　| tpch     |
　　+----------+
　　1 row inset (0.02 sec)
　　mysql>begin;
　　Query OK,0 rows affected (0.00 sec)
　　mysql>insert into t select 5;
　　Query OK,1 row affected (0.00 sec)
　　Records:1  Duplicates: 0  Warnings: 0

　　mysql> commit>　　Query OK,0 rows affected (0.00 sec)
　　③savepoint
　　a、对于不存在的SVAEPOINT，如果回滚，会抛出异常
　　mysql>rollback to t1;
　　ERROR1305 (42000): SAVEPOINT t1 does not exist
　　mysql>rollback to savepoint t1;
　　ERROR1305 (42000): SAVEPOINT t1 does not exist
　　b、在事务中，一条语句的失败并抛出异常并不会导致先前已经执行的语句自动回滚，必须由用户来决定是否对其进行提交或回滚操作。
　　commit操作：
　　mysql>set completion_type=0;
　　Query OK,0 rows affected (0.00 sec)
　　mysql>truncate table t;
　　Query OK,0 rows affected (0.04 sec)
　　mysql>begin;
　　Query OK,0 rows affected (0.00 sec)
　　mysql>insert into t select 1;
　　Query OK,1 row affected (0.00 sec)
　　Records:1  Duplicates: 0  Warnings: 0
　　mysql>insert into t select 1;
　　ERROR1062 (23000): Duplicate entry '1' for key 'PRIMARY'
　　mysql>select * from t;
　　+----+

　　|>　　+----+
　　|  1 |
　　+----+
　　1 row inset (0.00 sec)
　　mysql>commit;
　　Query OK,0 rows affected (0.00 sec)
　　mysql>select * from t;
　　+----+

　　|>　　+----+
　　|  1 |
　　+----+
　　1 row inset (0.00 sec)
　　mysql>
　　ROLLBACK操作：
　　mysql>truncate table t;
　　Query OK,0 rows affected (0.03 sec)
　　mysql>select * from t;
　　Empty set(0.00 sec)
　　mysql>begin;
　　Query OK,0 rows affected (0.01 sec)
　　mysql>insert into t select 5;
　　Query OK,1 row affected (0.00 sec)
　　Records:1  Duplicates: 0  Warnings: 0
　　mysql>insert into t select 5;
　　ERROR1062 (23000): Duplicate entry '5' for key 'PRIMARY'
　　mysql>select * from t;
　　+----+

　　|>　　+----+
　　|  5 |
　　+----+
　　1 row inset (0.00 sec)
　　mysql>rollback;
　　Query OK,0 rows affected (0.00 sec)
　　mysql>select * from t;
　　Empty set(0.00 sec)
　　c、执行返回到保存点操作之后，还需要显式地提交，才真正结束一个事务

　　也即在执行ROLLBACKTO SAVEPOINT>　　mysql>select * from t;
　　Empty set(0.00 sec)
　　mysql>begin;
　　Query OK,0 rows affected (0.00 sec)
　　mysql>insert into t select 10;
　　Query OK,1 row affected (0.00 sec)
　　Records:1  Duplicates: 0  Warnings: 0
　　mysql>savepoint sp1;
　　Query OK,0 rows affected (0.00 sec)
　　mysql>insert into t select 20;
　　Query OK,1 row affected (0.00 sec)
　　Records:1  Duplicates: 0  Warnings: 0
　　mysql>savepoint sp2;
　　Query OK,0 rows affected (0.00 sec)
　　mysql>select * from t;
　　+----+

　　|>　　+----+
　　| 10 |
　　| 20 |
　　+----+
　　2 rows inset (0.00 sec)
　　mysql>rollback to savepoint sp1;
　　Query OK,0 rows affected (0.00 sec)
　　mysql>select * from t;
　　+----+

　　|>　　+----+
　　| 10 |
　　+----+
　　1 row inset (0.00 sec)
　　mysql>rollback;
　　Query OK,0 rows affected (0.00 sec)
　　mysql>select * from t;
　　Empty set(0.00 sec)
　　mysql>
　　在回滚到某个保存点时，事务并没有结束，再运行ROLLBACK命令，事务才会完整地回滚。也就是说rollback to savepoint并不真正的结束事务。