MySQL事务——commit or rollback

什么是事务

事务是MYSQL的一项功能，它可以使一组数据操作要么全部成功，要么全部失败，不会出现只执行一部分操作的情况

事务的语法结构如下：

start transaction 或者使用 begin 开始事务
一组数据操作语句
commit 提交事务
rollback 事务回滚

事务主要有4个特性（ACID）：

A（atomicity）：原子性，表示事务中的操作要么全部成功，要么全部失败，像一个整体，不能从中间打断
C（consistency）：一致性，表示数据的完整性不会因为事务的执行而受到破坏
I（isolation）：隔离性，表示多个事务同时执行的过程中，不会相互干扰。不同的隔离级别，相互独立的程度不同
D（durability）：持久性，当事务正确完成后，它对于数据的改变是永久性的

操作原子性

这里我举一个全家的例子，全家的收银员帮顾客结账的情况，这里主要涉及到的就是商品库存表和商品流水表：

stock（库存表）

itemnumer（商品编号）	stock（商品库存）
1	10

record（流水表）

id（流水单号）	itemnumber（商品编号）	quantity（销售数量）

现在，假设门店销售了5个编号为1的商品，这个操作其实意味着我们需要创建一条流水记录，并更新商品库存，一共有两个操作：

像record表中插入一条1号商品卖了5个的记录
把stock表中商品编号为1的记录stock减5

为了避免意外的发生，我们需要将这两个操作放到一个事务中去执行，利用事务的原子性，来确保数据的一致性

这里我省略了建表语句，应该难不倒在座的各位，但要注意itemnumbe和id是两张表各自的主键:satisfied:

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

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

mysql> update stock set stock = stock - 5 where itemnumber = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

然后我们可以查询一下结果：

mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |     5 |
+------------+-------+
1 row in set (0.00 sec)

mysql> select * from record;
+----+------------+----------+
| id | itemnumber | quantity |
+----+------------+----------+
|  1 |          1 |        5 |
+----+------------+----------+
1 row in set (0.00 sec)

操作的一致性

但是这里有一个细节点需要注意，事务并不会自动帮你处理SQL语句执行中的错误，如果你对事务中某一步的数据操作发生的错误不进行处理，仍然会导致数据不一致

这里如果在insert的过程中发生了错误，但是没有做回滚处理，继续执行后面的操作，最后提交事务，结果就会出现没有流水但是库存减少的情况：

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

mysql> insert into record values (1, 5);
# ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> update stock set stock = stock - 5 where itemnumber = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

然后我们可以查询一下结果：

mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |     0 |
+------------+-------+
1 row in set (0.00 sec)

mysql> select * from record;
+----+------------+----------+
| id | itemnumber | quantity |
+----+------------+----------+
|  1 |          1 |        5 |
+----+------------+----------+
1 row in set (0.00 sec)

可以看到并没有往流水表中添加记录，但是库存却被更新了

这就是因为没有正确使用事务导致的数据不完整问题。那么，如何使用事务，才能避免这种由于事务中的某一步或者几步操作出现错误，而导致数据不完整的情况发生呢？这就要用到事务中错误处理和回滚了：

如果发现事务中的某个操作发生错误，要及时回滚
只有事务中的操作都正常执行，才进行提交

那么我们只需要关注如果判断某个操作发生了错误，我们可以使用MYSQL的函数ROW_COUNT的返回结果，来判断操作是否成功，此时我们把stock表中的1号商品的库存重新调整为5

mysql> update stock set stock = 5 where itemnumber = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into record values (1, 5);
# ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> select row_count();
+-------------+
| row_count() |
+-------------+
|          -1 |
+-------------+
1 row in set (0.00 sec)

我们可以看到row_count函数返回的结果是-1，表示操作执行失败，1表示操作执行成功，这个时候我们就可以使用rollback进行回滚

事务的隔离性

