Preventing Race Conditions in Inventory Management Using Database Transactions

Introduction

Inventory management looks simple at first: check how many items are available, confirm an order, and reduce the stock.

But what happens when two customers try to purchase the last item at exactly the same time?

If both requests read the same stock value before either request updates it, the application may accept both orders even though only one item was available. This is a classic example of a race condition.

In this tutorial, we will explore why this happens and how database transactions and atomic updates can help prevent overselling.

1. Understanding the Problem

Imagine an inventory table contains the following record:

Product	Available stock
Keyboard	1

Two customers submit an order simultaneously.

1. Request A reads stock = 1.
2. Request B reads stock = 1.
3. Both requests conclude that stock is available.
4. Both orders are accepted.

The application has now sold more units than it had available.

The problem is not simply that the database is slow. The problem is that checking stock and reserving it are separate operations without sufficient concurrency control.

2. Why a Simple Check Is Not Enough

Consider this simplified JavaScript logic:

if (product.stock > 0) {
  await createOrder(product.id);
  await updateStock(product.id, product.stock - 1);
}

This code illustrates the danger of a read-check-write sequence. Another request can execute between the check and the update.

Adding a transaction around arbitrary code is not automatically enough either. The database operations must be designed to prevent conflicting requests from both reserving the same stock.

3. Use an Atomic Conditional Update

One practical approach is to perform the stock check and decrement in a single database statement.

For PostgreSQL, the operation can look like this:

UPDATE products
SET stock = stock - 1
WHERE id = $1
  AND stock > 0
RETURNING id, stock;

The database checks the condition and updates the matching row as one statement.

• If a row is returned, one unit was successfully reserved.
• If no row is returned, the product does not exist or no stock is available.

Under PostgreSQL's normal transactional behaviour, concurrent updates to the same row are coordinated so that a later update rechecks the relevant condition after waiting for a conflicting update.

This is stronger than reading stock into application memory and updating it later.

4. Why Transactions Still Matter

Reserving stock is often only one part of an order workflow. The application may also need to create an order record and its associated order items.

These changes should remain consistent. For example, the application should not decrement inventory successfully and then leave the system with no corresponding order because a later database operation failed.

A transaction can group the operations:

BEGIN;

UPDATE products
SET stock = stock - 1
WHERE id = $1
  AND stock > 0
RETURNING id;

-- The application must verify that one row was returned.
-- If not, roll back the transaction.
-- Otherwise, insert the order record.

COMMIT;

This is a conceptual illustration. The application must inspect the returned rows and issue ROLLBACK when stock is unavailable or a required operation fails. Production code should also handle exceptions and transaction cleanup.

5. Implementing the Same Idea with an ORM

An ORM such as Prisma can make database access more convenient, but the same concurrency principles still apply.

A robust implementation should use a conditional update supported by the database and ORM, or an appropriate parameterized SQL statement. It should then create the order within a transaction and roll back if any required step fails.

The exact implementation depends on the schema, database provider, and ORM version. Avoid assuming that a normal read followed by a write is concurrency-safe merely because it is wrapped in a transaction.

6. Additional Production Considerations

Preventing negative stock is important, but a real order system has other concerns:

• Idempotency: Retrying the same payment or order request should not accidentally create duplicate orders.
• Payment failures: Inventory reservations may need to expire or be released if payment does not complete.
• Deadlocks: Transactions that lock multiple rows should acquire locks in a consistent order where possible.
• Testing: Simulate concurrent requests to verify that available stock is never oversold.
• Monitoring: Track failed reservations, transaction errors, and unusual order patterns.

These concerns demonstrate why inventory management is a system-design problem rather than just a counter update.

7. How to Test the Fix

Create a product with one unit in stock, then submit two order requests concurrently.

The expected outcome is:

• Only one request successfully reserves the final unit.
• The other request receives an out-of-stock response.
• The stock value never becomes negative.
• A failed order does not leave partial database changes behind.

Repeat the test with larger stock values and more concurrent requests. Automated integration tests against the actual database are preferable to relying only on mocked responses.

Conclusion

Race conditions occur when concurrent operations make decisions using state that can change before their updates are completed.

Atomic conditional updates help protect individual inventory changes, while transactions keep related database operations consistent. Idempotency, error handling, and concurrency tests add further protection for real-world order workflows.

The key lesson is simple: enforce critical business rules at the database-operation level rather than relying only on checks performed in application code.

About the author: I am a Computer Science engineering student interested in full-stack development, database systems, and reliable software engineering.

This article presents a general implementation pattern. Verify it against your own project and database schema before describing it as a feature implemented in your repository.