Start With Single Statements

While only one thing changes a row at a time, a single statement with autocommit is correct, and you don't need to think about isolation. That stops being true as soon as two requests can read and change the same row at the same moment, which is the situation below.

Take a typical case, made up for illustration. A flash sale on 50 units ends with 63 orders. Nobody wrote a bug you can point to: the code checked the stock, then decremented it. Two requests just did that at the same moment, and both saw one unit left.

Transactions are the database’s promise that a group of changes happens all together or not at all, and that concurrent work does not trample itself. How strong that second promise is depends on the isolation level, and most teams never look past the default. Below is what each level really guarantees, and the patterns that keep concurrent writes correct.

If concurrency has bitten you
  • You read a counter, added 1, and wrote it back — and lost updates under load
  • You don't know the difference between READ COMMITTED and SERIALIZABLE
  • You've hit a deadlock at 2am and just retried until it worked
  • You don't know what "write skew" is