Negative stock and lost updates in a Postgres transaction

Stock goes negative even though the code is wrapped in a transaction. Or a stocktake finds less physical stock than the system shows. Nothing appears in the error log. Tests that process one order at a time in sequence never show the symptom.

I measured two patterns that cause this symptom under PostgreSQL 16's default isolation level (Read Committed).¹ The symptom shows up differently in each pattern, and the fix that works is different too.

A transaction guarantees it does not stop halfway

Wrapping statements in BEGIN and COMMIT guarantees that either all of them take effect or none of them do. Even if the server crashes partway through, you do not end up with a partial result.

BEGIN;
UPDATE items SET stock = stock - 1 WHERE id = 1;
INSERT INTO orders (item_id, worker) VALUES (1, 42);
COMMIT;
-- if it crashes before COMMIT, neither items nor orders is changed

Whether the transaction stays separate from other transactions is decided by a different setting. That setting is the isolation level, and at the default level, changes from other transactions can leak in.

What happens under concurrent execution

UPDATE takes a write lock (a row lock) on the target row, even if you do not ask for one explicitly. The lock is not released until commit or rollback.

When another transaction tries to UPDATE the same row, it waits until the preceding UPDATE commits or rolls back.

Once the wait ends, the later UPDATE sees the latest committed value at that point.² How the UPDATE is written decides what happens next.

Test setup

Environment

I measured on PostgreSQL 16 at Read Committed, in a single environment on GitHub Actions CI. One PostgreSQL 16 instance has two tables, items (stock) and orders. items has one row, and all 50 concurrent clients update that same row.

Conditions

Multiple clients run a "check the stock, then place an order" operation at the same time. I measured two patterns that differ only in how the UPDATE is written.

When the app reads a value with SELECT, processes it on the app side (validation, external API calls, business logic calculations, and so on), and then runs UPDATE, another transaction can SELECT the same row during that processing. Several transactions end up reading the same value and going on to UPDATE with it. The conditions are 50 concurrent clients, an initial stock of 20, and a 0.1s sleep between SELECT and UPDATE. The sleep stands in for that application processing time. Each pattern was run 5 times.³

Pattern A: delta update

BEGIN;
SELECT stock FROM items WHERE id = :id;
-- if stock <= 0, ROLLBACK and stop
-- (sleep 0.1s)
UPDATE items SET stock = stock - 1 WHERE id = :id;
INSERT INTO orders ...;
COMMIT;

The UPDATE is written as SET stock = stock - 1. The database does the subtraction when the UPDATE runs.

Column definitions (the same for the tables that follow):

Column	Meaning
stock_sum	Total stock after processing
decremented	Initial total stock minus stock_sum. The total number actually decremented
orders_ok	Number of successful orders
lost_decrements	The difference between orders_ok and decremented. Above 0 means decrements were lost
negative_rows	Number of rows with negative stock

Results (identical in all 5 runs):

stock_sum	decremented	orders_ok	lost_decrements	negative_rows
-30	50	50	0	1

decremented is 50 and orders_ok is 50. All 50 orders went through, and the subtraction ran correctly 50 times. lost_decrements=0 means zero decrements were lost, so the number of subtractions is correct. The problem is that negative_rows is 1. The stock is at -30.

The 50 transactions proceed as follows.

Time	T1	T2	T3–T49	T50	stock
0	SELECT → 20, sleep	SELECT → 20, sleep	SELECT → 20, sleep	SELECT → 20, sleep	20
1	UPDATE stock - 1 (acquires lock)	waits	waits	waits	20
2	COMMIT	UPDATE stock - 1 (acquires lock)	waits	waits	19
3–49		COMMIT (time 3)	UPDATE stock - 1 and COMMIT, one at a time (T49 runs only the UPDATE)	waits	18 → -28
50			T49: COMMIT	UPDATE stock - 1 (acquires lock)	-29
51				COMMIT	-30

