Update can't see records just committed by another connection 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 (&lt;redacted&gt;) VALUES (&lt;redacted&gt;)
2018-08-03T13:31:24.830493Z  1150 Execute   INSERT INTO account (&lt;redacted&gt;) VALUES (&lt;redacted&gt;)
2018-08-03T13:31:24.831345Z  1150 Close stmt
2018-08-03T13:31:24.833228Z  1150 Prepare   INSERT INTO refresh_token (&lt;redacted&gt;) VALUES (&lt;redacted&gt;)
2018-08-03T13:31:24.833666Z  1150 Execute   INSERT INTO refresh_token (&lt;redacted&gt;) VALUES (&lt;redacted&gt;)
2018-08-03T13:31:24.834356Z  1150 Close stmt
2018-08-03T13:31:24.834477Z  1150 Prepare   INSERT INTO another (&lt;redacted&gt;) VALUES (&lt;redacted&gt;)
2018-08-03T13:31:24.835155Z  1150 Execute   INSERT INTO another (&lt;redacted&gt;) VALUES (&lt;redacted&gt;)
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 = '&lt;redacted&gt;' WHERE token = '&lt;redacted&gt;'
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'
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.