MySQL事务的常见操作（附带实例）

MySQL 通过 SET AUTOCOMMIT﹑START TRANSACTION﹑COMMIT 和 ROLLBACK 等语句控制本地事务，具体语法如下：

START TRANSACTION | BEGIN [WORK];
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0|1}

其中 START TRANSACTION 表示开启事务、COMMIT 表示提交事务、ROLLBACK  表示回滚事务、SET AUTOCOMMIT 用于设置是否自动提交事务。

默认情况下，MySQL 事务是自动提交的，如果需要通过明确的 COMMIT 和 ROLLBACK 来提交和回滚事务，那么需要通过明确的事务控制命令来开始事务，这是和 Oracle 的事务管理明显不同的地方。如果应用从 Oracle 数据库迁移到 MySQL 数据库，则需要确保应用中对事务进行了明确的管理。

MySQL 的 AUTOCOMMIT（自动提交）默认是开启，对 MySQL 的性能有一定影响。举个例子，如果用户插入了 1000 条数据，MySQL 会提交事务 1000 次。这时可以把自动提交关闭，通过程序来提交事务一次就可以了。

关闭自动提交功能的命令如下：

mysql> set @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)

查看自动提交功能是否被关闭，命令如下：

mysql> show variables like "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.02 sec)

下面通过两个 Session（Session1 和 Session2）来理解事务控制的过程。

【实例】事务的控制过程。
1) 在 Session1 中，打开自动提交事务功能，然后创建表 ctable 并插入两条记录，命令如下：

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

mysql> create table ctable (data INT);

mysql> insert into ctable values(1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into ctable values(2);
Query OK, 1 row affected (0.00 sec)

2) 在 Session2 中，打开自动提交事务功能，然后查询表 ctable，命令如下：

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

mysql> select * from ctable;
+------+
| data |
+------+
|   1  |
|   2  |
+------+
2 rows in set (0.00 sec)

3) 在 Session1 中，关闭自动提交事务功能，然后向表 ctable 中插入两条记录，命令如下：

mysql> set @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ctable values(3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ctable values(4);
Query OK, 1 row affected (0.00 sec)

4) 在 Session2 中，查询表 ctable，命令如下：

mysql> select * from ctable;
+------+
| data |
+------+
|   1  |
|   2  |
+------+
2 rows in set (0.00 sec)

从结果中可以看出，在 Session1 中新插入的两条记录没有被查询出来。

5) 在 Session1 中，提交事务，命令如下：

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

6) 在 Session2 中，查询表 ctable，命令如下：

mysql> select * from ctable;
+------+
| data |
+------+
|   1  |
|   2  |
|   3  |
|   4  |
+------+
4 rows in set (0.00 sec)

此时，Session1 中新插入的两条记录被查询出来了。

在表的锁定期间，如果使用 start transaction 命令开启一个新的事务，会造成一个隐含的 unlock tables 被执行，该操作存在一定的隐患。下面通过一个案例来理解。

【实例 2】在表的锁定期间，使用 start transaction 命令开启一个新的事务。
1) 首先创建数据表 nbaplayer，命令如下：

create table nbaplayer(
          id int,
          name varchar(20),
          salary int);

2) 在 Session1 中，查询 nbaplayer 表，结果为空：

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

3) 在 Session2 中，查询 nbaplayer 表，结果为空：

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

4) 在 Session1 中，对表 nbaplayer 加写锁，命令如下：

mysql> lock table nbaplayer write;
Query OK, 0 rows affected (0.00 sec)

5) 在 Session2 中，向表 nbaplayer 中增加一条记录，命令如下：

mysql> insert into nbaplayer values(1,'kobe',10000);
等待

6) 在 Session1 中，插入一条记录，命令如下：

mysql> insert into nbaplayer values(2,'durant',40000);
Query OK, 1 row affected (0.02 sec)

7) 在 Session1 中，回滚刚才插入的记录，命令如下：

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

8) 在 Session1 中，开启一个新的事务，命令如下：

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

9) 在 Session2 中，由于表锁被释放，此时成功增加该条记录，结果如下：

mysql> insert into nbaplayer values(1,'kobe',10000);
Query OK, 1 row affected (2 min 32.99 sec)

10) 在 Session2 中，查询 nbaplayer，命令如下：

mysql> select * from nbaplayer;
+------+--------+--------+
| id   | name   | salary |
+------+--------+--------+
|   2  | durant |  40000 |
|   1  | kobe   |  10000 |
+------+--------+--------+
2 rows in set (0.00 sec)

从结果中可以看出，Session1 的回滚操作并没有被成功执行。

MySQL 提供的 LOCK IN SHARE MODE 锁，可以保证停止任何对它要读的数据行的更新或者删除操作。下面通过一个例子来理解。

【实例 3】 MySQL 的 LOCK IN SHARE MODE 锁。
1) 在 Session1 中，开启一个新的事务，然后查询数据表 nbaplayer 的 salary 列的最大值，命令如下：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select max(salary) from nbaplayer lock in share mode;
+-----------------+
| max(salary)     |
+-----------------+
|     40000       |
+-----------------+
1 row in set (0.00 sec)

2) 在 Session2 中，尝试做更新操作，命令如下：

mysql> update nbaplayer set salary = 90000 where id = 1;
等待

3) 在 Session1 中，提交事务，命令如下：

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

4) 在 Session2 中，等 Session1 的事务提交之后，更新操作才成功执行，结果如下：

mysql> update nbaplayer set salary = 90000 where id = 1;
Query OK, 1 row affected (16.25 sec)
Rows matched: 1  Changed: 1  Warnings: 0

由结果可以看出，在 Session1 中对 salary 列加了 LOCK IN SHARE MODE 锁，在 Session1 未提交前，不能在 Session2 中对 salary 列进行更新操作。