The subtraction itself works correctly. The problem is when the stock check happens. The check uses the value read at SELECT time, so orders keep going through even after the stock reaches 0. The system accepts more orders than it has stock for, and orders that cannot ship pile up.

Pattern B: absolute write-back

BEGIN;
SELECT stock FROM items WHERE id = :id;  -- store v = 20 in an app variable
-- if v <= 0, ROLLBACK and stop
-- (sleep 0.1s)
UPDATE items SET stock = :new_stock WHERE id = :id;  -- set the result of v-1 as a fixed value
INSERT INTO orders ...;
COMMIT;

The UPDATE is written as SET stock = :new_stock. It writes a fixed value that the app calculated at SELECT time, not the current stock value. This is the only difference from pattern A.

Results (5 runs):

run	stock_sum	decremented	orders_ok	lost_decrements	negative_rows
1	19	1	50	49	0
2	19	1	50	49	0
3	19	1	50	49	0
4	19	1	50	49	0
5	19	1	50	49	0

orders_ok is 50, so all 50 orders went through. But decremented is 1: the stock went down by only 1. lost_decrements is 49, so 49 decrements were lost. negative_rows is 0, so the stock did not go negative.

:new_stock is an app variable. When the wait ends, the UPDATE does not look at the current stock value. It uses the fixed value the app calculated at SELECT time.

A later write overwrites the earlier one, so the final value is whatever the last thread to commit wrote. Every thread that read stock=20 writes 19.

Time	T1	T2	T3	stock
0	SELECT → 20, sleep	SELECT → 20, sleep	SELECT → 20, sleep	20
1	UPDATE SET 19 (acquires lock)	waits	waits	20
2	COMMIT	UPDATE SET 19 (acquires lock)	waits	19
3		COMMIT	UPDATE SET 19 (acquires lock)	19 (unchanged)
4			COMMIT	19 (unchanged)

All three wrote SET stock = 19, but the T2 and T3 commits do not change stock, because it was already 19. The same thing happens with 50 concurrent clients: there is only one effective decrement (20→19), and the other 49 are lost. If a thread that ran its SELECT after the first commit is in the mix, that thread reads 19 and writes 18. If that thread commits last, the final value is 18. In a separate measurement under the same conditions, all five runs ended at 18.⁴

The orders succeed and the stock stays positive. There is no constraint violation and no error. In this measurement, 50 orders went through against a stock of 20, which means the system accepted a large number of orders it cannot ship. The gap between the stock count and actual sales is first detected at stocktake.

What happens here is that a preceding transaction's update is overwritten by a later write and disappears. This is called a lost update.

Comparing the two patterns

	Pattern A (delta update)	Pattern B (absolute write-back)
SET clause	stock - 1	v - 1 (app variable)
Row lock wait	Occurs	Occurs
Stock goes negative	Yes (negative_rows=1)	No
Decrements lost	No (lost_decrements=0)	Yes (lost_decrements=49)

You can tell which pattern a piece of code is by looking at whether the UPDATE uses stock - 1 (a column reference) or v - 1 (an app variable).

