Search code examples
mysqlsqltransactionsmysql-workbenchrollback

Does the MySQL workbench show uncommitted changes?

Why does the MySQL workbench show uncommitted changes?

-- create a new empty table
DROP TABLE IF EXISTS X;
CREATE TABLE X (val varchar(10));

-- disable autocommit
SET AUTOCOMMIT = 0;

-- insert a row without committing
INSERT INTO X (val) VALUES ('text');

At this point

SELECT @@autocommit, @@tx_isolation;

returns

| 0 | REPEATABLE-READ |

However, a query shows the value which has not yet been committed:

SELECT * FROM X;

| text |

Why does MySQL return a result set with uncommitted data?

If I roll back the transaction using

ROLLBACK;

then MySQL returns an empty table when querying X. (That confirms that the transaction hasn't been committed automatically at some point of time.)

Solution

  • The reason is that I am always in the same transaction (regardless the window tab).