这里还是使用全家的例子，只不过我们这次使用的是全家的会员卡，假设索隆的全家会员卡上有50块的余额（也就是5000积分），众所周知，全家的门店会员卡都是互通的，此时索隆拿着自己的实体会员卡来到了全家消费了50元，与此同时，索隆的爱人古伊娜用他的会员卡在全家APP上也消费了50元

索隆在门店消费的时候，开始了一个事务A，包括以下三个操作：

读取卡内余额50元
更新卡内余额为0
插入一条流水记录

古伊娜在APP中付款，开启了一个事务B，也来读取卡内余额，如果B读取卡内余额的操作发生了A更新卡内余额之后，并在插入流水记录之前，那么A有可能由于后面的操作失败而进行回滚。因此，本来可以成功进行的交易，因为读取到错误的信息而导致本可以成功的交易失败了

所以这里我们会用到MYSQL的锁机制。MYSQL可以把A中被修改过且还没有提交的数据锁住，让B处于等待状态，一直到A提交完成或者失败回滚，再释放锁，允许B读取数据，这样可以防止因为A回滚而B读取错误的可能了

MYSQL可以通过使用锁来控制事务对数据的操作，可以实现事务之间的相互隔离，锁的使用方式不同，隔离程度也不同

READ UNCOMMITED：未提交读，这种就是我们上面举的例子，可以读取事务中还未提交的被更改的数据
READ COMMITED：已提交读，只能读取事务中已经提交的被更改的数据
REPEATABLE READ：可重复读，表示在一个事务中，对一个数据读取的值，永远跟第一次读取的值一致，不受其他事务中数据操作的影响。这个也是MYSQL的默认隔离级别
SERIALIZARBLE：表示任何一个事务，一旦对某一个数据进行了任何操作，那么一直到这个事务结束，MYSQL都会把这个数据锁住。禁止其他事务对这个数据进行任何操作

下面是对各个隔离级别的总结：

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

然后我们来依次对各个隔离级别进行讲解

# 可以看到这里默认的隔离级别是可重复读
# 我安装的是MYSQL8.0的版本，低版本的请使用 select @@tx_isolation;
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

读未提交

先打开终端A，然后将MYSQL的默认隔离级别修改为读未提交
开启事务但不提交事务
然后打开终端B，设置隔离级别，开始事务并修改数据
在终端未提交的事务中进行查询

终端A如下：

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

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

mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |     5 |
+------------+-------+
1 row in set (0.00 sec)

##################### 第二步 #####################
# 查询编号为1的商品库存有多少
mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |    10 |
+------------+-------+
1 row in set (0.00 sec)
# 此时这里应该是 5，因为终端B中的事务还没有提交，产生了脏读

终端B如下：

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

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

mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |     5 |
+------------+-------+
1 row in set (0.00 sec)

#####################  第一步 #####################
# 设置 stock 的值为 10

mysql> update stock set stock = stock + 5 where itemnumber = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |    10 |
+------------+-------+
1 row in set (0.00 sec)

此时如果终端B中因为某种原因进行了回滚，那么终端A中读取到的就是错误的数据，这也就是所谓的脏读

读已提交（不可重复读）

打开一个终端A，并设置当前事务模式为read committed（已提交读），查询表stock的所有记录
在终端A的事务提交之前，打开另一个终端B，更新表stock
这时，终端B的事务还没提交，终端A不能查询到B已经更新的数据，解决了脏读问题
终端B的事务提交
终端A再次查询，结果与上一步不一致，产生了不可重复读的问题

终端A如下：

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

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

mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |     5 |
+------------+-------+
1 row in set (0.00 sec)

#####################  第二步 #####################
# 在终端A中查看编号为1的商品的库存数量
mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |     5 |
+------------+-------+
1 row in set (0.00 sec)
# 可见这里解决了上面的脏读问题

#####################  第四步 #####################
# 再次查询
mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |    10 |
+------------+-------+
1 row in set (0.00 sec)
# 再次查询后发现和第二步的结果不一致，产生了不可重复读的问题

