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屋！
