Menu

Negative stock and lost updates in a Postgres transaction
πŸ“°

Negative stock and lost updates in a Postgres transaction

DEV Community: postgresΒ·KazuΒ·7 days ago
#uL2dxztS
#devtopostgresql#devto#dev#stock#select
Reading 0:00
15s threshold

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). 1 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.…

Continue reading β€” create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.