事务特性ACID及隔离级别

注：例子引用来自：https://www.cnblogs.com/WJ-163/p/6023054.html

• 事务就是一组原子性的SQL查询，或者说一个独立的工作单元。
• 银行应用是解释事务必要性的一个经典例子。
• 假设一个银行的数据库有两张表：支票（checking）表和储蓄（savings）表。现在要从用户Jane的支票账户转移200美元到她的储蓄账户，那么需要至少三个步骤：
  • 1、检查支票账户的余额高于200美元。
  • 2、从支票账户余额中减去200美元。
  • 3、在储蓄账户余额中增加200美元。
• 上述三个步骤的操作必须打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤。
• 事务SQL的样本如下：

start transaction;
select balance from checking where customer_id = 123
update checking set balance = balance - 200.00 where customer_id = 123;
update savings set balance = balance + 200.00 where customer_id = 123;
commit;

• 如果执行到第四条时服务器崩溃了，会发生什么？用户可能会损失200美元。
• 在执行到第三条语句和第四条语句之间时，另外一个进程要删除支票账户的所有余额，那么结果可能就是银行在不知道这个逻辑的情况下白白给了Jane200美元。

ACID

原子性（atomicity）

• 一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。

一致性（consistency）

• 数据库总是从一个一致性的状态转换到另外一个一致性的状态。
• 在前面的例子中，一致性确保了，即使在执行第三、第四条语句之间时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。

隔离性（isolation）

• 通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的。
• 在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外一个账户汇总程序开始运行，则其看到的支票账户的余额并没有减去200美元。

持久性（durability）

• 一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

隔离级别

• 在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。
• 较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

READ UNCOMMITTED（未提交读）

• 在READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。
• 事务可以读取未提交的数据，这也被称为脏读（Dirty Read）。
• 从性能上来说，READ UNCOMMITTED不会比其他的级别好太多，但却缺乏其他级别的很多好处。

#首先，修改隔离级别
set tx_isolation='READ-UNCOMMITTED';
select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+

#事务A：启动一个事务
start transaction;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+

#事务B：也启动一个事务(那么两个事务交叉了)
       在事务B中执行更新语句，且不提交
start transaction;
update tx set num=10 where id=1;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |   10 |
|    2 |    2 |
|    3 |    3 |
+------+------+

#事务A：那么这时候事务A能看到这个更新了的数据吗?
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |   10 |   --->可以看到！说明我们读到了事务B还没有提交的数据
|    2 |    2 |
|    3 |    3 |
+------+------+

#事务B：事务B回滚,仍然未提交
rollback;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+

#事务A：在事务A里面看到的也是B没有提交的数据
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 |      --->脏读意味着我在这个事务中(A中)，事务B虽然没有提交，但它任何一条数据变化，我都可以看到！
|    2 |    2 |
|    3 |    3 |
+------+------+

READ COMMITTED（提交读）

• 大多数数据库系统默认隔离级别都是READ COMMITTED（但MySQL不是）。
• READ COMMITTED满足前面提到的隔离性的简单定义：
• 一个事务开始时，只能“看见”已经提交的事务所做的修改。
• 一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。
• 这个级别有时候也叫做不可重复读（nonrepeatable read），因为两次执行同样的查询，可能会得到不一样的结果。

#首先修改隔离级别
set tx_isolation='read-committed';
select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+


#事务A：启动一个事务
start transaction;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+


#事务B：也启动一个事务(那么两个事务交叉了)
       在这事务中更新数据，且未提交
start transaction;
update tx set num=10 where id=1;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |   10 |
|    2 |    2 |
|    3 |    3 |
+------+------+

#事务A：这个时候我们在事务A中能看到数据的变化吗?
select * from tx; --------------->
+------+------+                |
| id   | num  |                |
+------+------+                |
|    1 |    1 |--->并不能看到！  |
|    2 |    2 |                |
|    3 |    3 |                |
+------+------+                |——>相同的select语句，结果却不一样
                                  |
#事务B：如果提交了事务B呢?            |
commit;                           |
                                  |
#事务A:                            |
select * from tx; --------------->
+------+------+
| id   | num  |
+------+------+
|    1 |   10 |--->因为事务B已经提交了，所以在A中我们看到了数据变化
|    2 |    2 |
|    3 |    3 |
+------+------+

REPEATABLE READ（可重复读）

• REPEATABLE READ解决了脏读的问题。
• 该级别保证了在同一个事务中多次读取同样记录的结果是一致的。
• 但是理论上，可重复读隔离级别还是无法解决另外一个幻读（Phantom Read）的问题。
• 所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）。
• InnoDB和XtraDB存储引擎通过多版本并发控制解决了幻读的问题。

#首先，更改隔离级别
set tx_isolation='repeatable-read';
select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

#事务A：启动一个事务
start transaction;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+

#事务B：开启一个新事务(那么这两个事务交叉了)
       在事务B中更新数据，并提交
start transaction;
update tx set num=10 where id=1;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |   10 |
|    2 |    2 |
|    3 |    3 |
+------+------+
commit;

#事务A：这时候即使事务B已经提交了,但A能不能看到数据变化？
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |    1 | --->还是看不到的！(这个级别2不一样，也说明级别3解决了不可重复读问题)
|    2 |    2 |
|    3 |    3 |
+------+------+

#事务A：只有当事务A也提交了，它才能够看到数据变化
commit;
select * from tx;
+------+------+
| id   | num  |
+------+------+
|    1 |   10 |
|    2 |    2 |
|    3 |    3 |
+------+------+

SERIALIZABLE（可串行化）

• SERIALIZABLE是最高的隔离级别。
• 它通过强制事务串行执行，避免了前面说的幻读的问题。
• 简单来说，SERIALIZABLE会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。
• 只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。

#首先修改隔离界别
set tx_isolation='serializable';
select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+

#事务A：开启一个新事务
start transaction;

#事务B：在A没有commit之前，这个交叉事务是不能更改数据的
start transaction;
insert tx values('4','4');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
update tx set num=10 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction