MySQL事务隔离级别学习

1. MySQL事务隔离级别学习笔记

1.1. 隔离级别

READ UNCOMMITTED 未提交读

在READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称作脏读。这个级别会导致很多问题，从性能上来说，READ UNCOMMITTED不会比其他的级别好太多，但确缺乏其他级别的很多好处，除非真的有必要的理由，在实际应用中一般很少使用。
READ COMMITED 提交读

大多数数据库系统的默认隔离级别是 READ COMMITED（但mysql不是）。READ COMMITED满足前面提到的隔离性简单定义：一个事务开始时，只能看见已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读，因为两次执行同样的查询，可能会得到不一样的结果。

这里的原因是，在两次执行同样的查询中间，可能由其他事务修改了影响查询结果的数据。在 Read Commited级别下，会发生这种情况。
REPEATABLE READ 可重复读

MySQL的默认事务隔离级别。
REPEATABLE READ解决了脏读的问题。该级别保证了在同一个事务中多次读取同样的记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另一个幻读的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。InnoDB存储引擎通过多版本并发控制（MVCC）解决了幻读的问题。

在一个事务未提交前，多次查询相同的查询，都会返回相同的结果。即使在此期间有其他事务已提交了影响该查询的数据。
SERIALIZABLE 可串行化

SERIALIZABLE 是最高的隔离级别。它通过强事务串行执行，避免了前面说的幻读的问题。简单来说，SERIALIZABLE会在曲度的每一行数据都加上锁，所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据一致性而且可以接受没有并发的情况下，才考虑使用该级别。

1.2. 实验

上面是摘自《高性能MySQL》一书，光这么看，可能也理解不到多少东西，不如直接做实验亲身体验一下，加深理解。

我们先简单创建一个表 users，结构如下：

+------------+------------------+------+-----+-------------------+----------------+
| Field      | Type             | Null | Key | Default           | Extra          |
+------------+------------------+------+-----+-------------------+----------------+
| id         | int(11) unsigned | NO   | PRI | <null>            | auto_increment |
| name       | varchar(50)      | NO   |     |                   |                |
| created_at | datetime         | NO   |     | CURRENT_TIMESTAMP |                |
| updated_at | datetime         | NO   |     | CURRENT_TIMESTAMP |                |
| deleted_at | datetime         | YES  |     | <null>            |                |
+------------+------------------+------+-----+-------------------+----------------+

实现插入三条数据：

+----+-------------+---------------------+---------------------+------------+
| id | name        | created_at          | updated_at          | deleted_at |
+----+-------------+---------------------+---------------------+------------+
| 1  | coolcao2018 | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom         | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili        | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+-------------+---------------------+---------------------+------------+

然后打开两个终端，A和B分别表示两个用户同时在操作。

1.2.1. READ UNCOMMITTED 未提交读

对于用户A，操作：

mysql root@localhost:test> set session transaction isolation level read uncommitted;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> select * from users;

+----+-------------+---------------------+---------------------+------------+
| id | name        | created_at          | updated_at          | deleted_at |
+----+-------------+---------------------+---------------------+------------+
| 1  | coolcao2018 | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom         | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili        | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+-------------+---------------------+---------------------+------------+

然后，用户B操作，

mysql root@localhost:test> set session transaction isolation level read uncommitted;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> update users set name='coolcao' where id=1;

此时，用户B并未提交事务，用户A进行查询操作看看：

mysql root@localhost:test> select * from users;

+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom     | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili    | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+---------+---------------------+---------------------+------------+

从整个过程来看，用户B还并未提交事务，但是A却已经能够直接读到B的更新。

从上面实验结果来看，不难理解上面对于 READ UNCOMMITTED级别的描述：在READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。

1.2.2. READ COMMITTED 提交读

同时将A，B两个终端的事务级别设置为 read committed:

 // 在A，B两个终端都执行
 set session transaction isolation level read committed;

对于A，我们开启一个事务，然后更新一下数据，但并不提交事务：

mysql root@localhost:test>  set session transaction isolation level read committed;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> select * from users;

+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom     | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili    | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+---------+---------------------+---------------------+------------+

mysql root@localhost:test> update users set name='coolcao222' where id=1;
mysql root@localhost:test> select * from users;

+----+------------+---------------------+---------------------+------------+
| id | name       | created_at          | updated_at          | deleted_at |
+----+------------+---------------------+---------------------+------------+
| 1  | coolcao222 | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom        | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili       | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+------------+---------------------+---------------------+------------+

然后，在B终端，开启另外一个事务，进行数据查询：

mysql root@localhost:test> set session transaction isolation level read committed;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> select * from users;

+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom     | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili    | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+---------+---------------------+---------------------+------------+

然后，将A事务提交：

commit;

这时，再在B查询：

mysql root@localhost:test> select * from users;

+----+------------+---------------------+---------------------+------------+
| id | name       | created_at          | updated_at          | deleted_at |
+----+------------+---------------------+---------------------+------------+
| 1  | coolcao222 | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom        | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili       | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+------------+---------------------+---------------------+------------+

