FAQ系列 | 修改事务隔离级别的暗门

0、导读

我们知道,MySQL里可以动态修改事务隔离级别(TRANSACTIOIN ISOLATION LEVEL),既可以加 GLOBAL 关键字直接修改全局的设置,也可以加 SESSION 关键字只修改当前会话的设置。那么,如果两个关键字都不加,会出现什么情况呢?

1、调整事务隔离级别

MySQL里,可以直接用 SET 指令调整事务隔离级别,既可以对全局调整,也可以只调整当前会话,其用法见下:

SET [GLOBAL | SESSION] TRANSACTION

[ REPEATABLE READ

| READ COMMITTED

| READ UNCOMMITTED

| SERIALIZABLE ]

我们都知道指定 GLOBAL 关键字是可以修改全局的设置,用这个方式修改完后,已经存在的连接还不会生效,只有对新建立的连接才会起作用。

而指定 SESSION 关键字则是只修改当前连接会话的设置,修改完后立即生效,再次连接后会恢复成全局的设置,对其他连接/会话也不起作用。

另外,如果都不指定 GLOBAL、SESSION 关键字,会发生什么情况呢?官方文档给出的解释是这样的:

Without any SESSION or GLOBAL keyword, the statement applies to the next (not started) transaction performed within the current session. Subsequent transactions revert to using the SESSION isolation level.

原文详见手册:13.3.6 SET TRANSACTION Syntax,出处链接:https://dev.mysql.com/doc/refman/5.6/en/set-transaction.html

意思是说:采用这种方式设置后,本次连接当前事务还未起作用,要到下一个(尚未启动的)新事务才起作用,下一个事务结束后,又将恢复成本次 SESSION中原先的设置

2、实验演示

为了证实上面的说法,我们可以做一个演示,详细过程见下:

T1 T2
select @@tx_isolation;

READ-COMMITTED

select @@tx_isolation;

READ-COMMITTED

set transaction isolation level SERIALIZABLE;

select @@tx_isolation;

+—————-+
| @@tx_isolation |
+—————-+
| READ-COMMITTED |

这种方式调整完后,可以看到隔离级别还是RC

select @@tx_isolation;

READ-COMMITTED

begin;

select * from t;
| a | b | c | d |
+—+—+—+—+
| 1 | 2 | 3 | 4 |

begin;

select * from t;
| a | b | c | d |
+—+—+—+—+
| 1 | 2 | 3 | 4 |

update t set d = 5 where a = 1;

被阻塞了,耐心等待中~

rollback;
Query OK, 1 row affected (13.13 sec)

Rows matched: 1  Changed: 1  Warnings: 0

等到T1执行rollback后才能继续,等了10多秒

rollback;
当前连接再次启动新事务测试
select @@tx_isolation;

READ-COMMITTED

可以看到隔离级别还是RC

select @@tx_isolation;

READ-COMMITTED

begin;

select * from t;

| a | b | c | d |
+—+—+—+—+
| 1 | 2 | 3 | 4 |

begin;

select * from t;

| a | b | c | d |
+—+—+—+—+
| 1 | 2 | 3 | 4 |

update t set d = 20 where a = 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

立即完成,没有阻塞

rollback;
rollback;

其中,测试表t的建表DDL:

CREATE TABLE `t` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
`d` int(11) NOT NULL,
PRIMARY KEY (`a`,`b`),
KEY `i_c` (`c`)
) ENGINE=InnoDB;

该表中只有一条记录:

SELECT * FROM t;

+—+—+—+—+
| a | b | c | d |
+—+—+—+—+
| 1 | 2 | 3 | 4 |
+—+—+—+—+

3、建议

如果需要全局调整事务隔离级别,最好是在 my.cnf 全局配置文件中直接设置好。

而如果是要在当前会话中临时调整,可以不要加上 SESSION 关键字,这样的话,当前事务结束下一个新事务开始后,会自行恢复 SESSION 的设置,无需人为调整,更加方便。

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 获得专属优惠

视频分享:MySQLDBA成长之路 – InnoDB事务隔离级别、行锁、死锁解读

录制了一个“MySQL DBA成长之路”系列视频,关于InnoDB事务隔离级别、锁的简要介绍,主要内容有:

1、四个不同事务隔离级别的区别;
2、InnoDB行锁案例演示;
3、InnoDB死锁案例演示;
4、在没有索引的列上锁定,会引发更大范围的锁。

百度云盘:http://t.cn/R73hP5i , 搜狐视频:http://t.cn/R73hP56 ,初学者们可以看看 :)