Written by: ilmarkerm

Category: Blog entry

Published: June 20, 2012

Back-story: A developer came to me and wanted explanation for a weird behavior in MySQL. They inserted a record (to InnoDB table), committed, and after receiving a message (on another application) tried to read that inserted record immediately, but the newly inserted record was not found. Problem only happened in production, but not always (quite frequently).

After comparing the MySQL parameter files between production and development environments I discovered that in production autocommit was disabled to make MySQL behave more like Oracle. This setting was removed from development after we rebuilt the environment (to use multiple MySQL instances with Oracle Clusterware, instead of one large MySQL instance), but the rebuild was not yet done in production.

The default transaction level for MySQL InnoDB is REPEATABLE READ (unlike Oracle, that has READ COMMITTED as default), that means that the SELECT query always returns the data at the time point when the transaction was started. If autocommit is off, then the first issued select statement will open the transaction and any subsequent select statement will return the data at the time point when the first select was issued, until transaction is ended with COMMIT/ROLLBACK. If autocommit is enabled, SELECT statement is run in a self-contained transaction, ending with COMMIT, so the end result is like READ COMMITTED isolation level in Oracle.

Here is an example what you’d expect to see as a result:

mysql session 1$ create table test (id integer unsigned primary key) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql session 1$ set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

mysql session 1$ select * from test;
Empty set (0.01 sec)

  mysql session 2$ begin;
  Query OK, 0 rows affected (0.00 sec)

  mysql session 2$ insert into test values (1);
  Query OK, 1 row affected (0.05 sec)

  mysql session 2$ commit;
  Query OK, 0 rows affected (0.00 sec)

mysql session 1$ select * from test;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

The same example of having autocommit off and transaction isolation level set as default:

mysql session 1$ set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql session 1$ select * from test;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

  mysql session 2$ begin;
  Query OK, 0 rows affected (0.00 sec)

  mysql session 2$ insert into test values (2);
  Query OK, 1 row affected (0.05 sec)

  mysql session 2$ commit;
  Query OK, 0 rows affected (0.00 sec)

mysql session 1$ select * from test;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql session 1$ commit;
Query OK, 0 rows affected (0.00 sec)

mysql session 1$ select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

Now, autocommit off and transaction isolation level set to READ COMMITTED:

mysql session 1$ set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql session 1$ set session transaction isolation level read committed;
Query OK, 0 rows affected (0.03 sec)

mysql session 1$ select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.01 sec)

  mysql session 2$ begin;
  Query OK, 0 rows affected (0.00 sec)

  mysql session 2$ insert into test values (3);
  Query OK, 1 row affected (0.05 sec)

  mysql session 2$ commit;
  Query OK, 0 rows affected (0.00 sec)

mysql session 1$ select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

You can read more about how autocommit is handled by InnoDB in documentation.

Another key component in our puzzle – application server used connection pools for managing MySQL connections, so after Java session requested a connection from pool, it was only a matter of chance whether the connection already had a transaction left open by the previous user of that connection, did the previous user commit, or was it a brand new connection.