SET tx_isolation与SET TRANSACTION ISOLATION LEVEL [英] SET tx_isolation versus SET TRANSACTION ISOLATION LEVEL
问题描述
在MySQL(尤其是5.5)中,似乎我们有两种不同的方法来设置事务隔离级别.我只是想知道我是否正确认为
In MySQL (notably 5.5), it seems that we have two different methods to set the transaction isolation level. I just would like to know if I am right in thinking that
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
与
SET tx_isolation = 'REPEATABLE-READ'
,对于其他可能的隔离级别也是如此.
and that this is true for the other possible isolation levels as well.
编辑1
我不够精确.鉴于我的问题,@ danihp的答案是正确的.但实际上,我打算问一下
I have not been precise enough. @danihp's answer is correct given my question. But actually, I intended to ask about
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
与
SET SESSION tx_isolation = 'REPEATABLE-READ'
(请注意SET SESSION
而不是SET
).
它们完全一样吗?
推荐答案
服务器系统变量:
您可以使用SET tx_isolation = 'REPEATABLE-READ'
设置服务器系统变量的默认隔离级别. "MySQL服务器维护着许多指示其配置方式的系统变量." 此变量设置默认的事务级别.
You can use SET tx_isolation = 'REPEATABLE-READ'
to set default isolation level on server system variables. "The MySQL server maintains many system variables that indicate how it is configured" This variable sets the default transaction level.
当前交易:
您可以使用SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
将当前交易上的隔离级别从默认级别更改为新级别.
You can use SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
to change isolation level from default to new level on your current transaction.
通知:
"transaction_isolation
作为tx_isolation
的别名,现已弃用,并在MySQL 8.0中删除了它.应将应用程序调整为优先使用transaction_isolation
代替
"transaction_isolation
was added in MySQL 5.7.20 as an alias for tx_isolation
, which is now deprecated and is removed in MySQL 8.0. Applications should be adjusted to use transaction_isolation
in preference to tx_isolation
."
参考:
- 5.1.5 Server System Variables sysvar_tx_isolation
- 5.1.5 Server System Variables sysvar_transaction_isolation
- 13.3.6 SET TRANSACTION Syntax
已编辑
您可以使用交易来封装"多个语句作为一个单一的操作.当您开始交易时,您此时可以更改此新事务的隔离级别:
You can use transactions to "encapsulate" several statements as a single operation. When you start a transaction, just at this time, you can change isolation level for this new transaction:
START TRANSACTION;
/** change isolation level here with SET TRANSACTION statement
to avoid default isolation level **/
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;
这篇关于SET tx_isolation与SET TRANSACTION ISOLATION LEVEL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!