程序员需了解的MYSQL事务控制（八）

---

前言

数据库事务是一个很重要的概念，日常开发中如何保证数据的准确性是一个很大的学问。MYSQL支持的事务的实现依赖于其ACID特性。ACID是什么？即原子性、一致性、隔离性、持久性 。

事务名称	解释
原子性（Atomicity）	事务是一个原子操作，原子操作简单理解指的是这个操作要么全部成功，要么全部失败
一致性(Consistency)	事务无论成功与否，数据库必须保证所处的数据不应被破坏,举个例子:A给B无论成功或失败转账，那么A的钱+B的钱前后应该总和相等
隔离性（Isolation）	同一份的数据可能有很多事务进行操作，因此要将各种事务隔离开，防止数据被损坏
持久性（Durability）	事务如果一旦完成，结果都应不变，因为这样无论系统发送了什么错误，都能进行数据恢复

---

一、事务控制

MYSQL通过SET AUTOCOMMIT、START TRANSACTION、COMMIT、ROLLBACK 等语句支持事务，MYSQL的事务默认是自动提交的。可以通过语句查询当前MYSQL的事务信息：

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

mysql> 

也可以手动开启事务、提交事务以及回滚事务，下面介绍事务控制语句详情：

• START TRANSACTION 开启新事务
• COMMIT 和ROLLBACK用于提交和回滚事务
• SET AUTOCOMMIT 用于设置是否开启事务
• CHAIN 出现当前事务提交后，会立即重启新的事务并保持相同的隔离级别。
• RELEASE 表示提交事务后会立即断开与客户端的连接。

下面以实操感受一下数据库事务特性，首先创建一个数据库表如下：

 　mysql> create table tb_user 
 　(id int primary key auto_increment,
 　name varchar(125) not null,
 　age int not null);
Query OK, 0 rows affected (0.04 sec)

START TRANSACTION 例子

回话1	回话2
查询tb_user表id为1的记录 mysql> select * from tb_user where id =1; Empty set (0.00 sec)	查询tb_user表id为1的记录 mysql> select * from tb_user where id =1; Empty set (0.00 sec)
用START TRANSACTION开启一个事务并插入一条记录，没有进行事务commit mysql> insert into tb_user(name,age) values(‘Michael’,23) ; Query OK, 1 row affected (0.00 sec)	再次查询表id为1的记录依然为空 mysql> select * from tb_user where id =1; Empty set (0.00 sec)
事务提交commit	再次查询表后可以查到结果: mysql> select * from tb_user where id=1;
直接插入一条数据，这个事务是自动提交的 mysql> insert into tb_user(name,age) values(‘John’,24) ; Query OK, 1 row affected (0.00 sec)	可以直接查询刚刚回话一添加的记录 mysql> select * from tb_user where id=2;

COMMIT AND CHAIN

当前事务提交并开启一个新的事务，保持与当前事务同一个事务隔离水平。

会话1	会话2
重新开启一个事务并在向表中插入一条数据 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into tb_user(name,age) values(‘T-bag’,25); Query OK, 1 row affected (0.00 sec) 然后用commit and chain命令进行提交 mysql> commit and chain;Query OK, 0 rows affected (0.00 sec)	然后在会话2中无法查询到会话1中插入的数据
在此用commit命令提交事务	然后在此查询可以看到会话一中新插入的数据 mysql> select * from tb_user where id =3;

SAVE POINT 与ROLL BACK

SAVE POINT可以指定回滚事务的一部分，可以定义不同的SAVE POINT满足不同条件下回滚不同的事务，SAVE PONIT 一般不能重名，因为这会造成后定义的SAVE PONIT会覆盖之前同名的SAVE POINT，如果不在使用SAVE PONIT可以通过RELEASE SAVE PONT命令进行删除，一旦被删除就无法在进行事务回滚。

会话1	会话2
启动一个新的事务，并向表中插入新的数据 mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into tb_user(name,age) values(‘Sara’,22); Query OK, 1 row affected (0.00 sec)	会话2中无法查询到name为Sara的记录 mysql> select * from tb_user where name=‘Sara’; Empty set (0.00 sec)
会话1中是可以查到此条记录的
此处定义SAVE POINT并命名transaction1 mysql> savepoint transaction1; Query OK, 0 rows affected (0.00 sec)
此处在此插入数据 mysql> insert into tb_user(name,age) values(‘Bellick’,32); Query OK, 1 row affected (0.00 sec)	会话2中无法查询到name为Sara和name为Bellick的两条记录
会话1中可以查询name为Sara和name为Bellick记录 mysql> select * from tb_user where name=‘Sara’ or name=‘Bellick’;
回滚事务点transaction1 mysql> rollback to savepoint transaction1; Query OK, 0 rows affected (0.00 sec)
在此查询name为Sara和name为Bellick的记录，发现只能查询到Sara的记录
commit提交事务	会话2中可以查询到name为Sara的记录

