MySQL Workbench和默认会话隔离级别 [英] MySQL Workbench and default session isolation level

问题描述

我在使用MySQL Workbench 6.0 CE时遇到问题，我将尽最大可能对其进行描述:

MySQL Workbench始终将会话变量@@tx_isolation设置为"REPEATABLE READ"，更改此变量的唯一方法是使用SET tx_isolation='READ-COMMITTED';.

我想要的是，当我启动工作台时，tx_isolation的默认会话变量是'READ-COMMITTED'而不是'REPEATABLE-READ'；是的，我已经更改了全局变量tx_isolation，它是'READ-COMMITTED'，但会话1没有.

Ej:

SELECT @@Global.tx_isolation, @@tx_isolation;

分别返回:'READ-COMMITTED'，'REPEATABLE-READ'.

注意::如果我在MySQL命令行中查询与上面相同的代码，则两个变量都设置为'READ-COMMITTED'，这就是为什么我认为这是MySQL Workbench而不是服务器的问题. /p>

感谢您的帮助.

解决方案

这是一个老问题，但我仍然遇到相同的错误.

根据doc( https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html#isolevel_repeatable-read )，默认隔离级别为REPEATABLE-READ.

这意味着在第一次读取事务时会创建数据库快照.每次对该事务的其他读取都会向您显示快照的数据.

因此，您需要结束事务(提交或回滚)才能在下次读取时获得新的快照.

在AutoCommit上设置MySQL Workbench的同事看不到可重复读取的行为.我们发现这是因为在每次执行SELECT之后，事务都会关闭，并且会创建一个新的快照.

因此，由于该错误仍未得到纠正(如Sithsu所述)，因此一种解决方法是:

• 切换到自动提交以自动创建新快照
• 或在每次SELECT之后提交/回滚以创建新快照

I have a problem with MySQL workbench 6.0 CE, I will describe it the most explained possible:

MySQL Workbench always set my session variable @@tx_isolation to "REPEATABLE READ" and the only way to change this variable is using SET tx_isolation='READ-COMMITTED';.

What I want is that when I launch workbench the default session variable for tx_isolation is 'READ-COMMITTED' and not 'REPEATABLE-READ'; yes, I've changed the global variable tx_isolation and it's 'READ-COMMITTED' but session one is not.

Ej:

SELECT @@Global.tx_isolation, @@tx_isolation;

returns: 'READ-COMMITTED', 'REPEATABLE-READ' respectively.

Note: If I query the same code as above in MySQL command line, both variables are set to 'READ-COMMITTED', that's why I think it's a problem with MySQL Workbench and not the server.

Thanks for the help.

解决方案

This is an old question, but still I have the same bug.

According to doc (https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html#isolevel_repeatable-read), Default Isolation Level is REPEATABLE-READ.

This mean that a snapshot of the database is made on the FIRST read of the transaction. Every other read of this transaction will show you the data of the snapshot.

So you need to end the transaction (commit or roll-back) to get a new snapshot on the next read.

My colleagues who set MySQL Workbench on AutoCommit don't see the repeatable-read behaviour. We figured out it's because after each SELECT, the transaction is closed and a new snapshot is created.

So, as the bug is still not corrected (as Sithsu mentionned), a workaround would be :

• switch to autocommit for new snapshots to be automatically created
• or commit/rollback after each SELECT to create a new snapshot

这篇关于MySQL Workbench和默认会话隔离级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！