MySQL - 事务

事务

事务基本概念

-- 从id=1的账户给id=2的账户转账100元
-- 第一步：将id=1的A账户余额减去100
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 第二步：将id=2的B账户余额加上100
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

以上操作必须同时执行成功，如果有一个失败，则必须全部失败。

将两个操作看成一个整体操作时，可以使用事务。事务可以保证操作全部成功或全部失败。

数据库事务的ACID特性

• A - Atomic 原子性，要么全部执行成功，要么全部执行失败
• C - Consistent 一致性，A 账户减少了100，B账户则必定加上了100
• I - Isolation 隔离性，多个事务并发执行，互相隔离，互不影响
• D - Duration 持久性，事务完成后，发生的修改被数据库持久化存储

使用事务

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

对于单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为隐式事务。

COMMIT是指提交事务，即试图把事务内的所有SQL所做的修改永久保存。如果COMMIT语句执行失败了，整个事务也会失败。

有些时候，我们希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败：

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

隔离级别

SQL 中的隔离级别

隔离级别(Isolation Level)	脏读(Dirty Read)	不可重复读(Non Repeatable Read)	幻读(Phantom Read)
Read Uncommitted	Yes	Yes	Yes
Read Committed	-	Yes	Yes
Repeatable Read	-	-	Yes
Serializable	-	-	-

下面使用例子演示，建立一个数据库表，插入一条数据，结果如下：

mysql> select * from students;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
+----+-------+
1 row in set (0.00 sec)

查询和修改事务的隔离级别

• 查询 InnoDB 引擎全局的隔离级别和当前会话的隔离级别：
  select @@global.tx_isolation,@@tx_isolation;
  

• 设置innodb的事务级别：

     set 作用域 transaction isolation level 事务隔离级别
  
     SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
  

     mysql> set global transaction isolation level READ COMMITTED; // 设定全局的隔离级别为读提交
  
     mysql> set session transaction isolation level READ COMMITTED; // 设定当前会话的隔离级别为读提交
  

  

Read Uncommitted

读未提交，会产生：脏读，不可重复读，幻读

分别开启两个MySQL客户端连接，并按时间顺序执行下面的事务A和事务B：

时刻	事务A	事务B	说明
1	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;	设置事务的级别为 Read Uncommitted
2	BEGIN;	BEGIN;	开启事务
3	UPDATE students SET name = 'Bob' WHERE id = 1;		先在事务A中将id为1的student的name改为Bob
4		SELECT * FROM students WHERE id = 1;	事务B查出 id为1的student的name为Bob
5	ROLLBACK;		事务A回滚，Bob回滚为Alice，结束
6		SELECT * FROM students WHERE id = 1;	事务B查出 id为1的student的name为Alice
7		COMMIT;	提交事务，结束

上面演示的是出现脏读的情况：

一个事务会读取到另一个事务更新后但未提交的结果，如果另一个事务回滚了，则这个事务当前读取到的数据是脏数据。

---

Read Committed

读已提交，不会出现脏读，在上面的情况中，B事务始终不会读取到A事务做的修改

但是会产生不可重复读和幻读

分别开启两个MySQL客户端连接，并按时间顺序执行下面的事务A和事务B：

时刻	事务A	事务B	说明
1	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;	设置事务的级别为 Read Committed
2	BEGIN;	BEGIN;	开启事务
3		SELECT * FROM students WHERE id = 1;	读取到的name为Alice
4	UPDATE students SET name = 'Bob' WHERE id = 1;		修改Alice为Bob
5	COMMIT;		事务A提交事务,数据被修改了，结束
6		SELECT * FROM students WHERE id = 1;	再次查询，name变为Bob了
7		COMMIT;	事务B提交事务，结束

上面演示的是出现不可重复读的情况：

在一个事务内，这个事务还没结束，如果有另一个事务恰好修改了这个数据并提交了，该事务中，两次读取的数据可能不一样。

---

Repeatable Read

可重复读，不会出现脏读，不可重复读，在上面的情况中事务B读取的数据会一直为Alice

但是会出现幻读的情况

分别开启两个MySQL客户端连接，并按时间顺序执行下面的事务A和事务B：

时刻	事务A	事务B	说明
1	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;	设置事务的级别为 Repeatable Read
2	BEGIN;	BEGIN;	开启事务
3		SELECT * FROM students WHERE id = 99;	事务B查询id为99的数据，不存在
4	INSERT INTO students (id, name) VALUES (99, 'Bob');		事务A插入一条id为99的数据
5	COMMIT;		事务A提交，结束
6		SELECT * FROM students WHERE id = 99;	事务B查询id为99的数据,查不到数据
7		UPDATE students SET name = 'Alice' WHERE id = 99;	更新id为99的name为Alice，可以更新成功
8		SELECT * FROM students WHERE id = 99;	查询id为id为99的数据，可以查到数据，name为Alice
9		COMMIT;	提交事务，结束

上面演示的是出现幻读的情况：

在一个事务中，第一次查询某条记录，发现没有，但是试图去更新该条不存在的数据，居然可以更新成功，并且再一次读取同一条记录，竟然又出现了，就像出现了幻觉一样。

---

Serializable

Serializable是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。

虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。

默认隔离级别

如果没有指定隔离级别，数据库就会使用默认的隔离级别。

在MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read。

参考：
https://www.liaoxuefeng.com/wiki/1177760294764384/1179611198786848