MySQL 事务的操作

一、MySQL事务的概念

事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行；

事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元；

事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等，通过事务的整体性以保证数据的一致性；

事务是保证了一组操作的平稳性和可预测性的技术；

它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

二、事务的 ACID 特性

事务具有四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）；

这是一个可靠的数据库所应具备的几个特性。

2.1 原子性

指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生；
事务是一个完整的操作，事务的各元素是不可分的，即原子的；
事务中的所有元素必须作为一个整体提交或回滚；
如果事务中的任何元素失败，则整个事务将失败。

2.2 一致性

指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏；
当事务完成时，数据必须处于一致状态；
在事务开始前，数据库中存储的数据处于一致状态；
在正在进行的事务中，数据可能处于不一致的状态；
但当事务成功完成时，数据必须再次回到已知的一致状态。

2.3 隔离性

指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间；
对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务；
修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。

2.4 持久性

在事务完成以后，该事务对数据库所作的更改会持久的保存在数据库之中，并不会被回滚；
指不管系统是否发生故障，事务处理的结果都是永久的；
一旦事务被提交，事务的效果会被永久地保留在数据库中。

三、事物会出现的问题

3.1 脏读

一个事务读取了另一个事务未提交的数据

以上表为例，事物 A 读取主题访问量时读取到了事物B没有提交的数据 150。

如果事物 B 失败进行回滚，那么修改后的值还是会回到 100。然而事物 A 获取的数据是修改后的数据，这就有问题了。

3.2 不可重复读

事物读取同一个数据，返回结果先后不一致问题

上表格中，事物 A 在先后获取主题访问量时，返回的数据不一致。也就是说在事物 A 执行的过程中，访问量被其它事物修改，那么事物 A 查询到的结果就是不可靠的。

脏读与不可重复读的区别：脏读读取的是另一个事物没有提交的数据，而不可重复读读取的是另一个事物已经提交的数据。

3.3 幻读

事物按照范围查询，俩次返回结果不同

以上表为例，当对 100-200 访问量的主题做统计时，第一次找到了 100 个，第二次找到了 101 个。

3.4 丢失更新

脏读读取的是另一个事物没有提交的数据，而不可重复读读取的是另一个事物已经提交的数据。

幻读和不可重复读都是读取了另一条已经提交的事务（这点与脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

针对以上的三个问题，产生了四种事物隔离级别。

四、MySQL 事物隔离级别

4.1 未提交读 (Read Uncommitted)

俩个事物同时运行，有一个事物修改了数据，但未提交，另一个事物是可以读取到没有提交的数据。这种情况被称之为脏读。

4.2 提交读(Read committed)

一个事物在未提交之前，所做的任何操作其它事物不可见。这种隔离级别也被称之为不可重复读。因为会存在俩次同样的查询，返回的数据可能会得到不一样的结果。

4.3 可重复读(Repeatable Read)

这种隔离级别解决了脏读问题，但是还是存在幻读问题，这种隔离界别在 MySQL 的 innodb 引擎中是默认级别。MySQL 在解决幻读问题使用间隙锁来解决幻读问题。

4.4 可串行化 (Serializable)

这种级别是最高的，强制事物进行串行执行，解决了可重复读的幻读问题。

隔离级别与脏读、不可重复读、幻读的关系如见表所示：

对于隔离级别，级别越高并发就越低，而级别越低会引发脏读、不可重复读、幻读的问题。

因此在 MySQL 中使用可重复读（Repeatable Read）作为默认级别。

查看和设置事务隔离级别
查询全局事务隔离级别

mysql> show global variables like '%isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+

mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+

查询会话事务隔离级别

mysql> show session variables like '%isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+

mysql> SELECT @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

设置全局事务隔离级别

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+

mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED        |
+-----------------------+

设置会话事务隔离级别

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+

五、事务的操作

5.1 MySQL 操作事务

默认情况下，MySQL 的事务是自动提交的；
之前我们用 SQL 操作数据库时，一条语句执行后，系统会自动执行事务提交；
当需要把一组语句作为一个事务提交时，需要手动对事务进行控制；
手动控制事务有两种方法，一种是使用事务处理命令控制，另一种是使用 set 设置事务的处理方式。

5.2 使用事务命令控制事务

BEGIN 或 START TRANSACTION：显式地开启一个事务
COMMIT 或 COMMIT WORK：提交事务，并使已对数据库进行的所有修改变为永久性的。
ROLLBACK 或 ROLLBACK WORK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改
SAVEPOINT S1：使用 SAVEPOINT 允许在事务中创建一个回滚点，一个事务中可以有多个 SAVEPOINT；”S1“代表回滚点名称
ROLLBACK TO [SAVEPOINT] S1：把事务回滚到标记点

