MySQL Transaction – Update Can’t See Records Committed by Another Connection

MySQLtransaction

I am performing a transaction using a connection. Whenever a second connection immediately tries to update the rows just inserted, the rows are not found. Even if the transaction was committed before issuing an UPDATE.

I have the mysql query log which describes the scenario.

The last statement fails with 0 rows affected (UPDATE refresh_token...) :

2018-08-03T13:31:24.829038Z  1150 Query START TRANSACTION
2018-08-03T13:31:24.830026Z  1150 Prepare   INSERT INTO account (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.830493Z  1150 Execute   INSERT INTO account (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.831345Z  1150 Close stmt
2018-08-03T13:31:24.833228Z  1150 Prepare   INSERT INTO refresh_token (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.833666Z  1150 Execute   INSERT INTO refresh_token (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.834356Z  1150 Close stmt
2018-08-03T13:31:24.834477Z  1150 Prepare   INSERT INTO another (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.835155Z  1150 Execute   INSERT INTO another (<redacted>) VALUES (<redacted>)
2018-08-03T13:31:24.835621Z  1150 Close stmt
2018-08-03T13:31:24.835747Z  1150 Query COMMIT
2018-08-03T13:31:24.840374Z  1150 Prepare   UPDATE refresh_token SET accessed = ? WHERE token = ?
2018-08-03T13:31:24.840799Z  1150 Execute   UPDATE refresh_token SET accessed = '<redacted>' WHERE token = '<redacted>'
2018-08-03T13:31:24.843346Z  1150 Close stmt

If I wait 500ms before issuing the UPDATE refresh_token SET..., the record is found.

Here is the transaction isolation level:

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
'REPEATABLE-READ', 'REPEATABLE-READ'

Best Answer

Without knowing more (like the query execution plan, number of rows affected, indexing, etc.) it will be hard to provide a concise answer. But REPEATABLE_READ raises a flag, at least for me.

Transaction isolation levels are a matter of give-and-take. None of them are "the best"; rather, you choose one that's a "best fit". Each one comes with a cost. If you want high consistency, you get less concurrency.

I like this post: https://www.percona.com/blog/2012/08/28/differences-between-read-committed-and-repeatable-read-transaction-isolation-levels/

This excerpt from the post sums it up nicely:

In REPEATABLE READ every lock acquired during a transaction is held for the duration of the transaction.

In READ COMMITTED the locks that did not match the scan are released after the STATEMENT completes.

Related Solutions

MySQL: Transaction and Deadlock

If you are totally sure about the separation status of your data, you can set the global isolation level to READ COMMITTED or even to READ UNCOMMITTED. The first blocks less from the index, which should be enough in your case. The second one arranges for all SELECT statements to be non blocking. This is as low as you can go.

Whatever you choose, you should set the global isolation level back to REPEATABLE READ as soon as you finished inserting your data.

global means that this applies to all connections, which are established. So this change also affects connections beside your jarfiles. If you have the possibility to change the jarfiles, you should set the connection based isolation level with the SESSION keyword instead in both jarfiles.

MySQL Commited data not seen to select query

I try to make an hypothesis about the second question:

Given the commit happens before the select (commit is at 14:16:06 and select is at 14:16:07) how is it that the select does not return the row inserted by the transaction?

Transactions are managed by Spring. So it would be possible that before running the select spring has raised a start transaction or it has already used the connection for running an another query.

I start a first session where I simulate an insert into a table t:

session1> create table t (i int auto_increment primary key);
Query OK, 0 rows affected (0,32 sec)

session1> insert into t values();
Query OK, 1 row affected (0,00 sec)

session1> select * from t;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0,00 sec)

session1> start transaction;
Query OK, 0 rows affected (0,00 sec)

session1> insert into t values();
Query OK, 1 row affected (0,00 sec)

I create a new session, session2, where autocommit is set to 0. Into this new session, a transaction is implicitly started when running a select.

session2> set autocommit = 0;
Query OK, 0 rows affected (0,00 sec)

session2> select * from t;  -- this starts a transaction
+---+
| i |
+---+
| 1 |
+---+
1 rows in set (0,00 sec)

Move to session1 to commit the insert.

session1> commit;

Now move again to session2:

session2> select * from t;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0,00 sec)

Session2 cannot see the row just inserted. If a commit is raised in session2 we can see new row inserted in session1

session2> commit
1 row in set (0,00 sec)

session2> select * from t;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0,00 sec)

The general log looks like :

150804 14:04:10     2 Query select * from t

150804 14:04:30     1 Query start transaction
150804 14:04:39     1 Query insert into t values ()
150804 14:04:44     1 Query commit
150804 14:04:51     2 Query select * from t

150804 14:05:07     2 Query commit
150804 14:05:10     2 Query select * from t

The first row is related to session 2. It is when session 2 opens the transaction.

I don't know if this is what happen in your case. You could check in your general log if the connection_id 36 was used for other queries. Let we know.

Best Answer

Related Solutions

MySQL: Transaction and Deadlock

MySQL Commited data not seen to select query

Related Question