This is hard for me to remember. I feel like START TRANSACTION should be enough to prevent anything going wrong while that transaction is active for my session. Whatever value I read/select should not change until after I commit. I sometimes think wrapping it in a transaction is a panacea for race conditions.

However, during a transaction, at least in MySQL, any reads aren't locked. Just a write (concerned with UPDATE mostly, but probably DELETE too.).

So, UserA makes a request milliseconds before UserB. UserA goes into the transaction, makes the un-locked select query, then some calculations (probably in the app code) decides what to do. Update? Delete? If Update, to what value, etc?

During that decision-making code, UserB's request comes in, makes the same un-locked select and goes into decision-making code. But then UserA's code updates and commits the transaction. Because only the Update was locked (this is how InnoDB handles it), UserB's request can also make the update. UserB was pre-update when UserA's request got to the lock-update-unlock steps.

Using transactions doesn't necessarily solve a race condition, but it seems like it should, and I think a lot of people, me included, forget that you need to lock that select (using FOR UPDATE).