Through an ORM, the code does not tell you which one it is. The usual ORM style of loading an entity, changing a property, and saving it issues an absolute UPDATE by default, so it becomes pattern B even when the code is meant as a delta update. Every ORM has an API that issues a delta update (Django's F(), Rails' update_counters, Prisma's decrement, and others).⁵ With an ORM, you need to log the SQL it issues and check it.

Fixes

Once you know the type, choose a fix. A and B have different root causes, so the fixes that work are different. A fix that works for A does not necessarily work for B, and a fix that works for both costs more. Below are three fixes: conditional UPDATE and CAS with measured results, and a CHECK constraint with its behavior by design.

Conditional UPDATE

This is a fix for the negative-stock symptom (delta update). Adding AND stock > 0 to the UPDATE stops it from running once the stock reaches 0. If the stock has run out by the time the lock wait ends, the UPDATE finishes with no matching row.⁶

UPDATE items SET stock = stock - 1 WHERE id = :id AND stock > 0

As in A, the implementation checks the stock with SELECT, sleeps 0.1s, and then runs the UPDATE above. Concurrency, initial stock, and the sleep are the same as in A and B.

Results (identical in all 5 runs):

stock_sum	decremented	orders_ok	lost_decrements	negative_rows
0	20	20	0	0

The negative stock from A is gone. For orders placed after the stock runs out, the UPDATE updates nothing. PostgreSQL returns the number of updated rows (rowcount) as 0, so the app can detect this and reject the order.

The branch on the app side looks like this (pseudocode):

BEGIN;
UPDATE items SET stock = stock - 1 WHERE id = :id AND stock > 0;
-- an updated row count of 0 means out of stock
IF rowcount = 0 THEN
  ROLLBACK;
  RETURN error('out of stock');
END IF;
INSERT INTO orders ...;
COMMIT;

For an order quantity of N, it becomes SET stock = stock - :qty WHERE ... AND stock >= :qty.

This fix only works for delta updates (stock - 1). Adding the same condition to an absolute write-back does not help, because multiple threads still overwrite each other with the same fixed value.

CAS (Compare-And-Swap)

This fix works for both symptoms: stock going negative, and orders going through while the stock does not go down. It costs more to implement, and in return it works regardless of how the UPDATE is written.

Let v be the stock count read at SELECT time. At UPDATE time, if the stock is still v, nobody has cut in, so the update is safe. This check is written as a condition in the WHERE clause.

SELECT stock FROM items WHERE id = :id;  -- store this value in v
-- if stock <= 0, ROLLBACK and stop
-- compute new_stock = v - 1
UPDATE items SET stock = :new_stock WHERE id = :id AND stock = :v

Comparing on the business value (the stock count) is a simplification for the test. If the WHERE clause compares only the stock count, it cannot detect a change to other columns that the SELECT also read and the decision depended on. Also, if the stock returns to the same value, as in 20→19→20 (restocking or a cancellation), stock = :v still passes and misses the update that happened in between. With a version column (an integer incremented on every update), you can detect a conflict no matter which column changed, including when a value returns to where it was.

SELECT stock, version FROM items WHERE id = :id;  -- store the version value in v
-- if stock <= 0, ROLLBACK and stop
-- compute new_stock = stock - 1
UPDATE items SET stock = :new_stock, version = version + 1
WHERE id = :id AND version = :v

An updated row count of 0 means another transaction changed the value away from v first. This operation is called CAS (Compare-And-Swap). A caller that gets an updated row count of 0 can start over from SELECT (retry), or pass the conflict on to its own caller.

The test used an implementation that retries when the updated row count is 0. Retries were immediate, with no retry limit, no wait, and no timeout, and continued until the thread found the stock out. Concurrency, initial stock, and the sleep are the same as in A and B.

Results (5 runs):

run	stock_sum	decremented	orders_ok	lost_decrements	negative_rows	retries
1	0	20	20	0	0	148
2	0	20	20	0	0	150
3	0	20	20	0	0	146
4	0	20	20	0	0	146
5	0	20	20	0	0	144

Neither negative stock nor lost decrements occur. retries is high, 144 to 150, because 50 threads go after the same row at the same time, and the stock has often changed by the time a thread runs its UPDATE, which forces a retry. orders_ok is 20 because there are only 20 in stock. The other 30 orders are correctly rejected as out of stock after repeated retries. This fix only holds once the retry path (error handling, a retry limit, a timeout) is implemented as well.

CHECK constraint (stock >= 0)

With a CHECK (stock >= 0) constraint added to the items table (ALTER TABLE items ADD CONSTRAINT stock_nonneg CHECK (stock >= 0)), a pattern A transaction ends with a constraint violation error as soon as the stock - 1 that runs after the lock wait would take the stock below 0.⁷ The "nothing in the error log" symptom turns into an immediate error. With 50 concurrent clients against a stock of 20, the constraint should, by design, let the first 20 succeed and end the remaining 30 with a constraint violation.

It does not work for pattern B. B never makes the stock negative, so the constraint never fires. The gap between the stock count and actual sales remains.

Fixes by pattern

Pattern	Symptom	Fixes that work
A: delta update (stock - 1)	Stock goes negative	CHECK constraint / conditional UPDATE / CAS
B: absolute write-back (:new_stock)	Orders go through but stock does not go down	CAS only (conditional UPDATE and CHECK constraint do not work)

If B's code can be rewritten in the stock - 1 form, adding AND stock > 0 is enough (the same fix as the conditional UPDATE). If you want to fix B as it is, or if there is complex app-side processing between SELECT and UPDATE, you need CAS (with retries).

Other fixes

You can also take the row lock at SELECT time with SELECT ... FOR UPDATE, but it raises concerns about lock contention, deadlocks, throughput, and more, so I cover it in a separate article. Raising the isolation level to REPEATABLE READ or higher turns concurrent writes in both A and B into "could not serialize access due to concurrent update" errors.⁸ Data no longer ends up wrong without anyone noticing, but you need error handling and retries. That option, including its throughput impact, is also covered in a separate article.

A query to tell the type apart in a test environment

:keys is the number of rows in the target table, and :init_stock is the initial stock per row. For one row with an initial stock of 20, use :keys = 1 and :init_stock = 20.

SELECT
  sum(stock)                                          AS stock_sum,
  :keys * :init_stock - sum(stock)                    AS decremented,
  (SELECT count(*) FROM orders)                       AS orders_ok,
  (SELECT count(*) FROM orders)
    - (:keys * :init_stock - sum(stock))              AS lost_decrements,
  count(*) FILTER (WHERE stock < 0)                   AS negative_rows
FROM items;

If negative_rows is positive, it is type A (the stock has gone negative). If lost_decrements is positive, it is type B (decrements have been lost). If both are 0, either concurrency is low or the requests are spread across many rows.

This query is for telling the type apart when you reproduce the symptom in a test environment. It is not suited to production. In production, :init_stock (the initial stock per row) often cannot be reconstructed without the restocking history, and the query counts every row in orders, so past orders get mixed in unless you limit the time range. In production, the clue for type A is an alert that watches for rows with stock < 0, and the clue for type B is comparing the order count with the stock decrease over a limited time range.

Summary

How the symptom shows up depends on whether the UPDATE uses a column reference (stock - 1) or an absolute value computed in an app variable. Stock goes negative with the former (pattern A), and decrements are lost with the latter (pattern B).

A query that collects negative_rows and lost_decrements together tells you which type you have in your own environment.

The conditional UPDATE does not work for B. If B's code can be rewritten in the stock - 1 form, you can move the subtraction to the database to get A's form and then add the conditional UPDATE, and the CAS retry path is no longer needed. When there is complex app-side processing between SELECT and UPDATE, or when multiple rows or columns have to be updated consistently, the code cannot be moved to A's form, so you need CAS.

If the app's decision depends on the stock count and you need to guarantee that the value does not change between the check and the update, SELECT ... FOR UPDATE is an option. Measurements including its throughput impact are in a separate article.

---

1. The Read Committed section of the PostgreSQL 16 documentation. ↩

2. The Read Committed section of the PostgreSQL 16 documentation. A later update waits for the earlier update to commit or roll back, and if the earlier one committed, the later one operates on the updated version of the row. ↩

3. The test results are here, and the code is in show-your-work. ↩

4. Test results. ↩

5. Django's F(), Rails' update_counters, and Prisma's atomic number operations. ↩

6. The Read Committed section of the PostgreSQL 16 documentation. When the wait ends, the WHERE clause is re-evaluated against the updated row. ↩

7. The CHECK constraints section of the PostgreSQL 16 documentation. ↩

8. The Repeatable Read section of the PostgreSQL 16 documentation. ↩