Preventing Overselling in Inventory Systems with PostgreSQL Transactions and Idempotency

Preventing Overselling in Inventory Systems with PostgreSQL Transactions and Idempotency Building an inventory system looks straightforward until two customers try to purchase the last available item at the same time. Consider a small electronics retailer with one charging adapter left in stock. A salesperson creates an order from the billing counter while another employee processes an online order. Both requests arrive almost simultaneously. Both read the available quantity as one. Both assume that the item can be sold. If the backend handles stock updates incorrectly, the application may confirm two orders even though only one adapter is available. This is a classic race condition, and it can affect retail billing platforms, e-commerce applications, warehouse systems and repair shop inventory management. In this article, we'll examine why these problems occur and how PostgreSQL transactions, atomic updates and idempotency can help maintain inventory consistency. Why Basic Inventory Updates Fail A common implementation follows three steps: Fetch the current stock quantity. Check whether sufficient stock is available. Subtract the requested quantity. A simplified JavaScript implementation might look like this: async function sellItem(productId, quantity) { const product = await db.getProduct(productId); if (product.stock < quantity) { throw new Error("Insufficient stock"); } await db.updateStock( productId, product.stock - quantity ); } The problem is not the subtraction itself. The issue is that another request can change the stock between reading and updating the database. Understanding the Race Condition Suppose the initial stock is one unit. Step Request A Request B 1 Reads stock = 1 2 Reads stock = 1 3 Validation passes 4 Validation passes 5 Updates stock to 0 6 Updates stock to 0 7 Order confirmed Order confirmed The database may still display zero units, making the inventory appear correct. However, the application has confirmed two sales against one physical item. This is a lost-update problem combined with incorrect business-level order confirmation. A single-threaded Node.js event loop does not prevent it. Multiple requests, application instances and asynchronous database operations can still overlap. Designing a Reliable Inventory Database Before implementing concurrency control, we need a database structure that supports the business operation. For this example, we'll use PostgreSQL. Product Inventory Table CREATE TABLE products ( id BIGSERIAL PRIMARY KEY, sku VARCHAR(50) UNIQUE NOT NULL, name VARCHAR(200) NOT NULL, stock_quantity INTEGER NOT NULL DEFAULT 0, updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), CONSTRAINT stock_non_negative CHECK (stock_quantity >= 0) ); The stock_non_negative constraint prevents a negative quantity from being stored. However, the constraint alone cannot prevent two separate orders from being created for the same available unit. Orders Table CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, idempotency_key UUID UNIQUE NOT NULL, product_id BIGINT NOT NULL REFERENCES products(id), quantity INTEGER NOT NULL CHECK (quantity > 0), status VARCHAR(20) NOT NULL DEFAULT 'confirmed', created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() ); The idempotency key allows the backend to identify repeated attempts to perform the same logical operation. For simplicity, each order in this example contains one product. A production application may require separate order and order-item tables. Solution 1: Use an Atomic Conditional Update For simple stock deductions, PostgreSQL can validate and update the inventory in a single SQL statement. UPDATE products SET stock_quantity = stock_quantity - 1, updated_at = NOW() WHERE id = 101 AND stock_quantity >= 1 RETURNING id, stock_quantity; This statement is atomic. If two transactions attempt to update the same product row concurrently, PostgreSQL coordinates the updates using row-level locking. Under the default READ COMMITTED isolation level, a waiting update re-evaluates its condition after the competing transaction commits. Consequently, when only one unit exists, only one request can successfully deduct that unit. The application should check whether the query returned a row. const result = await client.query( ` UPDATE products SET stock_quantity = stock_quantity - $1, updated_at = NOW() WHERE id = $2 AND stock_quantity >= $1 RETURNING id, stock_quantity `, [quantity, productId] ); if (result.rowCount === 0) { throw new Error( "Product not found or insufficient stock" ); } This is generally preferable to separately reading stock and then writing a calculated value. However, an atomic update solves only the inventory deduction. If an order record must also be created, both operations need to happen within the same transaction. Solution 2: Combine Stock Updates and Order Creation in a Transaction Consider a situation where the stock deduction succeeds, but the application crashes before saving the order. The stock has decreased, but no corresponding order exists. A database transaction prevents this partial state. Node.js and PostgreSQL Example The following implementation uses the pg package. import { Pool } from "pg"; const pool = new Pool({ connectionString: process.env.DATABASE_URL }); async function createOrder({ productId, quantity, idempotencyKey }) { if (!Number.isSafeInteger(quantity) || quantity = $1 RETURNING id `, [quantity, productId] ); if (stockResult.rowCount === 0) { throw new Error("Insufficient stock"); } const completed = await client.query( ` UPDATE orders SET status = 'confirmed' WHERE id = $1 RETURNING * `, [inserted.rows[0].id] ); await client.query("COMMIT"); return completed.rows[0]; } catch (error) { await client.query("ROLLBACK"); throw error; } finally { client.release(); } } Why This Design Works The transaction provides three important guarantees. Atomicity: Either the stock deduction and order confirmation both succeed, or neither change is committed. Concurrency safety: The conditional update prevents two concurrent requests from deducting the same final stock unit. Retry protection: The unique idempotency key ensures that the same operation cannot independently create multiple orders. When two requests use the same key, PostgreSQL's unique constraint coordinates the competing inserts. Once the first transaction commits, the other request can retrieve the existing order. This example deliberately rolls back an insufficient-stock attempt. In a real API, you may want to persist rejected requests separately so that retries return a consistent failure response. Production systems also need structured API errors, authentication, request validation and explicit handling of database connection failures. Solution 3: Use Row-Level Locks for Complex Workflows Atomic updates work well when availability can be evaluated directly in SQL. Some operations need more information before deciding whether inventory can be deducted. For example, a repair shop might need to verify: Whether a spare part is allocated to another repair job. Whether it belongs to the correct warehouse. Whether it is reserved for a pending service request. Whether the selected batch meets a compatibility requirement. In such situations, an explicit row lock can be useful. BEGIN; SELECT stock_quantity FROM products WHERE id = 101 FOR UPDATE; -- Validate additional business rules. UPDATE products SET stock_quantity = stock_quantity - 1 WHERE id = 101; COMMIT; SELECT ... FOR UPDATE locks the selected row until the transaction finishes. Other transactions attempting conflicting row modifications must wait. This allows the application to read the selected row, apply business rules and modify it without another transaction changing that row in between. Important Limitation A row lock protects the rows actually locked. It does not automatically protect every related record or business condition. If availability depends on reservations, warehouse allocations or other tables, the transaction must also protect those relevant records or use an appropriate concurrency strategy. Keep the transaction short. Holding row locks while calling external APIs or waiting for user input can increase database contention. Idempotency: Protecting Against Duplicate Requests Concurrency is not the only reason stock quantities become incorrect. Mobile networks can disconnect, payment callbacks can be delivered more than once, and users may press the submit button repeatedly. Imagine that a billing application sends an order request. The backend creates the order successfully, but the connection drops before the response reaches the client. The client retries. Without idempotency, the system may create a second order and deduct the stock again. Using an Idempotency Key The client can generate a unique identifier for each logical order submission. const idempotencyKey = crypto.randomUUID(); async function submitOrder(productId, quantity) { const response = await fetch("/api/orders", { method: "POST", headers: { "Content-Type": "application/json", "Idempotency-Key": idempotencyKey }, body: JSON.stringify({ productId, quantity }) }); if (!response.ok) { throw new Error("Order submission failed"); } return response.json(); } The same key must be reused when retrying that submission. Generating a new key on every retry defeats the purpose. The backend must also validate that an existing idempotency key is not being reused with a different request. For a multi-tenant application, the key should generally be scoped to the authenticated tenant or merchant. What About Multiple Warehouses? A single quantity field becomes insufficient when a retailer operates several branches. Suppose the business has: Five adapters at its main warehouse. Two adapters at its retail counter. Three adapters reserved for repair jobs. Reporting a total of ten units does not mean all ten are available to sell. A more suitable schema separates stock by location. CREATE TABLE inventory_locations ( id BIGSERIAL PRIMARY KEY, name VARCHAR(150) NOT NULL ); CREATE TABLE location_stock ( product_id BIGINT NOT NULL REFERENCES products(id), location_id BIGINT NOT NULL REFERENCES inventory_locations(id), available_quantity INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (product_id, location_id), CHECK (available_quantity >= 0) ); A location-specific deduction can then use: UPDATE location_stock SET available_quantity = available_quantity - $1 WHERE product_id = $2 AND location_id = $3 AND available_quantity >= $1 RETURNING available_quantity; This prevents one branch from selling stock that physically belongs to another branch. For transfers, both the source deduction and destination addition should occur within one transaction when they represent an immediate movement within the same database. If stock is physically in transit, a separate in-transit state is more accurate than immediately treating it as available at the destination. Testing Concurrent Inventory Requests Concurrency bugs are difficult to discover through ordinary manual testing. A better approach is to send multiple requests simultaneously and verify the resulting database state. Here is a simplified Node.js test: import assert from "node:assert/strict"; async function testConcurrentOrders() { // Test fixture: product 101 starts with 1 unit. // Each attempt represents a different logical order. const attempts = Array.from({ length: 20 }, () => createOrder({ productId: 101, quantity: 1, idempotencyKey: crypto.randomUUID() }) ); const results = await Promise.allSettled(attempts); const successfulOrders = results.filter( result => result.status === "fulfilled" ); assert.equal(successfulOrders.length, 1); const stock = await pool.query( "SELECT stock_quantity FROM products WHERE id = $1", [101] ); assert.equal(stock.rows[0].stock_quantity, 0); console.log("Concurrency test passed"); } Run this against an isolated test database with a fresh product fixture. The expected result is: Exactly one successful order. Nineteen rejected attempts. Final stock quantity of zero. No duplicate confirmed orders. For production confidence, also test duplicate idempotency keys, transaction rollbacks, simultaneous warehouse transfers and application restarts. A single passing test is useful, but repeated stress tests and controlled fault injection provide stronger evidence. Common Architectural Mistakes Even with database transactions, several implementation choices can create problems. Keeping Transactions Open During External Requests Avoid calling payment gateways, notification services or remote APIs while holding inventory row locks. External calls can be slow or fail unpredictably. For workflows involving external services, consider reservations, explicit order states and asynchronous processing. Using Redis as the Only Source of Stock Truth Redis can help with caching and coordination, but a cache update and a PostgreSQL transaction are not automatically atomic together. If PostgreSQL is the authoritative inventory store, make the correctness decision there. Ignoring Deadlocks Transactions that update multiple products in inconsistent orders may deadlock. A consistent lock acquisition order reduces risk. Applications should also be prepared to retry appropriate transactions after PostgreSQL reports a deadlock or serialization failure. Treating Inventory Quantity as a Complete Audit Trail A stock quantity tells you how many items remain. It does not explain why that quantity changed. For reconciliation and investigation, consider an inventory movement ledger containing the product, location, quantity change, reason and originating transaction. Choosing the Right Approach Requirement Practical approach Simple stock deduction Atomic conditional UPDATE Stock deduction with order creation Database transaction Complex validation across records Row locks or stronger transaction strategy Duplicate API submissions Idempotency keys Multiple stores or warehouses Location-level inventory Inventory reconciliation Stock movement ledger Cross-service fulfilment Reservations and explicit workflow states There is no need to introduce distributed locks, event streaming or microservices simply to handle a stock deduction. For many small and medium-sized systems, a well-designed PostgreSQL transaction is enough. Additional infrastructure should address a measured requirement rather than compensate for unclear database consistency rules. Final Thoughts Inventory correctness depends on more than keeping a number updated. It requires the application to define when stock becomes unavailable, how simultaneous requests are handled and what happens when an operation is retried or fails halfway through. Atomic SQL updates, transactions and idempotency provide a reliable starting point. As the application grows, location-specific inventory, reservations and movement ledgers can be introduced according to actual operational needs. The important principle is to protect the business operation as a whole, rather than treating individual database queries as unrelated steps. Discussion: How do you handle concurrent stock reservations in your applications? Do you prefer PostgreSQL row locking, conditional updates or a dedicated inventory reservation service?