The coupon system looked fine in every test we ran. Create a coupon, set a max usage limit, redeem it. Works perfectly. Then we launched a flash sale, put a 50%-off coupon in a marketing email, and sent it to 80,000 people at once. By the time we noticed, the coupon had been redeemed 1,400 times. The limit was 500.
Every redemption was wrapped in a transaction. We checked before writing. The code was correct. The transaction was there. None of it helped.
What Transactions Actually Do
Transactions give you four properties, usually summarized as ACID: atomicity, consistency, isolation, durability. The one people misunderstand is isolation.
Isolation means that a transaction's intermediate states aren't visible to other transactions. If you update three rows in a transaction, another transaction won't see two of the three committed and one not. It's all or nothing as far as visibility goes. That's useful. It's not the same as saying concurrent transactions can't interfere with each other's logic.
Confusion comes from a mental model where a transaction is like a lock on the whole operation. You start a transaction, you own the data, nobody else can touch it. That's not how it works. Transactions don't lock anything by default. They just group your reads and writes into an atomic unit. Other transactions are still running at the same time, reading the same rows, making the same decisions.
The Race Window
Here's the pattern that breaks. You want to decrement inventory before completing an order:
BEGIN;
SELECT quantity FROM inventory WHERE product_id = 42;
-- quantity is 1, looks good
-- application code: if quantity > 0, proceed
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;
COMMIT;Two requests hit this code at the same millisecond. Both start a transaction. Both run the SELECT. Both see quantity = 1. Both check: is 1 greater than 0? Yes. Both run the UPDATE. Both commit. Quantity is now -1.
Neither transaction did anything wrong in isolation. The SELECT returned accurate data at the time it ran. The UPDATE was valid SQL. Both transactions committed cleanly. But between the read and the write, another transaction read the same value and made the same decision. The transaction boundary didn't help because the race happens inside it.
This is called a lost update. One transaction's write overwrites another's without knowing it happened. One of the most common concurrency bugs in backend systems, and almost invisible in single-threaded tests.
Isolation Levels Don't Save You Either
Most databases default to READ COMMITTED isolation. That means your transaction only sees data that has already been committed by other transactions. It won't read dirty, in-progress writes. That's the protection you get out of the box.
What READ COMMITTED doesn't protect against: reading a value, making a decision based on it, and having another transaction change that value before you act on it. The SELECT you just ran was accurate. By the time your UPDATE runs, the world has changed. You're acting on stale information.
Bumping to REPEATABLE READ helps in a narrower way. Your transaction will see the same data if it reads the same rows twice. But that doesn't stop two transactions from both reading the current value, both deciding it's valid, and both writing conflicting updates. They just both get a consistent view of the same outdated state.
SERIALIZABLE is the strongest option. It forces transactions to execute as if they ran one at a time, in some serial order. If two transactions would produce different results depending on their execution order, the database detects that and aborts one of them. That actually prevents this class of bug. But almost no production application runs at SERIALIZABLE isolation. The performance cost is real, the retry logic it requires is non-trivial, and most teams never think to change the default.
The Fixes That Actually Work
There are three approaches that reliably solve this, and they work at different layers.
SELECT FOR UPDATE. This is the most direct fix. When you read a row with the intent to update it, tell the database that upfront:
BEGIN;
SELECT quantity FROM inventory WHERE product_id = 42 FOR UPDATE;
-- now this row is locked until COMMIT or ROLLBACK
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;
COMMIT;The FOR UPDATE clause acquires a row-level lock at read time. Any other transaction that tries to select the same row with FOR UPDATE will block until the first transaction completes. The second transaction then reads the updated value and makes its decision with accurate data.
The cost is that you're serializing access to that row. Under high contention, this creates a queue. For most use cases that's fine. For genuinely high-throughput scenarios it can become a bottleneck.
Atomic UPDATE with a condition. For simpler cases, you can skip the SELECT entirely and push the check into the UPDATE:
UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 42
AND quantity > 0;The database evaluates the condition and the update atomically. Either the row matches and gets updated, or it doesn't. Two concurrent requests running this statement will not both decrement a quantity of 1 to -1, because each UPDATE is an atomic read-modify-write at the database level. The second one will find quantity = 0 and update zero rows.
Your application code needs to check the affected row count. If it's zero, the item was out of stock and you handle it accordingly. This is often the cleanest solution when the logic is simple enough to express in a single UPDATE.
Optimistic locking. Add a version column to the table. When you update, include the version you read in the WHERE clause and increment it:
-- Read
SELECT quantity, version FROM inventory WHERE product_id = 42;
-- Got quantity=1, version=7
-- Write
UPDATE inventory
SET quantity = 0, version = 8
WHERE product_id = 42 AND version = 7;If another transaction updated the row between your SELECT and your UPDATE, the version will have changed. Your UPDATE matches zero rows. You detect this, reload the current state, and retry the operation.
Optimistic locking works well when conflicts are rare. Under high contention it generates a lot of retries, which can amplify load. Most JPA/Hibernate setups support this out of the box with @Version, so the mechanics are handled for you.
Constraints as a Last Line of Defense
None of these solutions removes the value of adding a database constraint:
ALTER TABLE inventory ADD CONSTRAINT quantity_non_negative CHECK (quantity >= 0);A CHECK constraint won't prevent the race condition. It won't make your logic correct. But it will prevent the database from persisting a logically impossible state. If your application-level protection fails for any reason, the constraint catches the result. The transaction rolls back. You get an exception instead of corrupted data.
Constraints are not a substitute for correct concurrency handling. They're a backstop. A -1 inventory result should fail loudly instead of quietly persisting. Defense in depth.
Why the Test Always Passes
These bugs survive code review and pass all tests because they require genuine concurrency to reproduce. In a test suite, requests run sequentially. In a local environment, load is low. The race window is there, but the odds of hitting it are near zero.
You find these bugs under real traffic, usually during the worst possible moment: a product launch, a flash sale, a viral moment. Two users hit the same endpoint within milliseconds of each other and both slip through the same logic gap.
The mental model to build is this: a transaction guarantees atomicity and protects you from dirty reads. It does not guarantee that the data you read at the start of your transaction is still the current state of the world when you act on it. If your logic is read, check, write, the check and the write are not atomic unless you make them atomic explicitly.
Transactions are an important tool. They solve real problems. But wrapping code in BEGIN and COMMIT is not the same as making it safe for concurrent access. The race condition doesn't disappear because a transaction is present. It just hides better.
Comments (0)