MySQL学习（1）—— 事务隔离级别

事务的四种隔离级别

1、事务的四大特性（ACID）

1. 原子性（Atomicity）：原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。
2. 一致性（Consistency）：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态，数据库的完整性约束没有被破坏。
3. 隔离性（Isolation）：一个事务的影响在该事务提交之前对其他事务都是不可见的。
4. 持久性（Durability）：事务一旦提交，其结果就是永久性的。即使故障宕机，数据库也能恢复数据。隔离性有其他叫法，如并发控制。

2、四种事务隔离级别

1. Read Uncommitted（读未提交）：事物能读到不同事物没有提交（未commit）的数据结果，实际应用比较少，会产生脏读，事物已经读到其他事物未提交的数据，但数据被回滚，称为脏读。
2. Read Committed（读已提交）：事物读取其他事物已经提交的数据，读取到的是最新的数据，所以会出现在同一事物中select读取到的数据前后不一致，会出现不可重复读问题，不可重复读问题就是我们在同一个事务中执行完全相同的select语句时可能看到不一样的结果。
3. Repeatable Read（可重复读）：mysql默认事物隔离级别，在同一事物中多次读取同样的数据结果是一样的，解决了不可重复读的问题，此级别会出现幻读的问题，幻读：当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。
4. Serializable（串行化）：最高的事物隔离级别，串行化强制事物排序阻塞，避免事物冲突，解决了上述所有的问题，它使用了共享锁，执行效率低下，会导致大量的超时和锁切换竞争现象，实际开发应用很少。

隔离级别	脏读	不可重复读	幻读
Read Uncommitted（读未提交）	存在	存在	存在
Read Committed（读已提交）	不存在	存在	存在
Repeatable Read（可重复读）	不存在	不存在	存在
Serializable（串行化）	不存在	不存在	不存在

mysql默认的事物隔离级别为可重复读（Repeatable Read），可使用select @@tx_isolation 查看

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.03 sec)

设置事务的隔离级别
set [session | global] transaction isolation level [read uncommitted | read committed | repeatable |serializable];

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.03 sec)

1、读未提交（Read Uncommitted）

这种情况下可能出现脏读：

脏读：在隔离级别读未提交中可能会出现，一个事务读取另外一个事务还没有提交的数据叫脏读，事物A读取了事物B更新的事物，事物B没有commit并且回滚，此时就事物A产生脏读，应用也没保证数据的正确性。

CREATE TABLE `account` (
  `id` int(11) NOT NULL,
  `name` varchar(16) DEFAULT NULL,
  `money` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

开始实验之前开启两个连接，切换到实验的数据库，事务隔离级别都设置成读未提交
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.02 sec)
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.03 sec)
实验前插入一条数据:
mysql> select * from account where id = 1;
+----+----------+-------+
| id | name     | money |
+----+----------+-------+
|  1 | zhangsan |  1000 |
+----+----------+-------+
1 row in set (0.04 sec)

事务A:修改数据，暂时不提交
mysql> update account set money = money - 300 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
事务B:查看数据，读取到了事务还没提交的数据，如果事务A不提交而是回滚，那么便产生了脏读
mysql> select * from account where id = 1;
+----+----------+-------+
| id | name     | money |
+----+----------+-------+
|  1 | zhangsan |   700 |
+----+----------+-------+
1 row in set (0.04 sec)

事务A:事务回滚
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from account where id = 1;
+----+----------+-------+
| id | name     | money |
+----+----------+-------+
|  1 | zhangsan |  1000 |
+----+----------+-------+
1 row in set (0.04 sec)

事务B:重新读取数据，不是刚才读到的数据，产生脏读
mysql> select * from account where id = 1;
+----+----------+-------+
| id | name     | money |
+----+----------+-------+
|  1 | zhangsan |  1000 |
+----+----------+-------+
1 row in set (0.03 sec)

2、读已提交（Read Committed）

这种情况下可能出现不可重复读：

1、开启两个连接，都设置事务的隔离级别为read committed

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.03 sec)

2、开启两个事务A、B，事务的先后顺序用序号表示

事务A:都是同个事务中
1、
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account where id = 1;
+----+----------+-------+
| id | name     | money |
+----+----------+-------+
|  1 | zhangsan |  1000 |
+----+----------+-------+
1 row in set (0.03 sec)
3、
mysql> select * from account where id = 1;
+----+----------+-------+
| id | name     | money |
+----+----------+-------+
|  1 | zhangsan |   700 |
+----+----------+-------+
1 row in set (0.04 sec)
5、
mysql> select * from account where id = 1;
+----+----------+-------+
| id | name     | money |
+----+----------+-------+
|  1 | zhangsan |   800 |
+----+----------+-------+
1 row in set (0.04 sec)

事务B:
2、
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set money = money - 300 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)
4、
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set money = money + 100 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

可以看到事务A读取到的数据与之前读取到的不一致，此时就产生了不可重复读的现象。

3、可重复读(Repeatable Read)

该隔离级别下会出现幻读现像：

幻读：在隔离级别可重复读中可能出现的问题，幻读是针对按范围读取多条数据的现象。同一事务A多次查询，若另一事务B只是update,则A事务多次查询结果相同；若B事务insert/delete数据，则A事务多次查询就会发现新增或缺少数据，出现幻读。

事务A:
1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+----------+-------+
| id | name     | money |
+----+----------+-------+
|  1 | zhangsan |   800 |
|  2 | lisi     |  2000 |
|  3 | kuangtu  |  3000 |
|  4 | leihuo   |   400 |
+----+----------+-------+
4 rows in set (0.03 sec)

3:
mysql> select * from account;
+----+----------+-------+
| id | name     | money |
+----+----------+-------+
|  1 | zhangsan |   800 |
|  2 | lisi     |  2000 |
|  3 | kuangtu  |  3000 |
|  4 | leihuo   |   400 |
+----+----------+-------+
4 rows in set (0.03 sec)

mysql> insert into account value(5,'laoban',360);
1062 - Duplicate entry '5' for key 'PRIMARY'

事务B:
2:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account(id,name,money) value(5,'laoban',360);
Query OK, 1 row affected (0.01 sec)

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

4:
mysql> select * from account;
+----+----------+-------+
| id | name     | money |
+----+----------+-------+
|  1 | zhangsan |   800 |
|  2 | lisi     |  2000 |
|  3 | kuangtu  |  3000 |
|  4 | leihuo   |   400 |
|  5 | laoban   |   360 |
+----+----------+-------+
5 rows in set (0.04 sec)

事务B插入新的数据后，事务A还没提交的事务中查找不到该消息但是事务A插入该信息会提示信息已存在，但是事务B提交后再查询可以查到这个数据。幻读可以通过加锁解决。