Two Transactions at Once
The database handles many clients at the same time and can run their transactions concurrently. That makes the database more efficient, because it processes more work in the same time. It also makes it more complicated. Things are fine as long as transactions touch different rows. The question is what happens when they touch the same row. For example, what happens when a student buys lunch while their parent is reloading their account? The two transactions are running at the same time, and they both read and write the same balance row.
A purchase and a reload
Suppose the student has $5.00 on their account and taps at the register for a $3.00 lunch. At the same moment, the parent reloads $10.00 from the app. Here is what could happen if the two transactions simply read and wrote the row:
- The register reads the balance: $5.00. It computes the new balance: $5.00 - $3.00 = $2.00. It then continues to write the new balance and insert a ledger row for the purchase.
- Before any of this is written, the app (on the parent’s phone) reads the balance: $5.00 (no updates yet). It computes the new balance: $5.00 + $10.00 = $15.00. It then continues to write the new balance and insert a ledger row for the reload.
Now both transactions go to write. Depending on which one reaches the row first, the final balance will be either $2.00 or $15.00. If the register writes first, the app writes next and puts $15.00 over the register’s $2.00. The student ate a lunch they were not charged for. If the app’s write reaches the row first, the purchase writes $2.00 over the reload’s $15.00, and the parent’s card was charged $10.00 for nothing. This is called a lost update: one transaction’s work is lost because another transaction wrote over it.
This is not what happens.
The first thing you should know is that the database puts a lock on the row when a transaction writes to it. So two transactions cannot write to the same row at the same time. This alone will not prevent the lost update, because the transactions may read the same row before writing. This is the lost update we are facing: the register and the app both read $5.00 before either writes, so they both compute a new balance based on $5.00.
We can solve this by making the transactions lock the row when they read it. In fact, I wrote the transaction to lock the row when it reads it:
SELECT balance_cents FROM accounts WHERE account_id = 1 FOR UPDATE;
The FOR UPDATE clause tells the database to hold a lock on the row until the transaction commits.
The register’s transaction locks the balance row when it reads $5.00. The database makes the app wait until the register finishes. The register writes $2.00 and a ledger row, and then the app reads $2.00, computes $2.00 + $10.00 = $12.00, and writes $12.00 and a ledger row. If the app had locked the row first, the register would have waited and read $15.00, and the balance would still end at $12.00.
So the database may run the two transactions at the same time, but it does not let them touch the same row at the same time. This is the promise called isolation: a transaction behaves as if it were the only one running, even when others are running at the same time on the same rows. Whatever outcome you get from running several transactions at once, running them one at a time could have given you that same outcome.