【MySQL进阶篇第一节】事务与隔离级别

一、引言

1.1 事务的基本概念

事务是指作为单个逻辑工作单元执行的一系列操作，可以被看作一个单元的一系列SQL语句的集合。要么完全地执行，要么完全地不执行。它是一个不可分割的工作单位，它是并发控制的基本单位。

如果不对数据库进行并发控制，可能会产生脏读、非重复读、幻像读、丢失修改的异常情况。

1.2 事务的四大特点

1.2.1 事务的特点是：ACID

原子性（Atomic）：指数据库事务是不可分割的工作单位，一个事务要么执行，要么不执行；
一致性（Consistency）：指事务的运行并不改变数据库中数据的一致性，不破坏完整性约束；
隔离性（Isolation）：指两个以上的事务不会出现交错执行的状态，比如一个事务的操作结果不会让另外一个事务看到，直到完成；
持久性（Durability）：指事务运行成功以后，对数据库的更新操作是持久化存储的，不会无缘无故地回滚。

1.2.2 举个栗子🌰

“A向B汇钱100”
读出A账号余额（500）。
A账号扣钱操作（500-100）。
结果写回A账号（400）。
读出B账号余额（500）。
B账号做加法操作（500+100）。
结果写回B账号（600）。

原子性：

保证1-6所有过程要么都执行，要么都不执行。如果异常了那么回滚。

一致性

转账前，A和B的账户中共有500+500=1000元钱。转账后，A和B的账户中共有400+600=1000元。

隔离性

在A向B转账的整个过程中，只要事务还没有提交（commit），查询A账户和B账户的时候，两个账户里面的钱的数量都不会有变化。

持久性

一旦转账成功（事务提交），两个账户的里面的钱就会真的发生变化

1.3 事务的实现机制

事务的（ACID）特性是由关系数据库管理系统来实现的。

数据库管理系统采用日志来保证事务的原子性、一致性和持久性。日志记录了事务对数据库所做的更新，如果某个事务在执行过程中发生错误，就可以根据日志，撤销事务对数据库已做的更新，使数据库退回到执行事务前的初始状态。

数据库管理系统采用锁机制来实现事务的隔离性。当多个事务同时更新数据库中相同的数据时，只允许持有锁的事务能更新该数据，其他事务必须等待，直到前一个事务释放了锁，其他事务才有机会更新该数据。

二、事务的隔离级别

2.1 数据库事务与隔离级别

未提交读(Read Uncommitted)：顾名思义，就是一个事务可以读取另一个未提交事务的数据，允许脏读。

提交读(Read Committed)：顾名思义，就是一个事务要等另一个事务提交后才能读取数据。

