MySQL中事务的ACID及隔离级别的理解

系统环境：
RHEL6.5
MySQL5.5.58

我们知道ACID是衡量一个事物处理系统的标准，下面就ACID做简要说明，重点讨论I(isolation，隔离性)并举例说明

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

一致性(Consistency): 数据库总是从一个一致性的状态转换到另一个一致性的状态。

隔离性(Isolation)：一个事务所做的修改在最终提交以前，对其他事务是不可见的。

持久性(Durability):一旦事务提交，则其所做的修改不会永久保存到数据库。

在MySQL中隔离性有4种级别，分别是：
read-uncommitted 读未提交
read-committed 读提交
repeatable-read 可重读
serializable 可串行化

打开两个MySQL终端测试，为了区别，将其中一个提示符修改为--->
建一个cards表，测试4种隔离级别下，事务过程中有可能出现的情况

 1 cards建表语句如下：
 2 mysql> show create table cards G
 3 *************************** 1. row ***************************
 4        Table: cards
 5 Create Table: CREATE TABLE `cards` (
 6   `id` int(11) NOT NULL AUTO_INCREMENT,
 7   `name` varchar(32) NOT NULL DEFAULT '',
 8   `balance` int(10) unsigned DEFAULT NULL,
 9   PRIMARY KEY (`id`)
10 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
11 1 row in set (0.00 sec)
12 
13 
14 现在表中有一条数据如下所示：
15 mysql> select * from cards;
16 +----+--------------+---------+
17 | id | name         | balance |
18 +----+--------------+---------+
19 |  1 | zhangsanfeng |    5000 |
20 +----+--------------+---------+
21 1 row in set (0.00 sec)
22 
23 
24 查看当前MySQL系统隔离级别
25 mysql> show global variables like '%iso%';
26 +---------------+-----------------+
27 | Variable_name | Value           |
28 +---------------+-----------------+
29 | tx_isolation  | REPEATABLE-READ |
30 +---------------+-----------------+
31 1 row in set (0.00 sec)

改变系统隔离级别，按照上面4中级别依次测试

 1 mysql> set tx_isolation='read-uncommitted';
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 关闭自动提交，开启事务，模拟转账，将zhangsanfeng账户中的余额减去500
 5 
 6 mysql> set autocommit=0;
 7 Query OK, 0 rows affected (0.00 sec)
 8 mysql> start transaction;
 9 Query OK, 0 rows affected (0.00 sec)
10 mysql> update cards set balance=balance-500 where name='zhangsanfeng';
11 Query OK, 1 row affected (0.00 sec)
12 Rows matched: 1  Changed: 1  Warnings: 0
13 
14 mysql> select * from cards;
15 +----+--------------+---------+
16 | id | name         | balance |
17 +----+--------------+---------+
18 |  1 | zhangsanfeng |    4500 |
19 +----+--------------+---------+
20 1 row in set (0.00 sec)

这时我并没有提交(commit),现在去第二个终端读取(这样主要是模拟现实中并发量很大的情况，这时没有提交事务，但是有个请求过来读取)

 1 --->set tx_isolation='read-uncommitted';    #由于是两个会话终端，终端一中的设置是基于会话的，在终端二中不生效，所以终端二也要设置隔离级别
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 --->select * from cards;
 5 +----+--------------+---------+
 6 | id | name         | balance |
 7 +----+--------------+---------+
 8 |  1 | zhangsanfeng |    4500 |
 9 +----+--------------+---------+
10 1 row in set (0.00 sec)
11 
12 --->

可以看到，在终端二中，可以看到终端一提交后的最终结果，但是终端一并没有提交，所以这种隔离效果最差。
在实际情况中，这样就容易产生幻读，就是当一个用户在读取数据时，这时别的终端发起了一个事务修改了数据，导致用户
在前后看到的数据不一致。

同理在read-committed(读提交)情况下，也会发生幻读，只是产生幻读的时间点向后移了，当事务提交后，用户才会发现
读取的数据和之前读取的不同。原理较简单，就不演示了。

 1 改变系统隔离级别，调整到(repeatable-read)
 2 终端一：
 3 mysql> set tx_isolation='repeatable-read';
 4 Query OK, 0 rows affected (0.00 sec)
 5 
 6 mysql> select * from cards;
 7 +----+--------------+---------+
 8 | id | name         | balance |
 9 +----+--------------+---------+
10 |  1 | zhangsanfeng |    5000 |
11 +----+--------------+---------+
12 1 row in set (0.00 sec)
13 
14 mysql> 
15 
16 终端二：
17 --->set tx_isolation='repeatable-read';
18 Query OK, 0 rows affected (0.00 sec)
19 
20 --->select * from cards;
21 +----+--------------+---------+
22 | id | name         | balance |
23 +----+--------------+---------+
24 |  1 | zhangsanfeng |    5000 |
25 +----+--------------+---------+
26 1 row in set (0.00 sec)
27 
28 --->
29 
30 
31 终端一开启了一个事务
32 mysql> set autocommit=0;
33 Query OK, 0 rows affected (0.00 sec)
34 
35 mysql> start transaction;
36 Query OK, 0 rows affected (0.00 sec)
37 
38 mysql> update cards set balance=balance-500 where name='zhangsanfeng';
39 Query OK, 1 row affected (0.00 sec)
40 Rows matched: 1  Changed: 1  Warnings: 0
41 
42 mysql> select * from cards;
43 +----+--------------+---------+
44 | id | name         | balance |
45 +----+--------------+---------+
46 |  1 | zhangsanfeng |    4500 |
47 +----+--------------+---------+
48 1 row in set (0.00 sec)
49 
50 mysql> 
51 
52 终端一没有提交事务，终端二查看结果如下：
53 --->select * from cards;
54 +----+--------------+---------+
55 | id | name         | balance |
56 +----+--------------+---------+
57 |  1 | zhangsanfeng |    5000 |
58 +----+--------------+---------+
59 1 row in set (0.01 sec)
60 
61 --->
62 
63 终端一提交后，终端二在读取结果如下：
64 mysql> commit;
65 Query OK, 0 rows affected (0.01 sec)
66 
67 mysql> 
68 
69 
70 --->select * from cards;
71 +----+--------------+---------+
72 | id | name         | balance |
73 +----+--------------+---------+
74 |  1 | zhangsanfeng |    4500 |
75 +----+--------------+---------+
76 1 row in set (0.00 sec)
77 
78 --->

这样虽然也可能产生幻读，但是产生幻读的时间点是在另外一个事务结束后，也就是说，事务在未提交之前，其他用户是感受不到的。
所以相对而言，这种隔离效果较之前两种要好，起到了事务的隔离效果，又不影响并发，MySQL中默认就是该级别。

 1 接下来测试serializable(串行化)
 2 终端一：
 3 mysql> set tx_isolation='serializable';
 4 Query OK, 0 rows affected (0.00 sec)
 5 
 6 mysql> set autocommit=0;
 7 Query OK, 0 rows affected (0.00 sec)
 8 
 9 mysql> start transaction;
10 Query OK, 0 rows affected (0.01 sec)
11 
12 mysql> update cards set balance=balance-500 where name='zhangsanfeng';
13 Query OK, 1 row affected (0.04 sec)
14 Rows matched: 1  Changed: 1  Warnings: 0
15 
16 mysql> select * from cards;
17 +----+--------------+---------+
18 | id | name         | balance |
19 +----+--------------+---------+
20 |  1 | zhangsanfeng |    4500 |
21 +----+--------------+---------+
22 1 row in set (0.00 sec)
23 
24 mysql> 
25 
26 终端二：
27 --->set tx_isolation='serializable';
28 Query OK, 0 rows affected (0.00 sec)
29 
30 --->select * from cards;
31 +----+--------------+---------+
32 | id | name         | balance |
33 +----+--------------+---------+
34 |  1 | zhangsanfeng |    5000 |
35 +----+--------------+---------+
36 1 row in set (0.00 sec)
37 --->update cards set balance=balance-500 where name='zhangsanfeng';    //这一步可以看到，光标一直卡者，数据的修改不能成功，就是应为上面的事务没有提交。
38 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

可以看到串行化后，当事务没有结束时，该数据就一直处于锁定状态，其他用户不能操作。这种隔离级别最高，但是不支持并发，
所以在实际情况中一般不会用。

最后总结一下：
事务的隔离级别越高，事务越安全，但是并发能力越差。