DB isolation levels I ran into in production

After moving from an Oracle-based system to MySQL and building a new payment system, I used a DB lock inside a transaction to prevent concurrent requests from the same user from updating the balance simultaneously. The update was designed with a balance = originalBalance condition, so it would only be applied when the balance matched the value before payment.

The problem started after I added logic to first check whether a balance existed via readUserMethod(userId) before acquiring the lock, and insert one if it didn't. When request A and request B came in concurrently, even after A acquired the lock first, finished the payment, and committed, B — after waiting and then entering — kept using the value it had read initially, not the latest balance that A had updated.

The cause was REPEATABLE READ, MySQL's default isolation level. Within a single transaction, a value read once stays the same even if read again later, so the result of a query made before the lock stays fixed for the rest of the transaction. In contrast, the previous system's Oracle defaults to READ COMMITTED, so it could re-read values reflecting intermediate commits, and this same problem never surfaced.

There were three solutions:

• Acquire the lock first, then perform the balance lookup and insert, so that only committed data is seen
• Change just that transaction to @Transactional(isolation = Isolation.READ_COMMITTED)
• Adjust the isolation level for the entire DB to READ COMMITTED

That said, REPEATABLE READ isn't inherently bad. For example, in a situation where a product's price changes mid-payment, READ COMMITTED — which re-reads the updated value in the middle — could actually increase payment failures. The key point is not which isolation level is superior, but choosing one that fits the service's policy and concurrency requirements.

Also, since default isolation levels and supported ranges differ across RDBMSs, this difference must always be checked when switching databases or touching framework settings.