MySQL Workbench and default session isolation level

4
votes

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.

1
mysqlsessionworkbench
There's a verified bug on this. No idea what the status of it though. Related: stackoverflow.com/questions/20395889/…, stackoverflow.com/questions/26208007/… – Sithsu

1 Answers

2
votes

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