5

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.

Barranka
  • 20,547
  • 13
  • 65
  • 83
Eladio Mora
  • 159
  • 2
  • 6
  • 2
    There's a [verified bug](http://bugs.mysql.com/bug.php?id=69800) on this. No idea what the status of it though. Related: http://stackoverflow.com/questions/20395889/mysql-repeatable-read-workbench-transaction-level-not-set, http://stackoverflow.com/questions/26208007/mysql-workbench-session-does-not-see-updates-to-the-database – Sithsu Oct 07 '14 at 14:25

1 Answers1

2

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
Nico
  • 81
  • 1
  • 8
  • thanks @Nico.. even this is an old answer, It saves my time to troubleshoot the behaviour of mysql workbench works related to auto commit enable/disabled. – Yosep G Oct 06 '21 at 04:19