5.3 示例

准备测试数据

#创建数据库
mysql> create database Transaction;

#进入数据库
mysql> use Transaction;

#创建数据表
mysql> create table class (id int(10) primary key not null,name varchar(40),money double);
#字段一（10）：主键，且不能为空值
#字段二（40）：可变长度字段
#字段三：浮点类型

#在表中插入新数据记录
mysql> insert into class values(1,'A',1000);
mysql> insert into class values(2,'B',2000);

#查看class数据表
mysql> select * from class;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |  1000 |
|  2 | B    |  2000 |
+----+------+-------+

5.3.1 测试提交事务

使用 begin 开始事务，然后执行了两条插入语句，最后用 commit 提交事务；
此时两条数据插入到了数据表中，这两条语句是作为一个整体操作的。

#开启事务
mysql> begin;

#更新表字段，name字段=A的money值 - 100
mysql> update class set money = money - 100 where name = 'A';

#查看该class表
mysql> select * from class;

#这时候在另一个终端登录并查询数据，数据是没有修改的
mysql> select * from Transaction.class;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |  1000 |
|  2 | B    |  2000 |
+----+------+-------+

#提交事务
mysql> commit;

#重新登录或在另一个终端再次查询
#退出
mysql> quit
mysql> mysql -uroot -p123456
mysql> select * from Transaction.class;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |   900 |
|  2 | B    |  2000 |
+----+------+-------+

5.3.2 测试回滚事务

mysql> select * from class;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |   900 |
|  2 | B    |  2000 |
+----+------+-------+

#开启事务
mysql> begin;

#修改数据
mysql> update class set money = money + 100 where name = 'A';

#在事务中查看，数据已经修改了
mysql> select * from class;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |  1000 |
|  2 | B    |  2000 |
+----+------+-------+

#回滚前，在另一台终端查询该表数据，由于没有commit提交事务，所以依然还是原来的数据
mysql> select * from Transaction.class;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |   900 |
|  2 | B    |  2000 |
+----+------+-------+

#回滚，结束事务，并撤销正在进行的所有未提交的修改
mysql> rollback;
Query OK, 0 rows affected (0.01 sec) #0 rows，没有数据被修改

mysql> quit
mysql> mysql -uroot -p123456

#再次查看表数据，由于事务撤销了修改，所以数据没有发生变化
mysql> select * from Transaction.class;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |   900 |
|  2 | B    |  2000 |
+----+------+-------+

5.3.3 测试多点回滚

mysql> select * from class;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |   900 |
|  2 | B    |  2000 |
+----+------+-------+

#再次开启一个事务
mysql> begin;

#更新表中A的money值+100
mysql> update class set money = money + 100 where name = 'A';

#在事务中查询表数据
mysql> select * from class;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |  1000 |
|  2 | B    |  2000 |
+----+------+-------+

#创建回滚点S1
mysql> SAVEPOINT S1;

#更新表B中的money值+100
mysql> update class set money= money + 100 where name = 'B';

#在事务中再次查询表数据
mysql> select * from class;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |  1000 |
|  2 | B    |  2100 |
+----+------+-------

#创建回滚段S2
mysql> SAVEPOINT S2;

#再次插入一条数据记录
mysql> insert into class values(3,'C',1000);

#在事务中再一次查询表数据
mysql> select * from class;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |  1000 |
|  2 | B    |  2100 |
|  3 | C    |  1000 |
+----+------+-------+

#回滚到回滚点S2
mysql> ROLLBACK TO S2;

#在事务中查询表数据，已经回滚到了创建回滚点S2时的数据
mysql> select * from class;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |  1000 |
|  2 | B    |  2100 |
+----+------+-------+

#在回滚到回滚点S1
mysql> ROLLBACK TO S1;

#在事务中查询表数据，已经回滚到了创建回滚点S1时的数据
mysql> select * from class;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | A    |  1000 |
|  2 | B    |  2000 |
+----+------+-------+
mysql> commit;

5.4 设置事务自动提交

可以使用 SHOW VARIABLES LIKE 'AUTOCOMMIT'; 查看事务设置，是手动还是自动提交；

mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

值为 0 和值为 OFF：关闭事务自动提交。如果关闭自动提交，用户将会一直处于某个事务中，只有commit提交或rollback回滚后当前事务才算结束。当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。
值为 1 和值为 ON：开启事务自动提交。如果开启自动提交，则每执行一条 SQL 语句当成一个事务，然后自动的commit。当然无论开启与否，begin; commit|rollback;都是独立的事务。

可以使用 SET autocommit 语句设置事务的自动提交模式

1	mysql> SET autocommit = 0\|1\|ON\|OFF;