mysql隔离级别理解

关于mysql隔离级别的实验

前言

本文参考高性能mysql一书用mysql对四种SQL隔离级别进行仿真实验，以加深对SQL隔离级别的理解。

简介

SQL定义了四种隔离级别。包括

• READ UNCOMMITED(未提交读)
• READ COMMITED(提交读)
• REPEATABLE READ(可重复读)
• REPEATABLE READ(可串行化)

其区别在于
表1

隔离级别	脏读可能性	不可重复读可能性	幻读可能性	加锁读
READ UNCOMMITED	Yes	Yes	Yes	No
READ COMMITED	No	Yes	Yes	No
REPEATABLE READ	No	Yes	No	No
SERAILIZABLE	No	No	No	Yes

个人理解，所涉及的几个名称的含义为：

• 脏读： 在一事务中读取到其他未提交事务的数据
• 不可重复读： 在一事务中读取到其他已提交事务的数据
• 幻读： 在一事务中读取到其他已提交事务的新增行
• 加锁读： 在一事务中对读取到的每一行数据都加锁

为了实验上述理解，以下建立一城市表进行各个隔离级别的仿真。
统一建立一城市表为

CREATE TABLE `isolationtest_city_innodb` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

并建立两个测试用城市：

INSERT INTO isolationtest_city_innodb(id, NAME) VALUES(131, '北京'), (289, '上海');

开启两个事务A，B。A，B的执行顺序为

时序	A	B
1	start transaction
2		start transaction
3	update isolationtest_city_innodb set name = ‘重庆’ where name = ‘北京’
4		select * from isolationtest_city_innodb
5	commit
6		select * from isolationtest_city_innodb
7	start transaction
8	insert isolationtest_city_innodb set id = 332, name = ‘天津’
9		select * from isolationtest_city_innodb
10	commit
11		select * from isolationtest_city_innodb
12		insert isolationtest_city_innodb set id = 332, name = ‘天津’
13		commit

后续实验都将建立在此基础上。

READ UNCOMMITED

根据表1所述，事务B在时序4,6,9,11上将均能获取到事务A的数据。为验证以上断言，在开启事务B前修改数据库隔离模式

set tx_isolation = 'READ-UNCOMMITTED';

测试得到时序4,6,9,11的结果分别是：

2 rows in set (0.00 sec)

时序	结果
4	+—–+——–+ | id | name | +—–+——–+ | 131 | 成都 | | 289 | 上海 | +—–+——–+
6	+—–+——–+ | id | name | +—–+——–+ | 131 | 成都 | | 289 | 上海 | +—–+——–+ 2 rows in set (0.00 sec)
9	+—–+——–+ | id | name | +—–+——–+ | 131 | 成都 | | 289 | 上海 | | 332 | 天津 | +—–+——–+ 3 rows in set (0.00 sec)
11	+—–+——–+ | id | name | +—–+——–+ | 131 | 成都 | | 289 | 上海 | | 332 | 天津 | +—–+——–+ 3 rows in set (0.00 sec)

测试结果和理解上是一致的。

READ COMMITED

根据表1所述，事务B在事务A未提交事务前是看不到事务A的数据的。据此断言对于事务B，时序4将只能看到事务B的数据；时序6能看到事务A在时序5提交的数据；时序9看不到事务A的数据;时序11可以看到事务A在时序10提交的数据。
为验证以上断言，在开启事务 B前修改数据库隔离模式

set tx_isolation = 'READ-COMMITTED';

测试得到时序4,6,9,11的结果分别是：

时序	结果
4	+—–+——–+ | id | name | +—–+——–+ | 131 | 北京 | | 289 | 上海 | +—–+——–+ 2 rows in set (0.00 sec)
6	+—–+——–+ | id | name | +—–+——–+ | 131 | 成都 | | 289 | 上海 | +—–+——–+ 2 rows in set (0.00 sec)
9	+—–+——–+ | id | name | +—–+——–+ | 131 | 成都 | | 289 | 上海 | +—–+——–+ 2 rows in set (0.00 sec)
11	+—–+——–+ | id | name | +—–+——–+ | 131 | 成都 | | 289 | 上海 | | 332 | 天津 | +—–+——–+ 3 rows in set (0.00 sec)

测试结果和理解上是一致的。

REPEATABLE READ

根据表1所述，事务B在事务A未提交事务前是看不到事务A的数据的。据此断言对于事务B，时序4将只能看到事务B的数据；时序6能看到事务A在时序5提交的数据；时序9看不到事务A的数据;时序11可以看到事务A在时序10提交的数据。
为验证以上断言，在开启事务 B前修改数据库隔离模式

set tx_isolation = 'READ-COMMITTED';

测试得到时序4,6,9,11,12的运行结果分别为

时序	结果
4	+—–+——–+ | id | name | +—–+——–+ | 131 | 北京 | | 289 | 上海 | +—–+——–+ 2 rows in set (0.00 sec)
6	+—–+——–+ | id | name | +—–+——–+ | 131 | 北京 | | 289 | 上海 | +—–+——–+ 2 rows in set (0.00 sec)
9	+—–+——–+ | id | name | +—–+——–+ | 131 | 北京 | | 289 | 上海 | +—–+——–+ 2 rows in set (0.00 sec)
11	+—–+——–+ | id | name | +—–+——–+ | 131 | 北京 | | 289 | 上海 | +—–+——–+ 2 rows in set (0.00 sec)
12	ERROR 1062 (23000): Duplicate entry ‘332’ for key ‘PRIMARY’

测试结果和预测的有所不同。在时序11中，事务B并没有读取到事务A插入的‘天津’行而产生幻读。然而，这种幻读的影响在时序12中体现出来。事务B因为事务A已插入了‘天津’行而无法重复插入相同主键行。似乎mysql只读数据过程中避免了幻读现象，而在写数据过程中却没能避免。更多的介绍可以参考http://blog.sina.com.cn/s/blog_499740cb0100ugs7.html

SERIALIZABLE

SERIALIZABLE通过强制事务串行执行，避免幻读问题《高性能mysql》

也就是，事务B将在事务A结束后才得以执行。因此，事务B将在时序4阻塞；在时序6读取到‘北京’被修改为‘成都’的信息；而事务A在时序7再次阻塞。后续时序将无法继续执行。

测试得时序4,6,7的结果分别为

时序	结果
4
6	+—–+——–+ | id | name | +—–+——–+ | 131 | 成都 | | 289 | 上海 | +—–+——–+ 2 rows in set (0.00 sec)
7

以上空结果表示查询阻塞。从结果上看，和断言是一致的。

总结

对SQL的四种隔离级别的理解基本正确。针对REPEATABLE READ，mysql似乎在读数据过程避免了幻读现象，然而在写数据过程上没有避免。

展望

后续对mysql的REPEATABLE READ在幻读问题上的处理进行进一步理解分析。