mysql的事务控制

    要显式地开启一个事务须使用命令BEGIN或START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，以禁用当前会话的自动提交。

         事务控制语句：

         （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;

+----+

| id |

+----+

|  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 RELEASE。当事务提交后自动断开与服务器的连接。

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 release;

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;

+----+

| id |

+----+

|  1 |

+----+

1 row inset (0.00 sec)

mysql>commit;

Query OK,0 rows affected (0.00 sec)

mysql>select * from t;

+----+

| id |

+----+

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

+----+

| id |

+----+

|  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 identified之后，还需要运行COMMIT或者ROLLBACK命令才算真正结束该事务。

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;

+----+

| id |

+----+

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

+----+

| id |

+----+

| 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并不真正的结束事务。