mysql 表被事务占用 mysql事务控制语句

在 MySQL命令行的默认设置下,事务都是自动提交(auto commit)的,即执行SQL语句后就会马上执行 COMMIT操作。因此要显式地开启一个事务需使用命令BEGIN、 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,禁用当前会话的自动提交。每个数据库厂商自动提交的设置都不相同,每个DBA或开发人员需要非常明白这一点,这对之后的SQL编程会有非凡的意义,因此用户不能以之前的经验来判断 MySQL数据库的运行方式。在具体介绍其含义之前,先来看看用户可以使用哪些事务控制语句。

• START TRANSACTION | BEGIN:显式地开启一个事务。
• COMMIT:要想使用这个语句的最简形式,只需发出 COMMIT。也可以更详细些,写为 COMMIT WORK,不过这二者几乎是等价的。 COMMIT会提交事务,并使得已对数据库做的所有修改成为永久性的。
• ROLLBACK:要想使用这个语句的最简形式,只需发出 ROLLBACK。同样地,也可以写为 ROLLBACK WORK,但是二者几乎是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改。
• SAVEPOINT identifier: SAVEPOINT允许在事务中创建一个保存点,一个事务中可以有多个 SAVEPOINT。
• RELEASE SAVEPOINT identifier:删除一个事务的保存点,当没有一个保存点执行这句语句时,会抛出一个异常。
• ROLLBACK TO [SAVEPOINT] identifier:这个语句与 SAVEPOINT命令一起使用。可以把事务回滚到标记点,而不回滚在此标记点之前的任何工作。例如可以发出两条 UPDATE语句,后面跟一个 SAVEPOINT,然后又是两条 DELETE语句。如果执行 DELETE语句期间出现了某种异常情况,并且捕获到这个异常,同时发出了 ROLLBACK TO SAVEPOINT命令,事务就会回滚到指定的 SAVEPOINT,撤销 DELETE完成的所有工作,而 UPDATE语句完成的工作不受影响。
• SET TRANSACTION:这个语句用来设置事务的隔离级别。 InnoDB存储引擎提供的事务隔离级别有: READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、 SERIALIZABLE。

START TRANSACTION、BEGN语句都可以在 MySQL命令行下显式地开启一事务。但是在存储过程中, MySQL数据库的分析器会自动将BEGN识别为 BEGIN…END,因此在存储过程中只能使用 START TRANSACTION语句来开启一个事务。
COMMIT和 COMMIT WORK语句基本是一致的,都是用来提交事务。不同之处在于 COMMIT WORK用来控制事务结束后的行为是CHAIN还是 RELEASE的。如果是CHAIN方式,那么事务就变成了链事务。
用户可以通过参数 completion_type来进行控制,该参数默认为0,表示没有任何操作。在这种设置下 COMMIT和 COMMIT WORK是完全等价的。当参数 completion_type的值为1时, COMMIT WORK等同于 COMMIT AND CHAIN,表示马上自动开启个相同隔离级别的事务,如

mysql> create table t(a int primary key);
Query OK, 0 rows affected (1.18 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.05 sec)

mysql> set @@completion_type=1;
Query OK, 0 rows affected (0.06 sec)

mysql> begin;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t select 1;
Query OK, 1 row affected (0.12 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> commit work;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t select 2;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t select 2;
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> rollback;
Query OK, 0 rows affected (0.09 sec)

mysql> select * from t;
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.05 sec)

在这个示例中我们设置 completion_type为1,第一次通过 COMMIT WORK来插入1这个记录。之后插入记录2时我们并没有用 BEGIN(或者 START TRANSACTION)来显式地开启一个事务,之后再插入一条重复的记录2就会抛出异常。接着执行ROLLBACK操作,最后发现只有1这一个记录,2并没有被插入。因为 completion_type为1时, COMMIT WORK会自动开启一个链事务,第二条 INSERT INTO t SELECT 2语句是在同一个事务内的,因此回滚后2这条记录并没有被插入表t中。
参数 completion_type为2时, COMMIT WORK等同于 COMMIT AND 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.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> commit work;
Query OK, 0 rows affected (0.06 sec)

mysql> select @@version;
ERROR 2013 (HY000): Lost connection to MySQL server during query

通过上面的示例可以发现,当将参数 completion_type设置为2时, COMMIT WORK后用户再执行语句 SELECT @@version会出现ERROR 2013 (HY000): Lost connection to MySQL server during query的错误。抛出该异常的原因是当前会话已经在上次执行 COMMIT WORK语句后与服务器断开了连接。
ROLLBACK和 ROLLBACK WORK与 COMMIT和 COMMIT WORK的工作一样,这里不再进行赘述。
SAVEPOINT记录了一个保存点,可以通过 ROLLBACK TO SAVEPOINT来回滚到某个保存点,但是如果回滚到一个不存在的保存点,会抛出异常：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> rollback to savepoint t1;
ERROR 1305 (42000): SAVEPOINT t1 does not exist

InnoDB存储引擎中的事务都是原子的,这说明下述两种情况:构成事务的每条语句都会提交(成为永久),或者所有语句都回滚。这种保护还延伸到单个的语句。一条语句要么完全成功,要么完全回滚(注意,这里说的是语句回滚)。因此一条语句失败并抛出异常时,并不会导致先前已经执行的语句自动回滚。所有的执行都会得到保留,必须由用户自己来决定是否对其进行提交或回滚的操作。如

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t select 4;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t select 4;
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
mysql> select * from t;
+---+
| a |
+---+
| 1 |
| 3 |
| 4 |
+---+
3 rows in set (0.00 sec)

可以看到,插入第二记录1时,因为重复的关系抛出了1062的错误,但是数据库并没有进行自动回滚,这时事务仍需要用户显式地运行 COMMIT或 ROLLBACK命令。

另一个容易犯的错误是 ROLLBACK TO SAVEPOINT,虽然有 ROLLBACK,但其并不是真正地结束一个事务,因此即使执行了ROLLBACK TO SAVEPOINT,之后也需要显式地运行 COMMIT或 ROLLBACK命令。

mysql> truncate t;
Query OK, 0 rows affected (0.34 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> savepoint t1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t select 2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> savepoint t2;
Query OK, 0 rows affected (0.00 sec)

mysql> release savepoint t1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t select 2;
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> rollback to savepoint t2;
ERROR 1305 (42000): SAVEPOINT t2 does not exist
mysql> select * from t;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from t;
Empty set (0.00 sec)

可以看到,在上面的例子中,虽然在发生重复错误后用户通过 ROLLBACK TO SAVEPOINT2命令回滚到了保存点t2,但是事务此时没有结束。再运行命令ROLLBACK后,事务才会完整地回滚。这里再一次提醒, ROLLBACK TO SAVEPOINT命令并不真正地结束事务。