首页 > 编程笔记 > MySQL笔记 阅读：15

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}
其中：
默认情况下，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)

如果在表的锁定期间，如果使用 START TRANSACTION 命令开启一个新的事务，会造成一个隐含的 unlock tables 被执行，该操作存在一定的隐患。

下面通过一个案例来理解。首先创建数据表 nbaplayer，命令如下： 
create table nbaplayer(
     id int,
     name varchar(20),
     salary int);

1) 在 Session1 中，查询 nbaplayer 表，结果为空，命令如下： 
mysql> select * from nbaplayer;
Empty set (0.00 sec)

2) 在 Session2 中，查询 nbaplayer 表，结果为空，命令如下： 
mysql> select * from nbaplayer;
Empty set (0.00 sec)

3) 在 Session1 中，对表 nbaplayer 加写锁，命令如下： 
mysql> lock table nbaplayer write;
Query OK, 0 rows affected (0.00 sec)

4) 在 Session2 中，向表 nbaplayer 中增加一条记录，命令如下：
mysql> insert into nbaplayer values(1,'kobe',10000);
等待

5) 在 Session1 中，插入一条记录，命令如下： 
mysql> insert into nbaplayer values(2,'durant',40000);
Query OK, 1 row affected (0.02 sec)

6) 在 Session1 中，回滚刚才插入的记录，命令如下： 
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

7) 在 Session1 中，开启一个新的事务，命令如下： 
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

8) 在 Session2 中，表锁被释放，此时成功增加该条记录，结果如下： 
mysql> insert into nbaplayer values(1,'kobe',10000);
Query OK, 1 row affected (2 min 32.99 sec)

9) 在 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 锁，可以保证停止任何对它要读的数据行的更新或者删除操作。下面通过一个例子来理解。

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

相关文章