终端B如下：

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

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

mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |     5 |
+------------+-------+
1 row in set (0.00 sec)

#####################  第一步 #####################
# 设置 stock 的值为 10
mysql> update stock set stock = stock + 5 where itemnumber = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |    10 |
+------------+-------+
1 row in set (0.00 sec)

#####################  第三步 #####################
# 提交事务
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

此时由于终端B中进行了事务的提交，终端A中两次读取到的就是不一致的数据，这也就是所谓的不可重复读

可重复读

打开一个终端A，并设置当前事务模式为repeatable read，查询表stock的所有记录
在终端A的事务提交之前，打开另一个终端B，更新表stock并提交
在终端A查询表stock的所有记录，与步骤1查询结果一致，没有出现不可重复读的问题
在终端A，接着执行更新操作，数据的一致性没有被破坏
重新打开终端B，插入一条新数据后提交
接着在终端A中也插入步骤5同样的一条数据，发现无法插入，产生了幻读

终端A：

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

# 其实不用设置，我们发现MYSQL默认的隔离级别就是可重复读，但也是写出来让大家明白一些

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

mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |     5 |
+------------+-------+
1 row in set (0.00 sec)

#####################  第二步 #####################
# 在终端A中进行查询，发现stock没有变成10，所以没有脏读的问题
mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |     5 |
+------------+-------+
1 row in set (0.00 sec)

#####################  第四步 #####################
# 再次在终端A中进行查询，发现stock仍然为5，没有不可重复读的问题
mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |     5 |
+------------+-------+
1 row in set (0.00 sec)

mysql> update stock set stock = stock + 3 where itemnumber = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |    13 |
+------------+-------+
1 row in set (0.00 sec)
# 这里发现上面查询出来的数据 stock 为 5，但是下面进行库存的更新时，最终的结果却是用了终端B提交事务后最新的stock值，产生了幻读

#####################  第六步 #####################
# 插入和第五步一样的数据，发现无法插入，但是在当前事务中，它认为确实没有itemnumber为2的数据，产生了幻读
mysql> insert into stock values (2, 10);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

终端B：

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

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

mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |     5 |
+------------+-------+
1 row in set (0.00 sec)

#####################  第一步 #####################
# 更新库存为10
mysql> update stock set stock = stock + 5 where itemnumber = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |    10 |
+------------+-------+
1 row in set (0.00 sec)

#####################  第三步 #####################
# 提交事务
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |    10 |
+------------+-------+
1 row in set (0.00 sec)

#####################  第五步 #####################
# 再次开启事务并往stock表中插入一条新的数据（2, 10）
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |    10 |
|          2 |    10 |
+------------+-------+
2 rows in set (0.00 sec)

串行化

打开一个终端A，并设置当前事务模式为serializable，查询表stock的所有记录
打开终端B，开始事务，并且往stock表中添加一条记录，但是不提交事务
在终端A中再次查询stock表，发现无法查询
在终端B中提交事务
再次在终端A中查询

终端A：

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

mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |     5 |
+------------+-------+
1 row in set (0.00 sec)

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

#####################  第二步 #####################
# 查询数据
mysql> select * from stock;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

#####################  第四步 #####################
# 再次查询数据
mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |     5 |
|          2 |    10 |
+------------+-------+
2 rows in set (0.00 sec)

终端B：

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

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

mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |     5 |
+------------+-------+
1 row in set (0.00 sec)

#####################  第一步 #####################
# 插入数据
mysql> insert into stock values (2, 10);
Query OK, 1 row affected (0.00 sec)

mysql> select * from stock;
+------------+-------+
| itemnumber | stock |
+------------+-------+
|          1 |     5 |
|          2 |    10 |
+------------+-------+
2 rows in set (0.00 sec)

#####################  第三步 #####################
# 提交事务
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

可见串行化的数据库并发能力是最弱的，所以除非是要数据强一致，否则应该使用MYSQL数据库默认的可重复读的隔离级别