mysql 0.00 sec_MySQL InnoDB隔离级别

上篇文章讨论了事务隔离级别，隔离级别这个东西在不同的数据库产品上，是有一些区别的，本篇重点讲讲mysql数据库。

四种标准的隔离级别MySQL数据库都支持，下面我们一个一个看过来先。

首先我们先创建一个简单的测试表。

CREATE TABLE tb1(

idINTNOT NULL,

valueDECIMALNOT NULL,

PRIMARY KEY (id)

)ENGINE=INNODB;

Read Uncommitted读未提交

首先，会话S1查询tb1表，没有记录返回。

mysql> set session transaction isolation level read uncommitted;

Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb1;

Empty set (0.00 sec)

mysql>

接着会话S2往tb1表写入一条记录，但不提交。

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb1 values(1, 100);

Query OK, 1 row affected (0.00 sec)

mysql>

接着在会话S1中再次查询tb1表，可以看到，会话S2未提交的脏数据被会话S1查询出来了。

mysql> set session transaction isolation level read uncommitted;

Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb1;

Empty set (0.00 sec)

mysql> select * from tb1;

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

| id | value |

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

| 1 | 100 |

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

1 row in set (0.00 sec)

mysql>

由此可见，在这个级别下，是会发生

脏读的。

Read Committed读已提交

首先，会话S1查询tb1表，没有记录返回。

mysql> set session transaction isolation level read committed;

Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb1;

Empty set (0.00 sec)

mysql>

接着，会话S2往tb1表写入一条记录，但不提交。

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb1 values(1, 100);

Query OK, 1 row affected (0.00 sec)

mysql>

这时，在会话S1中再次查询tb1表，依然没有记录返回，说明在这个级别下，未提交的数据是不会被查询出来的，能避免

脏读。

mysql> set session transaction isolation level read committed;

Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb1;

Empty set (0.00 sec)

mysql> select * from tb1;

Empty set (0.00 sec)

mysql>

这时，在会话S2中提交事务。

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb1 values(1, 100);

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql>

再次看看会话S1中的情况。

mysql> set session transaction isolation level read committed;

Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb1;

Empty set (0.00 sec)

mysql> select * from tb1;

Empty set (0.00 sec)

mysql> select * from tb1;

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

| id | value |

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

| 1 | 100 |

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

1 row in set (0.00 sec)

mysql>

在会话S2中提交事务后，会话S1中的事务可以看到新的记录了，说明该级别不能防止

不可重复读的问题。

Repeatable Read

再来看看Repeatable Read，首先会话S1查询tb1表，返回记录(1, 100)。

mysql> set session transaction isolation level repeatable read;

Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb1;

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

| id | value |

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

| 1 | 100 |

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

1 row in set (0.00 sec)

mysql>

接着会话S2更新记录(1, 100)-> (1, 101)并提交事务。

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> update tb1 set value = 101 where id = 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql>

在会话S1中看看情况。

mysql> set session transaction isolation level repeatable read;

Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb1;

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

| id | value |

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

| 1 | 100 |

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

1 row in set (0.00 sec)

mysql> select * from tb1;

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

| id | value |

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

| 1 | 100 |

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

1 row in set (0.00 sec)

mysql>

会话S1中查询到的还是原来的结果，如果提交或回滚事务后再次查询，看到的就是被会话S2更新后的数据了。

mysql> set session transaction isolation level repeatable read;

Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb1;

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

| id | value |

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

| 1 | 100 |

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

1 row in set (0.00 sec)

mysql> select * from tb1;

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

| id | value |

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

| 1 | 100 |

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

1 row in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb1;

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

| id | value |

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

| 1 | 101 |

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

1 row in set (0.00 sec)

mysql>

所以说这个级别可以防止

不可重复读，但是对于

幻读呢？我们来看看。

首先，会话S1查询tb1表，返回记录(1, 101)。

mysql> set session transaction isolation level repeatable read;

Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb1;

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

| id | value |

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

| 1 | 101 |

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

1 row in set (0.00 sec)

mysql>

接着会话S2插入一条新的记录(2, 200)并提交事务。

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb1(id, value) values(2, 200);

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql>

再看看会话S1中的情况。

mysql> set session transaction isolation level repeatable read;

Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb1;

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

| id | value |

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

| 1 | 101 |

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

1 row in set (0.00 sec)

mysql> select * from tb1;

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

| id | value |

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

| 1 | 101 |

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

1 row in set (0.00 sec)

mysql>

从结果看到，

幻读并没有发生，这个本人开始的认识有出入，因为在标准的事务隔离级别定义下，Repeatable Read是不能防止幻读产生的。这里是因为InnoDB使用了2种技术手段(MVCC AND GAP LOCK)实现了防止幻读的发生。

Serializable序列化

既然Repeatable Read已经可以防止

幻读的发生了，那Serializable存在的意义何在呢？我们还是来看一个例子吧。

首先，会话S1(在Repeatable Read隔离级别下)查询tb1表。

mysql> set session transaction isolation level repeatable read;

Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb1;

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

| id | value |

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

| 1 | 101 |

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

1 row in set (0.00 sec)

mysql>

接着，会话S2在tb1中插入一条新数据(2, 200)并提交事务。

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb1(id, value) values(2, 200);

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql>

回到会话S1中再次查询，从结果从看只有(1, 101)这条数据，但在尝试插入新数据(2, 200)时确提示主键重复错误了。

mysql> set session transaction isolation level repeatable read;

Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb1;

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

| id | value |

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

| 1 | 101 |

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

1 row in set (0.00 sec)

mysql> select * from tb1;

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

| id | value |

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

| 1 | 101 |

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

1 row in set (0.00 sec)

mysql> insert into tb1(id, value) values(2, 200);

ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

mysql>

如果隔离级别是Serialiable的话，上面的情况就不会发生了。来看看在Serialiable下的情况：

首先，会话S1查询tb1表。

mysql> set session transaction isolation level serializable;

Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from tb1;

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

| id | value |

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

| 1 | 101 |

| 2 | 200 |

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

2 rows in set (0.00 sec)

mysql>

接着会话S2尝试在tb1中插入一条新的记录。

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb1(id, value) values(3, 300);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql>

会话S2的插入操作将会被挂起，直到会话S1中的事务结束，所以就不存在Repeatable级别下的问题了，但是Serialiable级别下相关于串行化执行事务了，并行性能太差，一般不会在生产环境使用。

隔离级别与锁的探讨

在我的上一篇文章

《事务、事务并发》中已经提到S锁与X锁的概念，但是在测试中发现MySQL与其它数据库存在差异，比如在可重复读这个隔离级别下，查询操作并不会对数据记录加S锁，但更新操作还是会加X锁的。个人猜想，MySQL内部可能为每个数据行都维护了一个版本的概念，通过版本以及X锁来共同实现各种隔离级别的。

以下两种方式，可以显示地指定查询记录时加S锁或X锁。

select * from ... where ... lock in share mode

select * from ... where ... for update