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种隔离级别下，事务过程中有可能出现的情况

cards建表语句如下：
mysql> show create table cards \G
*************************** 1. row ***************************
       Table: cards
Create Table: CREATE TABLE `cards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL DEFAULT '',
  `balance` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


现在表中有一条数据如下所示：
mysql> select * from cards;
+----+--------------+---------+
| id | name         | balance |
+----+--------------+---------+
|  1 | zhangsanfeng |    5000 |
+----+--------------+---------+
1 row in set (0.00 sec)


查看当前MySQL系统隔离级别
mysql> show global variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

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

mysql> set tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec)

关闭自动提交，开启事务，模拟转账，将zhangsanfeng账户中的余额减去500

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update cards set balance=balance-500 where name='zhangsanfeng';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from cards;
+----+--------------+---------+
| id | name         | balance |
+----+--------------+---------+
|  1 | zhangsanfeng |    4500 |
+----+--------------+---------+
1 row in set (0.00 sec)

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

--->set tx_isolation='read-uncommitted';    #由于是两个会话终端，终端一中的设置是基于会话的，在终端二中不生效，所以终端二也要设置隔离级别
Query OK, 0 rows affected (0.00 sec)

--->select * from cards;
+----+--------------+---------+
| id | name         | balance |
+----+--------------+---------+
|  1 | zhangsanfeng |    4500 |
+----+--------------+---------+
1 row in set (0.00 sec)

--->

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

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

改变系统隔离级别，调整到(repeatable-read)
终端一：
mysql> set tx_isolation='repeatable-read';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from cards;
+----+--------------+---------+
| id | name         | balance |
+----+--------------+---------+
|  1 | zhangsanfeng |    5000 |
+----+--------------+---------+
1 row in set (0.00 sec)

mysql> 

终端二：
--->set tx_isolation='repeatable-read';
Query OK, 0 rows affected (0.00 sec)

--->select * from cards;
+----+--------------+---------+
| id | name         | balance |
+----+--------------+---------+
|  1 | zhangsanfeng |    5000 |
+----+--------------+---------+
1 row in set (0.00 sec)

--->


终端一开启了一个事务
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> update cards set balance=balance-500 where name='zhangsanfeng';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from cards;
+----+--------------+---------+
| id | name         | balance |
+----+--------------+---------+
|  1 | zhangsanfeng |    4500 |
+----+--------------+---------+
1 row in set (0.00 sec)

mysql> 

终端一没有提交事务，终端二查看结果如下：
--->select * from cards;
+----+--------------+---------+
| id | name         | balance |
+----+--------------+---------+
|  1 | zhangsanfeng |    5000 |
+----+--------------+---------+
1 row in set (0.01 sec)

--->

终端一提交后，终端二在读取结果如下：
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> 


--->select * from cards;
+----+--------------+---------+
| id | name         | balance |
+----+--------------+---------+
|  1 | zhangsanfeng |    4500 |
+----+--------------+---------+
1 row in set (0.00 sec)

--->

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

接下来测试serializable(串行化)
终端一：
mysql> set tx_isolation='serializable';
Query OK, 0 rows affected (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> update cards set balance=balance-500 where name='zhangsanfeng';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from cards;
+----+--------------+---------+
| id | name         | balance |
+----+--------------+---------+
|  1 | zhangsanfeng |    4500 |
+----+--------------+---------+
1 row in set (0.00 sec)

mysql> 

终端二：
--->set tx_isolation='serializable';
Query OK, 0 rows affected (0.00 sec)

--->select * from cards;
+----+--------------+---------+
| id | name         | balance |
+----+--------------+---------+
|  1 | zhangsanfeng |    5000 |
+----+--------------+---------+
1 row in set (0.00 sec)
--->update cards set balance=balance-500 where name='zhangsanfeng';    //这一步可以看到，光标一直卡者，数据的修改不能成功，就是应为上面的事务没有提交。
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

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

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