【SQL】事务的特性及使用

事务是一组操作的集合，他是一个不可分割的工作单位，事务会把所有的操作作为一个整体向系统提交或撤销操作请求。意味着这些操作要么同时都成功，要么同时都失败。

事务的四大特性（ACID）

• 原子性（Atomicity）：当前事务下操作要么同时成功，要么同时失败
• 一致性（Consisncy）：事务完成时必须所有数据都保持一致状态
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的环境下运行
• 持久性（Durbility）：事务一旦提交或回滚，那么他对数据库中数据的改变就是永久的

实际操作事务

1. 环境准备

我们会模拟银行转账的过程，为此我们需要构建模拟环境：

• 张三有2000元
• 李四有2000元
• 张三向李四转账1000元
  如下为创建基础数据的SQL语句

CREATE TABLE account (
	id INT PRIMARY KEY,
	name VARCHAR(10) NOT NULL,
	money INT NOT NULL
);

INSERT INTO account VALUES (1, '张三', 2000), (2, '李四', 2000);

2. 事务实现方法一

通常我们执行的每一条单独的SQL语句都可以看作是一个事务，只不过当语句正确时系统会帮助我们自动提交该事务。
因此如果我们期望将一组操作（多条SQL语句）作为同一个事务那么我们就需要将自动提交事务的机制改为手动提交。

SELECT @@autocommit; 该语句可以查看事务提交方式是否为自动提交，1为自动
SET@@autocommit = 0; 设置事务提交方式为非自动
COMMIT; 提交事务
ROLLBACK; 回滚事务

熟悉了上述四条语句我们便可以开始模拟转账了

首先我们需要确保已经将事务提交方式设置为非自动，即执行过 SELECT @@autocommit = 0; 且 SELECT @@autocommit 的结果为0
此时我们查看张三和李四的资金数如下

执行以下语句，分别模拟从张三账户转出1000，转入到李四账户中。

UPDATE account SET money=money-1000 WHERE name="张三"
UPDATE account SET money=money+1000 WHERE name="李四"

此时我们再次查看数据表格，发现张三李四的数据没有任何变化，这是因为我们将事务的提交方式改为了非自动。
此时如果我们确定更新语句执行没有问题，则可以执行 COMMIT; 进行提交。一但我们提交数据便会同时立即更新。
相反，此时如果我们发现更新语句执行报错或逻辑错误，则可以执行 ROLLBACK; 将事务回滚，一但回滚数据便不再会进行任何改动。

注意事项：
SET@@autocommit = 0; 的设置只在当前会话内生效

3. 事务实现方法二

我们使用 START TRANSACTION; 或 BEGIN; 便可以避免在会话中不断修改 @@autocommit

此时我们便可以开始模拟转账了

首先我们需要先执行 START TRANSACTION; 或 BEGIN; 以开启事务
然后执行以下语句，分别模拟从张三账户转出1000，转入到李四账户中。

UPDATE account SET money=money-1000 WHERE name="张三"
UPDATE account SET money=money+1000 WHERE name="李四"

此时我们再次查看数据表格，发现张三李四的数据没有任何变化，这是因为我们开启了事务。
此时如果我们确定更新语句执行没有问题，则可以执行 COMMIT; 进行提交。一但我们提交数据便会同时立即更新。
相反，此时如果我们发现更新语句执行报错或逻辑错误，则可以执行 ROLLBACK; 将事务回滚，一但回滚数据便不再会进行任何改动。

注意事项：
当我们提交或者回滚时意味着当前开启的事务已经结束，意味着在这之后再执行的语句又会自动提交。

事务并发可能引起的问题

在本章节的演示中，我们会开启两个终端，模式并行的两个事务（事务A、事务B）
模拟数据依然是之前用到的张三、李四
由于数据库系统有默认的隔离机制来避免事务并发造成的问题，因此在本节演示过程需要不断调整隔离级别以暴露事务并发问题。详细的隔离级别及设置方法将在下节中详细说到。

1. 脏读

一个事务中读到另一个事务还未提交的数据。

• 查看原始数据

  mysql> SELECT * FROM account;
  +----+--------+-------+
  | id | name   | money |
  +----+--------+-------+
  |  1 | 张三   |  2000 |
  |  2 | 李四   |  2000 |
  +----+--------+-------+
  2 rows in set (0.00 sec)
  

• 使用指令 START TRANSACTION; 或 BEGIN; 分别开启事务A、事务B
• 在事务A中更新一行数据，此时我们还未提交该事务，因此当我们查询时数据应该没有改变

  mysql> UPDATE account SET money=money-1000 WHERE name='张三';
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  

• 在事务B中查看数据，但发现数据已经改变，这便是脏读的问题

  mysql> SELECT * FROM account;
  +----+--------+-------+
  | id | name   | money |
  +----+--------+-------+
  |  1 | 张三   |  1000 |
  |  2 | 李四   |  2000 |
  +----+--------+-------+
  2 rows in set (0.00 sec)
  

2. 不可重复读

一个事务先后读取同一条记录，但两次读取的数据不同

• 使用指令 START TRANSACTION; 或 BEGIN; 分别开启事务A、事务B
• 在事务A中第一次查看数据

  mysql> SELECT * FROM account;
  +----+--------+-------+
  | id | name   | money |
  +----+--------+-------+
  |  1 | 张三   |  1000 |
  |  2 | 李四   |  2000 |
  +----+--------+-------+
  2 rows in set (0.00 sec)
  

• 在事务B中修改数据，并提交事务。

  mysql> UPDATE account SET money=money-1000 WHERE name='张三';
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  

• 在事务A中第二次查看数据，发现第二次查询与第一次不同，这便是不可重复读的问题

  mysql> SElECT * FROM account;
  +----+--------+-------+
  | id | name   | money |
  +----+--------+-------+
  |  1 | 张三   |     0 |
  |  2 | 李四   |  2000 |
  +----+--------+-------+
  2 rows in set (0.00 sec)
  

3. 幻读

一个事务按照条件查询数据时，没有对应的数据行，但在插入数据时又发现这行数据已经存在导致报错

• 使用指令 START TRANSACTION; 或 BEGIN; 分别开启事务A、事务B
• 在事务A中查找一条不存在的数据

  mysql> SELECT * FROM account WHERE id=3;
  Empty set (0.00 sec)
  

• 在事务B中插入该不存在的数据，并提交

  mysql> INSERT INTO account VALUES (3, '王五', 2000);
  Query OK, 1 row affected (0.00 sec)
  
  mysql> COMMIT;
  Query OK, 0 rows affected (0.01 sec)
  

• 回到事务A使用同样的插入语句发现报错，再次查询也无法查到任何数据，这便是幻读

  mysql> INSERT INTO account VALUES (3, '王五', 2000);
  ERROR 1062 (23000): Duplicate entry '3' for key 'account.PRIMARY'
  mysql> SELECT * FROM account WHERE id=3;
  Empty set (0.01 sec)
  

事务的隔离级别

事务的隔离级别是用来解决上述并发事务问题的，那么我们需要简单了解一下不同隔离级别与并发事务问题之间的关系

隔离级别	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read(MySQL默认)	×	×	√
Serializable	×	×	×

注意事项
四种隔离级别由上到下级别越来越高但性能越来越低，因此当我们为数据库设置隔离级别时要权衡安全和效率

SELECT @@TRANSACTION_ISOLATION 查看当前隔离级别
SET {SESSION I GLOBAL} TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } 以当前会话或全局的方式修改隔离级别