上面我们验证了savepoint的使用方法，接下来我们演示其出现的覆盖问题，以及删除savepoint的用法如下：

## 开启新事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
## 插入数据
mysql> insert into tb_user(name,age) values('Bellick',32);
Query OK, 1 row affected (0.00 sec)
## 生成savepoint
mysql> savepoint transaction1;
Query OK, 0 rows affected (0.00 sec)

## 插入新的数据
mysql> insert into tb_user(name,age) values('Veronica',21);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_user;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | Michael  |  23 |
|  2 | John     |  24 |
|  3 | T-bag    |  25 |
| 4 | Sara     |  22 |
| 5 | Bellick  |  32 |
| 6 | Veronica |  21 |
+----+----------+-----+
6 rows in set (0.00 sec)
## 回滚事务
mysql> rollback to savepoint transaction1;
Query OK, 0 rows affected (0.00 sec)
## 在此查询
mysql> select * from tb_user;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | Michael |  23 |
|  2 | John    |  24 |
|  3 | T-bag   |  25 |
| 4 | Sara    |  22 |
| 5 | Bellick |  32 |
+----+---------+-----+
5 rows in set (0.00 sec)

## 重新在插入数据
mysql> insert into tb_user(name,age) values('Veronica',21);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_user;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | Michael  |  23 |
|  2 | John     |  24 |
|  3 | T-bag    |  25 |
| 4 | Sara     |  22 |
| 5 | Bellick  |  32 |
| 6 | Veronica |  21 |
+----+----------+-----+
6 rows in set (0.00 sec)

mysql> savepoint transaction1;
Query OK, 0 rows affected (0.00 sec)
## 再次插入数据并回滚
mysql> select * from tb_user;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | Michael  |  23 |
|  2 | John     |  24 |
|  3 | T-bag    |  25 |
| 4 | Sara     |  22 |
| 5 | Bellick  |  32 |
| 6 | Veronica |  21 |
| 7 | Fernando |  24 |
+----+----------+-----+
7 rows in set (0.00 sec)

## 回滚事务
mysql> rollback to savepoint transaction1;
Query OK, 0 rows affected (0.00 sec)
## 查询发现两次回滚同一个名称为transaction1的savepoint发现查询结果并不一样
mysql> select * from tb_user;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
|  1 | Michael  |  23 |
|  2 | John     |  24 |
|  3 | T-bag    |  25 |
| 11 | Sara     |  22 |
| 12 | Bellick  |  32 |
| 14 | Veronica |  21 |
+----+----------+-----+
6 rows in set (0.00 sec)

## 删除savepoint
mysql> release savepoint transaction1;
Query OK, 0 rows affected (0.00 sec)

二、事务隔离级别

隔离级别	描述
READ_UNCOMMITED	允许事务读取其他事务未提交的数据，不可重复读、脏读、幻读将会出现
READ_COMMITED	只允许事务读取其他事务已提交的数据，可以避免脏读，但不可重复读以及幻读依然存在
REPEATABLE_READ	确保事务可以多次对同一字段读取是同一个值，该事务持续期间禁止其他事务进行更新，可以避免脏读、不可重复读，但仍然存在幻读的可能性
SERIALIZABLE	确保事务可以多次读取同一行的值，事务操作期间不允许其他事务进行更新、删除、添加操作，可以避免上述并发的问题，但问题是效率低下

• 事务的隔离级别

典型的事务隔离不同所造成问题如下：

1. 脏读：脏读发送在A事务读取B事务已经改写但是还未提交的数据,若此时B事务回滚了，那么A事务获取就是脏数据
2. 不可重复读：不可重复读发送在当A事务执行2次查询，每一次获取的数据结果都不相同，这是由于B事务在A事务2次查询期间进行了更新
3. 幻读： 幻读发送在当A事务读取了几行数据，紧接着B事务进行输入的插入，在随后的查询中A事务就会读了原本不存在的记录
   ⚠️ 不可重复读特指修改的记录，而幻读指的是新增或删除的记录

• 数据库默认隔离级别
  MYSQL默认隔离级别REPEATABLE_READ,ORACLE 默认隔离级别READ_COMMITED。