从结果来看，也不难理解 read committed级别，对于一个事务，只能读取到当前事务的数据和其他已经提交的事务的数据，对于其他未提交事务的数据，读不到。
而且，从上面的实验结果中，我们也看到了，会话B在会话A提交事务前后查询的结果并不一致，这也就是上面所说的，不可重复读。

1.2.3. REPEATABLE READ 可重复读

我们将会话A设置为REPEATABLE READ :

mysql root@localhost:test> set session transaction isolation level repeatable read;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> select * from users;

+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-08-10 15:21:02 | 2018-08-10 15:21:02 | <null>     |
| 2  | tom     | 2018-08-10 15:21:07 | 2018-08-10 15:21:07 | <null>     |
| 3  | lili    | 2018-08-10 15:21:11 | 2018-08-10 15:21:11 | <null>     |
+----+---------+---------------------+---------------------+------------+

此时，我们在B终端插入一条数据：

mysql root@localhost:test> insert into users (id,name) values (4,'coco');
mysql root@localhost:test> commit;
mysql root@localhost:test> select * from users;

+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-08-10 15:21:02 | 2018-08-10 15:21:02 | <null>     |
| 2  | tom     | 2018-08-10 15:21:07 | 2018-08-10 15:21:07 | <null>     |
| 3  | lili    | 2018-08-10 15:21:11 | 2018-08-10 15:21:11 | <null>     |
| 4  | coco    | 2018-08-10 15:23:50 | 2018-08-10 15:23:50 | <null>     |
+----+---------+---------------------+---------------------+------------+

在终端B中，插入一条记录，并提交，这时id=4的用户已经被插入到数据库。

此时，再回到终端A，查询：

mysql root@localhost:test> select * from users;

+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-08-10 15:21:02 | 2018-08-10 15:21:02 | <null>     |
| 2  | tom     | 2018-08-10 15:21:07 | 2018-08-10 15:21:07 | <null>     |
| 3  | lili    | 2018-08-10 15:21:11 | 2018-08-10 15:21:11 | <null>     |
+----+---------+---------------------+---------------------+------------+

哎，查询的结果中，没有B刚插入的id=4的用户，这也就是说该级别的事务隔离，保证了在同一个事务中多次读取同样的记录的结果是一致的。这时，我们在A中插入一条记录：

mysql root@localhost:test> insert into users (id,name) values (4,'coco');
(1062, u"Duplicate entry '4' for key 'PRIMARY'")

哎，这个时候，数据库报错了，提示主键重复。明明我在这个事务中，查询的数据只有1,2,3，为什么插入4的时候提示主键冲突呢？是发生幻觉了么？是的，发生“幻读”了。由于REPEATABLE READ级别的隔离，在一个事务中，多次读取同样记录的结果是一致的，在这多次读取之间，被别的事务插入了新的数据，这时前事务再插入数据，必然会导致错误。

在一个事务未提交前，多次查询相同的查询，返回的结果是相同的，即使在此期间，其他事务已经提交了影响该查询的数据。

1.2.4. SERIALIZABLE 可串行化

我们将A，B同时设置为SERIALIZABLE, 然后在A开启是个事务，做一个简单查询：

mysql root@localhost:test> set session transaction isolation level serializable;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> select * from users where id<10;

+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-08-10 15:21:02 | 2018-08-10 15:21:02 | <null>     |
| 2  | tom     | 2018-08-10 15:21:07 | 2018-08-10 15:21:07 | <null>     |
| 3  | lili    | 2018-08-10 15:21:11 | 2018-08-10 15:21:11 | <null>     |
| 4  | coco    | 2018-08-10 15:23:50 | 2018-08-10 15:23:50 | <null>     |
| 5  | juli    | 2018-08-10 15:31:32 | 2018-08-10 15:31:32 | <null>     |
+----+---------+---------------------+---------------------+------------+

此时，A事务并未提交，然后在B再开启一个事务，进行插入操作：

mysql root@localhost:test> set session transaction isolation level serializable;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> insert into users (id,name) values (6,'kate');
(1205, u'Lock wait timeout exceeded; try restarting transaction')

你会发现，哎我去，B事务被挂住了，然后过了一段时间，提示了错误 (1205, u'Lock wait timeout exceeded; try restarting transaction')，说等待锁超时。
是的，在串行化级别，会在读取的每一行数据都加上锁，也就是说，上面A事务在读取时，已经加了锁，此时B事务在插入操作时，得等待锁的放开，时间一长，A锁未放开，B就报错了。
从实验中可以看出，可串行化级别，由于要保证避免幻读而加了锁导致效率以及可能会触发的等待锁超时等错误，实际应用中，该级别的事务隔离也很少使用。

对照着实验结果，来理解上面四个隔离级别，就容易理解了。

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 162,710评论 4赞 376
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 68,839评论 2赞 308
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 112,295评论 0赞 255
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 44,776评论 0赞 223
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 53,198评论 3赞 297
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 41,074评论 1赞 226
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 32,200评论 2赞 322
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,986评论 0赞 214
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,733评论 1赞 250
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,877评论 2赞 254
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,348评论 1赞 265
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,675评论 3赞 265
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,393评论 3赞 246
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,209评论 0赞 9
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,996评论 0赞 201
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 36,212评论 2赞 287
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 36,003评论 2赞 280