| Bug #69661 | Without autocommit turned on, SELECT statements don't refresh | ||
|---|---|---|---|
| Submitted: | 3 Jul 2013 13:52 | Modified: | 3 Jul 2013 15:15 |
| Reporter: | Eric Spencer | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Workbench: SQL Editor | Severity: | S2 (Serious) |
| Version: | 5.2.47 CE | OS: | Windows (XP SP3) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | autocommit, refresh | ||
[3 Jul 2013 15:15]
Todd Farmer
Thank you for your bug report! However, this is expected server-side behavior (not specific to MySQL Workbench) with the default transaction isolation of REPEATABLE READ. Please see the following page for details on how transaction isolation levels work, and the impacts of REPEATABLE READ: http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html
Description: With autocommit mode turned off (the icon in the toolbar of the SQL window), the SELECT statement will return the same results over and over even though in another session rows are being changed and committed. It's like it is caching the results or something and the cache is stale until a commit occurs. It does appear that a commit ('commit;' statement) in the same session will cause the results to refresh again. But then they get "stuck" again. This is bad because autocommit is generally considered bad in corporate environments. But, with the only way to make sure select results are accurate is to have it turned on. That makes it even worse. How to repeat: 1. create a table like this: create table test.sqleditor_refresh ( avalue VARCHAR(10) NOT NULL ); insert into test.sqleditor_refresh VALUES ('1'),('1'),('1'),('2'),('2'),('2'); commit; 2. In that same session turn off autocommit mode and do: SELECT * FROM test.sqleditor_refresh; 3. In another session (make sure it's a different connection) do: update test.sqleditor_refresh SET avalue = '3' where avalue = '2'; commit; 4. Re-issue query in step #2. 5. The values don't change. 6. Now turn on autocommit mode in the session from step #2 and #4 and re issue query. 7. The values have change to what is expected. The only difference being auto commit mode for a SELECT.