MySQL defaults to REPEATABLE-READ transactions. This means that repeated SELECT statements within the same transaction return the same data (i.e., they don’t pick up interim updates made outside the transaction). Specifically, reads take place from a snapshot of the database created for the first read in the transaction.
This may lead to unexpected results when combined with exclusive locks (SELECT … FOR UPDATE).
Consider two transactions, A and B, which run concurrently and each acquire an exclusive lock on a record. One of them wins, let’s say A. So A carries out its updates while B waits. Now A completes, and B becomes unblocked. One might reasonably assume that B will have access to the latest state of the database, namely the changes made by A. But not with REPEATABLE-READ.
Under REPEATABLE-READ, B’s view of the database is as of the first read of the database within B, which was quite possibly before the lock was obtained. So, B’s view of the database may not include the changes made by A.
Let’s consider an example: A and B both deduct $100 from a bank account initially containing $500. What’s the proper result? $300 of course. But under REPEATABLE-READ, you could easily wind up with a result of $400. Yikes.
The solution is to use READ-COMMITTED transaction isolation (that’s transaction-isolation = READ-COMMITTED in my.cnf).
Under READ-COMMITTED (which most industrial-strength databases use as the default), B’s view of the database will, by definition, include the changes made by A. And, because we’re using an exclusive lock, B is isolated from other transactions.