Building an e-commerce application is not just about displaying products, adding items to a cart, and allowing users to place orders.

One interesting challenge appears when multiple requests try to modify the same data at the same time.

For example:

• What happens when two users try to purchase the last available unit of a product?
• What happens when the same user submits checkout twice simultaneously?
• How do we prevent duplicate orders and incorrect inventory updates?

These are concurrency problems, and handling them correctly is essential for building reliable backend systems.

While developing Acheter Hub, a full-stack e-commerce application, I implemented transactional checkout and PostgreSQL row-level locking to handle these scenarios.

In this article, I'll walk through the problems, the implementation, and how I tested both cases.

1. A quick look at Acheter Hub

Acheter Hub is a full-stack e-commerce application built using:

• Frontend: React, Vite, TypeScript
• Backend: Node.js, Express, TypeScript
• Database: PostgreSQL (Neon)
• Deployment: Vercel and Render

The backend follows a layered architecture:

Routes → Controllers → Services → PostgreSQL

The application supports product browsing, authentication, persistent carts, checkout, and order history.

The checkout process is where concurrency becomes particularly important.

2. The problem: Two users trying to purchase the last item

Imagine a product has only one unit remaining in stock.

Two different users add that product to their carts and attempt to purchase it at nearly the same time.

Without proper concurrency handling, both requests might read the available stock as 1 before either request updates it.

Both requests could then proceed as if the product were available.

This creates an overselling problem.

What can go wrong?

A simplified version of the problematic flow looks like this:

1. User A reads stock = 1.
2. User B reads stock = 1.
3. User A checks out.
4. User B checks out.
5. Both requests attempt to purchase the same unit.

We need a way to ensure that only one transaction can successfully deduct the final unit.

3. Scenario 1: Different users competing for the same product

The solution I implemented uses a conditional SQL update inside a database transaction.

Here is the important query:

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

Let's break it down.

• stock = stock - $1 deducts the requested quantity.
• WHERE id = $2 identifies the product.
• AND stock >= $1 ensures sufficient stock exists before deduction.
• RETURNING id tells us whether the update succeeded.

The important part is that the stock check and deduction happen together in a single SQL statement.

PostgreSQL automatically obtains a row-level lock when updating a row.

If another transaction tries to update the same product row, it has to wait until the conflicting transaction finishes.

How does this prevent overselling?

Suppose the stock is initially 1.

1. Transaction A executes the conditional update.
2. PostgreSQL locks the product row.
3. Transaction A deducts one unit, making stock 0.
4. Transaction B attempts the same update and waits.
5. Transaction A commits.
6. Transaction B continues, and PostgreSQL re-evaluates the update condition against the updated row.
7. Since stock is now 0, the condition stock >= 1 fails.
8. No row is updated, and the application throws an INSUFFICIENT_STOCK error.

The second transaction cannot purchase an item that is no longer available.

Flow diagram

sequenceDiagram participant A as User A participant B as User B participant DB as PostgreSQL Note over DB: Initial stock = 1 A->>DB: UPDATE stock if stock >= 1 Note over DB: Row locked DB-->>A: Update successful, stock = 0 B->>DB: UPDATE stock if stock >= 1 Note over DB: Transaction B waits A->>DB: COMMIT Note over DB: Lock released Note over DB: B resumes and condition is rechecked DB-->>B: No rows updated Note over B: INSUFFICIENT_STOCK

This ensures that only one user can successfully purchase the last available unit.

4. Scenario 2: The same user submitting checkout twice

The second problem is slightly different.

Imagine a user clicks the checkout button twice, or two checkout requests reach the backend almost simultaneously.

Both requests belong to the same user and may attempt to process the same cart.

Without protection, this could result in duplicate orders or incorrect inventory deductions.

To handle this, I used PostgreSQL's SELECT ... FOR UPDATE.

Here is the query:

SELECT id FROM users WHERE id = $1 FOR UPDATE; 

This explicitly locks the selected user's row.

If another transaction attempts to acquire the same lock, it has to wait until the first transaction commits or rolls back.

How does this work?

Suppose the same user sends two checkout requests: A and B.

1. Transaction A begins.
2. Transaction A locks the user's row using SELECT ... FOR UPDATE.
3. Transaction B begins and attempts to lock the same user row.
4. Transaction B waits.
5. Transaction A reads the cart and processes the order.
6. Transaction A deducts stock, creates the order and order items, and deletes the user's cart.
7. Transaction A commits.
8. The lock is released.
9. Transaction B acquires the lock and reads the cart.
10. The cart is now empty, so the application throws CART_EMPTY.

Only one order is created.

Flow diagram

sequenceDiagram participant A as Checkout A participant B as Checkout B participant DB as PostgreSQL A->>DB: BEGIN A->>DB: SELECT user FOR UPDATE Note over DB: User row locked B->>DB: BEGIN B->>DB: SELECT user FOR UPDATE Note over DB: B waits for A A->>DB: Read cart A->>DB: Deduct stock A->>DB: Create order and order items A->>DB: Delete cart A->>DB: COMMIT Note over DB: Lock released DB-->>B: Lock acquired B->>DB: Read cart DB-->>B: Cart is empty Note over B: CART_EMPTY B->>DB: ROLLBACK