可重复读(Repeatable Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻读。

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

上面这样的教科书式定义第一次接触事务隔离概念的同学看了可能会一脸懵逼，不知所云，下面我们通过具体的实例来解释四个隔离级别。

2.2 修改事务隔离级别

有两种方式可以修改事务的隔离级别，一种通过配置文件修改全局性的配置，一种是通过指令修改的会话级别的配置。下面分别针对这两种方式进行介绍。

1.全局修改，修改mysql.ini(windows)或者my.cnf(linux)配置文件，在最后加上

1 #可选参数有：READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
2 [mysqld]
3 transaction-isolation = REPEATABLE-READ

这里全局默认是REPEATABLE-READ,其实MySQL本来默认也是这个级别。

2.对当前session修改，在登录mysql客户端后，执行命令：

# 查看当前会话的事务隔离级别
mysql> SELECT @@SESSION.tx_isolation;
+------------------------+
| @@SESSION.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
1 row in set (0.00 sec)

# 修改当前会话的事务隔离级别
mysql> set session transaction isolation level read uncommitted;

2.3 案例分析

首先我们创建一个user表：

CREATE TABLE user (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE `uniq_name` USING BTREE (name)
) ENGINE=`InnoDB` AUTO_INCREMENT=10 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

备注：

“ UNIQUE uniq_name USING BTREE (name)”的含义是在name字段上利用以B树为数据结构建立唯一性索引，名字为uniq_name。
“COLLATE utf8_general_ci”指定字符集的排序规则。

2.2.1 未提交读（Read Uncommitted）

可能会读取到其他会话中未提交的部分数据，这部分数据称之为脏数据。下面通过一个示例演示这种场景。

将事务的隔离级别设置为read uncommitted：

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set (0.00 sec)

在下面我们开了两个终端分别用来模拟事务一和事务二，按照备注的操作编号的顺序交替执行，后面的操作与此类似，不再赘述。

事务1

mysql> start transaction; # 操作1
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user(name) values('louxj424'); # 操作3
Query OK, 1 row affected (0.05 sec)

事务2

mysql> start transaction; # 操作2
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # 操作4
+----+----------+
| id | name     |
+----+----------+
| 10 | louxj424 |
+----+----------+
1 row in set (0.00 sec)

从上面的执行结果可以很清晰的看出来，在read uncommited 级别下面我们在事务一中可能会读取到事务二中没有commit的数据，这就是所谓的“脏读”，意思是读取到了还没有提交的、可能不是最终提交内容的数据，一旦把这个数据拿去使用容易导致不一致性的问题。比如，上述操作的货币数额，前一个事务中有增加200 的操作，后一个事务读取到了这200，就离开了。而后前一个事务后来又减少了200，才最终提交该事务，但是后一个事务却以为200是一直存在的，这样容易出现严重的问题。

2.2.2 提交读（Read Committed）

通过设置隔离级别为提交读read committed可以解决上面提到的“脏读”的问题。

mysql> set session transaction isolation level read committed;

事务一

mysql> start transaction; # 操作一
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # 操作三
+----+----------+
| id | name     |
+----+----------+
| 10 | louxj424 |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from user; # 操作五，操作四的修改并没有影响到事务一
+----+----------+
| id | name     |
+----+----------+
| 10 | louxj424 |
+----+----------+
1 row in set (0.00 sec)
mysql> select * from user; # 操作七
+----+------+
| id | name |
+----+------+
| 10 | zhzh |
+----+------+
1 row in set (0.00 sec)
mysql> commit; # 操作八
Query OK, 0 rows affected (0.00 sec)

事务二

mysql> start transaction; # 操作二
Query OK, 0 rows affected (0.00 sec)
mysql> update user set name='zhzh' where id=10; # 操作四
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> commit; # 操作六
Query OK, 0 rows affected (0.08 sec)

虽然脏读的问题解决了，但是注意在事务一的操作七中，事务二在操作六commit后会造成事务一在同一个transaction中两次读取到的数据不同，这就是不可重复读问题，使用第三个事务隔离级别repeatable read可以解决这个问题。

不可重复读案例：

小A去买东西（卡里有1万元），当他买单时（事务开启），系统事先检测到他的卡里有1万，就在这个时候！！小A的妻子要把钱全部转出充当家用，并提交。当系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。A就会很郁闷

分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

2.2.3 可重复读（Repeatable Read）

MySQL的Innodb存储引擎默认的事务隔离级别就是可重复读（Repeated Read），所以一般情况下无需手动设置，但是如果调整到了其他的隔离级别，想要再次调整过来，可以使用如下的指令：

mysql> set session transaction isolation level repeatable read;

事务一

mysql> start tansactoin; # 操作一
mysql> select * from user; # 操作五
+----+----------+
| id | name     |
+----+----------+
| 10 | louxj424 |
+----+----------+
1 row in set (0.00 sec)
mysql> commit; # 操作六
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user; # 操作七
+----+------+
| id | name |
+----+------+
| 10 | zhzh |
+----+------+
1 row in set (0.00 sec)

事务二

mysql> start tansactoin; # 操作二
mysql> update user set name='zhzh' where id=10; # 操作三
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> commit; # 操作四

在事务一的操作五中我们并没有读取到事务二在操作三中的update，只有在commit之后才能读到更新后的数据。

幻读问题

幻读：指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的“全部数据行”。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入“一行新数据”。操作第一个事务的用户发现表中还存在没有修改的数据行，就好象发生了幻觉一样。

实际上RR级别是可能产生幻读，InnoDB引擎官方称中利用MVCC多版本并发控制解决了这个问题，下面我们验证一下Innodb真的解决了幻读了么？

为了方便展示，我修改了一下上面的user表：

mysql> alter table user add salary int(11);
Query OK, 0 rows affected (0.51 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> delete from user where id = 10;
Query OK, 1 rows affected (0.07 sec)
mysql> insert into user(name, salary) value('louxj424', 88888888);
Query OK, 1 row affected (0.07 sec)
mysql> select * from user;
+----+----------+----------+
| id | name     | salary   |
+----+----------+----------+
| 10 | louxj424 | 88888888 |
+----+----------+----------+
1 row in set (0.00 sec)

事务一

mysql> start transaction;  # 操作一
Query OK, 0 rows affected (0.00 sec)
mysql> update user set salary='4444'; # 操作五，竟然影响了两行，不是说解决了幻读么？
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0
mysql> select * from user; # 操作六， Innodb并没有完全解决幻读
+----+----------+--------+
| id | name     | salary |
+----+----------+--------+
| 10 | louxj424 |   4444 |
| 11 | zhangsan |   4444 |
+----+----------+--------+
2 rows in set (0.00 sec)
mysql> commit; # 操作七
Query OK, 0 rows affected (0.04 sec)

事务二

mysql> start transaction; # 操作二
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user(name, salary) value('zhangsan', '666666'); # 操作三
Query OK, 1 row affected (0.00 sec)
mysql> commit; # 操作四
Query OK, 0 rows affected (0.04 sec)

从上面的例子可以看出，Innodb并没有如官方所说解决幻读，不过上面这样的场景中也不是很常见不用过多的担心。

一般解决幻读的方法是增加范围锁RangeS，锁定检索范围为只读，这样就避免了幻读。

2.2.4 串行读（Serializable）

Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

在实际开发中很少使用到，只要了解有这种方式的存在即可。

三、参考资料

实例分析MySQL下的四种事务隔离级别

最后编辑于：2019.01.12 23:45:11

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

【MySQL进阶篇 第一节】事务与隔离级别