Mysql 事务隔离级别

事务：是一组原子性的SQL查询语句，也可以被看做一个工作单元。

事务具有的四个特性：原子性(atomicity)、一致性(consistency)、隔离性(isolation)、持久性(durability)

1.关系数据库标准中的4个事务隔离级别

#未提交读(read uncommitted): 允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

#提交读(read committed): 只能读取到已经提交的数据。oracle等多数数据库默认都是该级别

#可重复读(repeated read): 可重复读。在同一个事务内的查询都是事务开始时刻一致的，innodb的默认级别。

在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读

#串行读(serializable): 完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。

2.事务并发导致的几个问题

#脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

#不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

#幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

隔离级	脏读可能性	不可重复读可能性	幻读可能性	加锁读
Read uncommitted	是	是	是	否
Read commited	否	是	是	否
Repeatable read	否	否	是	否
serializable	否	否	否	是

测试：

1. 建测试表，并加入一条数据

CREATE TABLE `isotest` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(50) DEFAULT NULL,

  `score` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> insert into isotest (name,score) values('wjlcn',80);

2. 建立两个连接，假定 A、B

* 测试隔离级为 read uncommitted 和 read committed

将A、B 分别设置隔离级为 read uncommitted

mysql> set autocommit=off;

mysql> set session transaction isolation level read uncommitted;

mysql> select @@tx_isolation;

+------------------+

| @@tx_isolation   |

+------------------+

| READ-UNCOMMITTED |

+------------------+

A：

mysql> update isotest set score=90 where name='wjlcn';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from isotest;

+----+-------+-------+

| id | name  | score |

+----+-------+-------+

|  1 | wjlcn |    90 |

+----+-------+-------+

1 row in set (0.00 sec)

B：

mysql> set session transaction isolation level read uncommitted;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from isotest;

+----+-------+-------+

| id | name  | score |

+----+-------+-------+

|  1 | wjlcn |    90 |

+----+-------+-------+

1 row in set (0.00 sec)

mysql> set session transaction isolation level read committed;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from isotest;

+----+-------+-------+

| id | name  | score |

+----+-------+-------+

|  1 | wjlcn |    80 |

+----+-------+-------+

1 row in set (0.00 sec)

注：在 read uncommitted 隔离级，B上可以脏读，而在read committed上却不可以。

A：

mysql> commit;

Query OK, 0 rows affected (0.01 sec)

B：

mysql> select * from isotest;

+----+-------+-------+

| id | name  | score |

+----+-------+-------+

|  1 | wjlcn |    90 |

+----+-------+-------+

1 row in set (0.00 sec)

注：当A执行commit后，在read committed上读到了修改后的数据，验证了不可重复读。

* 测试隔离级为 repeatable read

将A、B 隔离级设置为：repeatable read  

mysql> set session transaction isolation level repeatable read;

测试不可重复读可能性

A：

mysql> update isotest set score=100 where name='wjlcn';

B：

mysql> select * from isotest;

+----+-------+-------+

| id | name  | score |

+----+-------+-------+

|  1 | wjlcn |    90 |

+----+-------+-------+

1 row in set (0.00 sec)

A：

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

B：

mysql> select * from isotest;

+----+-------+-------+

| id | name  | score |

+----+-------+-------+

|  1 | wjlcn |    90 |

+----+-------+-------+

1 row in set (0.01 sec)

注：A事务已经commit，B事务中数据仍旧没变，验证了可重复读。

    A事务已经commit，而B事务查的数据没变，在B事务没有提交前，对同一行数据进行update又会怎样？（另外已经验证，会在A事务commit后再进行update）

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from isotest;

+----+-------+-------+

| id | name  | score |

+----+-------+-------+

|  1 | wjlcn |   100 |

+----+-------+-------+

1 row in set (0.00 sec)

注：只有将B事务commit，重新开始新的事务时，才可以查到更改后的数据。

测试幻读可能性

A：

mysql> insert into isotest (name,score) values('kaka',80);

Query OK, 1 row affected (0.00 sec)

mysql> select * from isotest;

+----+-------+-------+

| id | name  | score |

+----+-------+-------+

|  1 | wjlcn |   100 |

|  2 | kaka  |    80 |

+----+-------+-------+

2 rows in set (0.00 sec)

B：

mysql> select * from isotest;

+----+-------+-------+

| id | name  | score |

+----+-------+-------+

|  1 | wjlcn |   100 |

+----+-------+-------+

1 row in set (0.00 sec)

A：

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

B：

mysql> select * from isotest;

+----+-------+-------+

| id | name  | score |

+----+-------+-------+

|  1 | wjlcn |   100 |

+----+-------+-------+

1 row in set (0.00 sec)

注：此时用

//mysql> select * from isotest lock in share mode;

//mysql> select * from isotest for update;

//可以查到2条记录，InnoDB提供了这样的机制，在默认的可重复读的隔离级别里，可以使用加锁读去查询最新的数据。

mysql> update isotest set score=180;

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from isotest;

+----+-------+-------+

| id | name  | score |

+----+-------+-------+

|  1 | wjlcn |   180 |

|  2 | kaka  |   180 |

+----+-------+-------+

2 rows in set (0.00 sec)

怎么啦？咋多出一行？ 幻读来了……

但innodb 在repeatable read可以避免幻读又是怎么回事？

MySQL InnoDB的可重复读并不保证避免幻读，需要应用使用加锁next-key locks读来保证。

* 测试隔离级为 serializable

A:

mysql> set session transaction isolation level serializable;

mysql> update isotest set score=300 where id=4;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

B:

mysql> set session transaction isolation level serializable;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from isotest;

此时，B事务会被阻塞，因为A事务要个更新id=4 这一行，因此给这行加上了排它锁，B事务再将给 其加上共享锁将会失败。使用A事务commit之后，B事务才会往下执行。