文章目录
1. session和global区别
说明:MySQL的session和global一般使用在终端,用来对配置进行暂时设置,当数据库服务重启就会失效
。session和global体现在新的设置生效的范围。
session:当前会话,也就是当前连接立即生效。
global:全局,不包含当前连接
,之后新获取的连接都会生效。
因此修改事务隔离级别设置时,需要保持打开的窗口一致,每个窗口都要执行一下修改语句(针对session立即生效,如果是global的话,需要重新打开一个窗口)
2. MySQL 5.7.0官网语法介绍
注意:5.7版本细分版本可能也不一致
语法模板,对比发现,这种形式的和8.0的是通用模板:
SET [GLOBAL | SESSION] TRANSACTION
transaction_characteristic [, transaction_characteristic] ...
transaction_characteristic: {
ISOLATION LEVEL level '小写的level是占位符'
| access_mode 'access_mode 也是占位符 '
}
level: { '小写的level枚举项'
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE
}
access_mode: {
READ WRITE
| READ ONLY
}
2.1 Transaction Characteristic Scope(事务的范围)
下面介绍官网的解释,基本上是session和GLOBAL 的区别
You can set transaction characteristics globally, for the current session, or for the next transaction only:
-
With the GLOBAL keyword(带GLOBAL 标识):
-
The statement applies globally for all subsequent sessions.
对所有的后来的session(可以理解成连接,一个session对应一个连接
)生效 -
Existing sessions are unaffected.
当前session不生效
-
-
With the SESSION keyword(带SESSION 标识):
-
The statement applies to all subsequent transactions performed within the current session.
对当前session中的后面的所有事务生效 -
The statement is permitted within transactions, but does not affect the current ongoing transaction.
该语句在事务中是允许的,但不会影响当前正在进行的事务。倘若我的隔离级别是a,通过set SESSION transaction xxxx,想设置成b,可以在已开启的事务中执行,begin语句后调用,但是当前事务隔离级别不变,从下一个新的事务开始生效。
-
If executed between transactions, the statement overrides any preceding statement that sets the next-transaction value of the named characteristics.
如果在事务之间执行,则该语句将覆盖设置命名特征的next-transaction值的任何在前语句。(不清楚什么意思)
-
-
Without any SESSION or GLOBAL keyword(无GLOBAL 和SESSION 修饰):
-
The statement applies only to the next single transaction performed within the session.
仅对下一个事务生效; -
Subsequent transactions revert to using the session value of the named characteristics.
下一个事务提交后,会恢复为session的事务级别 -
The statement is not permitted within transactions:
该语句在事务中是不允许的。(不能在begin后面加该语句,只能在commit后加)mysql> START TRANSACTION; `//开启一个事务` Query OK, 0 rows affected (0.02 sec) mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; `//执行报错` ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress
-
2.2 2种语法
具体的语法存在2种
2.2.1 Table 13.6 SET TRANSACTION Syntax for Transaction Characteristics
Syntax | Affected Characteristic Scope |
---|---|
SET GLOBAL TRANSACTION transaction_characteristic | Global |
SET SESSION TRANSACTION transaction_characteristic | Session |
SET TRANSACTION transaction_characteristic | Next transaction only (仅对下一次开启的连接生效) |
transaction_characteristic可选项,没有引号,中间是空格:
REPEATABLE READ
READ COMMITTED
READ UNCOMMITTED
SERIALIZABLE
例:
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
2.2.2 Table 13.7 SET Syntax for Transaction Characteristics
Syntax | Affected Characteristic Scope |
---|---|
SET GLOBAL var_name = value | Global |
SET @@GLOBAL.var_name = value | Global |
SET SESSION var_name = value | Session |
SET @@SESSION.var_name = value | Session |
SET var_name = value | Session 这个比较特殊,不带session和 GLOBAL 时,表示session |
SET @@var_name = value | Next transaction only |
不带@@符号且不带session和 GLOBAL 标识时,表示session,建议都加上@@符号,这样更精确,避免歧义
var_name 指transaction_isolation
( 或写为tx_isolation
,表示缩写)
value可选项,注意是字符串,必须带引号,并且中间有连接符:
'READ-COMMITTED'
'READ-UNCOMMITTED'
'REPEATABLE-READ'
'SERIALIZABLE'
旧版本(包括5.7.0)中tx_isolation
是作为transaction_isolation
的别名被应用的,新版本已经弃用了,所以在MYSQL8.0输入会显示未知变量。
Prior to MySQL
5.7.20
, usetx_isolation
andtx_read_only
rather thantransaction_isolation
andtransaction_read_only
.
5.7.0查询语法:
SELECT @@tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@global.tx_isolation;
5.7.0修改语法例子:
set @@global.tx_isolation = 'READ-UNCOMMITTED'; //全局
set @@session.tx_isolation = 'READ-UNCOMMITTED'; //当前session
set @@tx_isolation = 'READ-UNCOMMITTED'; //仅对下一个事务生效
3. MySQL 8.0
8.0 查询语法和5.7语法基本一致,只是对于 @@形式的语法,废弃了tx_isolation
,只能用标准的transaction_isolation
参考:
《数据库查询事务隔离级别时,报错误代码:Unknown system variable tx_isolation解决办法》