English is currently available.
SQL DISCUSSION
Our component store has a stock table. The application reads the quantity, checks that it is greater than zero, and then writes back quantity - 1. Under load two requests sometimes read the same value, say 1, and both succeed, so one item is sold twice.
I wrapped the two statements in BEGIN and COMMIT, but it still happens occasionally. What does a transaction actually guarantee here, and what is the correct pattern?
A transaction guarantees atomicity: its statements are all committed or all rolled back. It does not by default stop another transaction from reading the same row between your SELECT and your UPDATE. At the READ COMMITTED isolation level, which is the default in PostgreSQL, SQL Server and Oracle, both sessions may read quantity 1, and both then write 0. That is the lost update.
The simplest fix removes the gap entirely by letting one statement do the check and the change: UPDATE stock SET quantity = quantity - 1 WHERE part_id = 7 AND quantity >= 1. Then look at the affected row count: 1 means the item was reserved, 0 means it was sold out.
When the logic between read and write is too complex for one statement, lock the row while reading. In PostgreSQL, MySQL (InnoDB) and Oracle: SELECT quantity FROM stock WHERE part_id = 7 FOR UPDATE inside the transaction. A second session running the same statement waits until the first commits or rolls back, and then sees the new value. SQL Server expresses the same idea with a table hint, WITH (UPDLOCK, ROWLOCK).
Keep such a transaction short: no user interaction and no slow network calls while the lock is held, because every other request for that row is waiting.
Isolation levels define which anomalies the database prevents for you. READ UNCOMMITTED can see uncommitted changes (dirty reads). READ COMMITTED sees only committed data, but two reads in one transaction can return different values. REPEATABLE READ keeps the rows you have read stable. SERIALIZABLE behaves as if transactions ran one after another.
Raising the level is a valid fix, but at the stricter levels the database may abort one of the conflicting transactions with a serialization or deadlock error, and the application must be prepared to retry. Behaviour also differs between products at the same nominal level (MySQL's InnoDB defaults to REPEATABLE READ, for instance), so read your database's documentation rather than assuming.
An alternative that needs no long-held locks is optimistic concurrency. Add a version column, read it with the data, and make the write conditional: UPDATE stock SET quantity = 0, version = 13 WHERE part_id = 7 AND version = 12. If someone else changed the row first, the version no longer matches, zero rows are affected, and you reload and retry or report the conflict. This suits web applications where a user looks at a form for minutes before saving.
Two supporting measures. Add a constraint such as CHECK (quantity >= 0) so that a logic error anywhere fails loudly instead of storing negative stock. And when one transaction touches several rows, always lock them in the same order, for example by ascending part_id. If one session locks part 7 then 9 while another locks 9 then 7, each waits for the other; the database detects the deadlock and cancels one of them, so handle that error with a retry.
You can reproduce the original bug on a test database by opening two sessions and stepping through the statements by hand, which is a good way to confirm the fix.