The important detail is that the second request reads the cart only after acquiring the lock. Therefore, it sees the committed result of the first checkout.

5. Putting everything inside a transaction

Both mechanisms are used inside a PostgreSQL transaction.

A transaction allows multiple database operations to be treated as one logical unit.

The checkout process follows this general structure:

BEGIN; -- Lock the user's row SELECT id FROM users WHERE id = $1 FOR UPDATE; -- Read cart items -- Conditionally deduct product stock -- Create order -- Create order items -- Clear cart COMMIT; 

If something fails at any point, the transaction is rolled back:

ROLLBACK; 

This matters because we don't want a partially completed checkout.

For example, if stock is deducted but creating an order item fails, the transaction should not leave the database in an inconsistent state.

A rollback reverses the changes made within that transaction.

Transaction lifecycle

flowchart TD A[Start checkout] --> B[BEGIN] B --> C[Lock user row] C --> D[Read cart] D --> E{Cart empty?} E -->|Yes| R[ROLLBACK] E -->|No| F[Deduct stock conditionally] F --> G{Stock available?} G -->|No| R G -->|Yes| H[Create order] H --> I[Create order items] I --> J[Clear cart] J --> K[COMMIT]

In the actual implementation, I use a dedicated PostgreSQL client acquired through pool.connect(). Every query in the transaction runs through that same client, and it is released in a finally block.

6. A look at the implementation

Here is a simplified version of the checkout service:

import { pool } from "../db/pool"; import type { PoolClient } from "pg"; export async function createOrder(userId: number) { const client: PoolClient = await pool.connect(); try { await client.query("BEGIN"); // Prevent simultaneous checkout requests // from the same user. await client.query( `SELECT id FROM users WHERE id = $1 FOR UPDATE`, [userId] ); const cartResult = await client.query( `SELECT ci.product_id, ci.quantity, p.name, p.price_cents, p.stock FROM cart_items AS ci JOIN products AS p ON p.id = ci.product_id WHERE ci.user_id = $1 ORDER BY ci.product_id`, [userId] ); const items = cartResult.rows; if (items.length === 0) { throw new Error("CART_EMPTY"); } for (const item of items) { const stockResult = await client.query( `UPDATE products SET stock = stock - $1 WHERE id = $2 AND stock >= $1 RETURNING id`, [item.quantity, item.product_id] ); if (stockResult.rowCount === 0) { throw new Error("INSUFFICIENT_STOCK"); } } // Calculate total and create the order. // Insert order items. // Clear the user's cart. await client.query("COMMIT"); } catch (error) { await client.query("ROLLBACK"); throw error; } finally { client.release(); } } 

The code above is simplified to highlight the concurrency-related parts. The actual implementation also handles order totals, order creation, order items, and shipping information.

There are two important locking mechanisms here:

These solve two different problems.

The user-row lock protects the checkout workflow for a particular user, while the conditional product update protects inventory when multiple users compete for the same product.

7. Testing both concurrency scenarios

Implementing concurrency handling is one thing. Verifying that it actually works is another.

I created a concurrency test script to simulate both situations.

Test 1: Different users competing for the last unit

Initial conditions:

• Two different users.
• One product.
• Stock = 1.
• Both users attempt checkout.

Expected result:

• One checkout succeeds.
• The other returns INSUFFICIENT_STOCK.
• Final stock = 0.
• Exactly one order is created.

Test 2: Same user submitting checkout twice

Initial conditions:

• One user.
• Two checkout requests.
• Cart contains a product with quantity 1.
• Product stock = 5.

Expected result:

• One checkout succeeds.
• The second returns CART_EMPTY.
• Final stock = 4.
• Exactly one order is created.

Both tests help verify that the database operations behave correctly under concurrent requests.

8. What I learned

Working on these scenarios helped me understand several important backend and database concepts:

• A transaction does not lock the entire database.
• PostgreSQL uses row-level locks to coordinate conflicting operations.
• SELECT ... FOR UPDATE allows explicit locking of selected rows.
• An UPDATE automatically obtains the necessary row-level lock.
• Conditional updates can prevent race conditions during inventory deduction.
• Transactions help maintain consistency when multiple database operations must succeed or fail together.
• Concurrency needs to be considered even in relatively small applications.

One important takeaway is that concurrency is not just a concern for large-scale systems. Even a small e-commerce application can encounter race conditions when multiple requests modify shared data.

Conclusion

Building Acheter Hub gave me an opportunity to go beyond basic CRUD operations and work with transactional database operations.

The two scenarios taught me how to approach concurrency from different angles:

1. Different users, same product: Conditional stock updates and PostgreSQL row-level locking prevent overselling.
2. Same user, simultaneous checkout: Explicit user-row locking prevents duplicate checkout processing.

There is still a lot more to explore in database concurrency, isolation levels, deadlocks, and transaction performance, but implementing and testing these cases was a valuable learning experience.

Project: Acheter Hub

• Live Demo: https://acheterhub.vercel.app/
• GitHub Repository: https://github.com/tanqdev/Acheter

I'd love to hear how others approach concurrency control in e-commerce applications, especially when dealing with inventory management and simultaneous checkout requests.

postgresql #nodejs #backend #webdev #typescript #database #concurrency #buildinpublic