<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Tanishq Srivastava</title>
    <description>The latest articles on DEV Community by Tanishq Srivastava (@tanishq_srivastava_c6468b).</description>
    <link>https://dev.to/tanishq_srivastava_c6468b</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4056441%2Fcd9cbba5-1742-4722-9374-040da12e4c06.png</url>
      <title>DEV Community: Tanishq Srivastava</title>
      <link>https://dev.to/tanishq_srivastava_c6468b</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/tanishq_srivastava_c6468b"/>
    <language>en</language>
    <item>
      <title>How I Used PostgreSQL Row-Level Locking to Prevent Overselling in My E-commerce App</title>
      <dc:creator>Tanishq Srivastava</dc:creator>
      <pubDate>Fri, 13 Nov 2026 15:33:51 +0000</pubDate>
      <link>https://dev.to/tanishq_srivastava_c6468b/how-i-used-postgresql-row-level-locking-to-prevent-overselling-in-my-e-commerce-app-30cm</link>
      <guid>https://dev.to/tanishq_srivastava_c6468b/how-i-used-postgresql-row-level-locking-to-prevent-overselling-in-my-e-commerce-app-30cm</guid>
      <description>&lt;p&gt;Building an e-commerce application is not just about displaying products, adding items to a cart, and allowing users to place orders.&lt;/p&gt;

&lt;p&gt;One interesting challenge appears when multiple requests try to modify the same data at the same time.&lt;/p&gt;

&lt;p&gt;For example:&lt;/p&gt;

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

&lt;p&gt;These are concurrency problems, and handling them correctly is essential for building reliable backend systems.&lt;/p&gt;

&lt;p&gt;While developing &lt;strong&gt;Acheter Hub&lt;/strong&gt;, a full-stack e-commerce application, I implemented transactional checkout and PostgreSQL row-level locking to handle these scenarios.&lt;/p&gt;

&lt;p&gt;In this article, I'll walk through the problems, the implementation, and how I tested both cases.&lt;/p&gt;

&lt;h2&gt;
  
  
  1. A quick look at Acheter Hub
&lt;/h2&gt;

&lt;p&gt;Acheter Hub is a full-stack e-commerce application built using:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Frontend:&lt;/strong&gt; React, Vite, TypeScript&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Backend:&lt;/strong&gt; Node.js, Express, TypeScript&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Database:&lt;/strong&gt; PostgreSQL (Neon)&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Deployment:&lt;/strong&gt; Vercel and Render&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The backend follows a layered architecture:&lt;/p&gt;

&lt;p&gt;&lt;code&gt;Routes → Controllers → Services → PostgreSQL&lt;/code&gt;&lt;/p&gt;

&lt;p&gt;The application supports product browsing, authentication, persistent carts, checkout, and order history.&lt;/p&gt;

&lt;p&gt;The checkout process is where concurrency becomes particularly important.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. The problem: Two users trying to purchase the last item
&lt;/h2&gt;

&lt;p&gt;Imagine a product has only one unit remaining in stock.&lt;/p&gt;

&lt;p&gt;Two different users add that product to their carts and attempt to purchase it at nearly the same time.&lt;/p&gt;

&lt;p&gt;Without proper concurrency handling, both requests might read the available stock as &lt;code&gt;1&lt;/code&gt; before either request updates it.&lt;/p&gt;

&lt;p&gt;Both requests could then proceed as if the product were available.&lt;/p&gt;

&lt;p&gt;This creates an overselling problem.&lt;/p&gt;

&lt;h3&gt;
  
  
  What can go wrong?
&lt;/h3&gt;

&lt;p&gt;A simplified version of the problematic flow looks like this:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;User A reads stock = 1.&lt;/li&gt;
&lt;li&gt;User B reads stock = 1.&lt;/li&gt;
&lt;li&gt;User A checks out.&lt;/li&gt;
&lt;li&gt;User B checks out.&lt;/li&gt;
&lt;li&gt;Both requests attempt to purchase the same unit.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;We need a way to ensure that only one transaction can successfully deduct the final unit.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Scenario 1: Different users competing for the same product
&lt;/h2&gt;

&lt;p&gt;The solution I implemented uses a conditional SQL update inside a database transaction.&lt;/p&gt;

&lt;p&gt;Here is the important query:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;UPDATE&lt;/span&gt; &lt;span class="n"&gt;products&lt;/span&gt;
&lt;span class="k"&gt;SET&lt;/span&gt; &lt;span class="n"&gt;stock&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;stock&lt;/span&gt; &lt;span class="o"&gt;-&lt;/span&gt; &lt;span class="nv"&gt;$1&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nv"&gt;$2&lt;/span&gt;
  &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="n"&gt;stock&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;=&lt;/span&gt; &lt;span class="nv"&gt;$1&lt;/span&gt;
&lt;span class="k"&gt;RETURNING&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Let's break it down.&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;code&gt;stock = stock - $1&lt;/code&gt; deducts the requested quantity.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;WHERE id = $2&lt;/code&gt; identifies the product.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;AND stock &amp;gt;= $1&lt;/code&gt; ensures sufficient stock exists before deduction.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;RETURNING id&lt;/code&gt; tells us whether the update succeeded.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;The important part is that the stock check and deduction happen together in a single SQL statement.&lt;/p&gt;

&lt;p&gt;PostgreSQL automatically obtains a row-level lock when updating a row.&lt;/p&gt;

&lt;p&gt;If another transaction tries to update the same product row, it has to wait until the conflicting transaction finishes.&lt;/p&gt;

&lt;h3&gt;
  
  
  How does this prevent overselling?
&lt;/h3&gt;

&lt;p&gt;Suppose the stock is initially &lt;code&gt;1&lt;/code&gt;.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Transaction A executes the conditional update.&lt;/li&gt;
&lt;li&gt;PostgreSQL locks the product row.&lt;/li&gt;
&lt;li&gt;Transaction A deducts one unit, making stock &lt;code&gt;0&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;Transaction B attempts the same update and waits.&lt;/li&gt;
&lt;li&gt;Transaction A commits.&lt;/li&gt;
&lt;li&gt;Transaction B continues, and PostgreSQL re-evaluates the update condition against the updated row.&lt;/li&gt;
&lt;li&gt;Since stock is now &lt;code&gt;0&lt;/code&gt;, the condition &lt;code&gt;stock &amp;gt;= 1&lt;/code&gt; fails.&lt;/li&gt;
&lt;li&gt;No row is updated, and the application throws an &lt;code&gt;INSUFFICIENT_STOCK&lt;/code&gt; error.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;The second transaction cannot purchase an item that is no longer available.&lt;/p&gt;

&lt;h3&gt;
  
  
  Flow diagram
&lt;/h3&gt;



&lt;pre data-lang="mermaid"&gt;&lt;code&gt;sequenceDiagram
    participant A as User A
    participant B as User B
    participant DB as PostgreSQL

    Note over DB: Initial stock = 1

    A-&amp;gt;&amp;gt;DB: UPDATE stock if stock &amp;gt;= 1
    Note over DB: Row locked
    DB--&amp;gt;&amp;gt;A: Update successful, stock = 0

    B-&amp;gt;&amp;gt;DB: UPDATE stock if stock &amp;gt;= 1
    Note over DB: Transaction B waits

    A-&amp;gt;&amp;gt;DB: COMMIT
    Note over DB: Lock released

    Note over DB: B resumes and condition is rechecked
    DB--&amp;gt;&amp;gt;B: No rows updated

    Note over B: INSUFFICIENT_STOCK&lt;/code&gt;&lt;/pre&gt;



&lt;p&gt;This ensures that only one user can successfully purchase the last available unit.&lt;/p&gt;

&lt;h2&gt;
  
  
  4. Scenario 2: The same user submitting checkout twice
&lt;/h2&gt;

&lt;p&gt;The second problem is slightly different.&lt;/p&gt;

&lt;p&gt;Imagine a user clicks the checkout button twice, or two checkout requests reach the backend almost simultaneously.&lt;/p&gt;

&lt;p&gt;Both requests belong to the same user and may attempt to process the same cart.&lt;/p&gt;

&lt;p&gt;Without protection, this could result in duplicate orders or incorrect inventory deductions.&lt;/p&gt;

&lt;p&gt;To handle this, I used PostgreSQL's &lt;code&gt;SELECT ... FOR UPDATE&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;Here is the query:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;users&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nv"&gt;$1&lt;/span&gt;
&lt;span class="k"&gt;FOR&lt;/span&gt; &lt;span class="k"&gt;UPDATE&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;This explicitly locks the selected user's row.&lt;/p&gt;

&lt;p&gt;If another transaction attempts to acquire the same lock, it has to wait until the first transaction commits or rolls back.&lt;/p&gt;

&lt;h3&gt;
  
  
  How does this work?
&lt;/h3&gt;

&lt;p&gt;Suppose the same user sends two checkout requests: A and B.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Transaction A begins.&lt;/li&gt;
&lt;li&gt;Transaction A locks the user's row using &lt;code&gt;SELECT ... FOR UPDATE&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;Transaction B begins and attempts to lock the same user row.&lt;/li&gt;
&lt;li&gt;Transaction B waits.&lt;/li&gt;
&lt;li&gt;Transaction A reads the cart and processes the order.&lt;/li&gt;
&lt;li&gt;Transaction A deducts stock, creates the order and order items, and deletes the user's cart.&lt;/li&gt;
&lt;li&gt;Transaction A commits.&lt;/li&gt;
&lt;li&gt;The lock is released.&lt;/li&gt;
&lt;li&gt;Transaction B acquires the lock and reads the cart.&lt;/li&gt;
&lt;li&gt;The cart is now empty, so the application throws &lt;code&gt;CART_EMPTY&lt;/code&gt;.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;Only one order is created.&lt;/p&gt;

&lt;h3&gt;
  
  
  Flow diagram
&lt;/h3&gt;



&lt;pre data-lang="mermaid"&gt;&lt;code&gt;sequenceDiagram
    participant A as Checkout A
    participant B as Checkout B
    participant DB as PostgreSQL

    A-&amp;gt;&amp;gt;DB: BEGIN
    A-&amp;gt;&amp;gt;DB: SELECT user FOR UPDATE
    Note over DB: User row locked

    B-&amp;gt;&amp;gt;DB: BEGIN
    B-&amp;gt;&amp;gt;DB: SELECT user FOR UPDATE
    Note over DB: B waits for A

    A-&amp;gt;&amp;gt;DB: Read cart
    A-&amp;gt;&amp;gt;DB: Deduct stock
    A-&amp;gt;&amp;gt;DB: Create order and order items
    A-&amp;gt;&amp;gt;DB: Delete cart
    A-&amp;gt;&amp;gt;DB: COMMIT

    Note over DB: Lock released

    DB--&amp;gt;&amp;gt;B: Lock acquired
    B-&amp;gt;&amp;gt;DB: Read cart
    DB--&amp;gt;&amp;gt;B: Cart is empty

    Note over B: CART_EMPTY
    B-&amp;gt;&amp;gt;DB: ROLLBACK&lt;/code&gt;&lt;/pre&gt;



&lt;p&gt;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.&lt;/p&gt;

&lt;h2&gt;
  
  
  5. Putting everything inside a transaction
&lt;/h2&gt;

&lt;p&gt;Both mechanisms are used inside a PostgreSQL transaction.&lt;/p&gt;

&lt;p&gt;A transaction allows multiple database operations to be treated as one logical unit.&lt;/p&gt;

&lt;p&gt;The checkout process follows this general structure:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;BEGIN&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="c1"&gt;-- Lock the user's row&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;users&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="nv"&gt;$1&lt;/span&gt;
&lt;span class="k"&gt;FOR&lt;/span&gt; &lt;span class="k"&gt;UPDATE&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="c1"&gt;-- Read cart items&lt;/span&gt;

&lt;span class="c1"&gt;-- Conditionally deduct product stock&lt;/span&gt;

&lt;span class="c1"&gt;-- Create order&lt;/span&gt;

&lt;span class="c1"&gt;-- Create order items&lt;/span&gt;

&lt;span class="c1"&gt;-- Clear cart&lt;/span&gt;

&lt;span class="k"&gt;COMMIT&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;If something fails at any point, the transaction is rolled back:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;ROLLBACK&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;This matters because we don't want a partially completed checkout.&lt;/p&gt;

&lt;p&gt;For example, if stock is deducted but creating an order item fails, the transaction should not leave the database in an inconsistent state.&lt;/p&gt;

&lt;p&gt;A rollback reverses the changes made within that transaction.&lt;/p&gt;

&lt;h3&gt;
  
  
  Transaction lifecycle
&lt;/h3&gt;



&lt;pre data-lang="mermaid"&gt;&lt;code&gt;flowchart TD
    A[Start checkout] --&amp;gt; B[BEGIN]
    B --&amp;gt; C[Lock user row]
    C --&amp;gt; D[Read cart]
    D --&amp;gt; E{Cart empty?}
    E --&amp;gt;|Yes| R[ROLLBACK]
    E --&amp;gt;|No| F[Deduct stock conditionally]
    F --&amp;gt; G{Stock available?}
    G --&amp;gt;|No| R
    G --&amp;gt;|Yes| H[Create order]
    H --&amp;gt; I[Create order items]
    I --&amp;gt; J[Clear cart]
    J --&amp;gt; K[COMMIT]&lt;/code&gt;&lt;/pre&gt;



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

&lt;h2&gt;
  
  
  6. A look at the implementation
&lt;/h2&gt;

&lt;p&gt;Here is a simplified version of the checkout service:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight typescript"&gt;&lt;code&gt;&lt;span class="k"&gt;import&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt; &lt;span class="n"&gt;pool&lt;/span&gt; &lt;span class="p"&gt;}&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="s2"&gt;"../db/pool"&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;span class="k"&gt;import&lt;/span&gt; &lt;span class="kd"&gt;type&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt; &lt;span class="nc"&gt;PoolClient&lt;/span&gt; &lt;span class="p"&gt;}&lt;/span&gt; &lt;span class="k"&gt;from&lt;/span&gt; &lt;span class="s2"&gt;"pg"&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

&lt;span class="k"&gt;export&lt;/span&gt; &lt;span class="k"&gt;async&lt;/span&gt; &lt;span class="kd"&gt;function&lt;/span&gt; &lt;span class="nf"&gt;createOrder&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;userId&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="kt"&gt;number&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
  &lt;span class="kd"&gt;const&lt;/span&gt; &lt;span class="n"&gt;client&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="nc"&gt;PoolClient&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="k"&gt;await&lt;/span&gt; &lt;span class="n"&gt;pool&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;connect&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

  &lt;span class="k"&gt;try&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
    &lt;span class="k"&gt;await&lt;/span&gt; &lt;span class="n"&gt;client&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;query&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"BEGIN"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

    &lt;span class="c1"&gt;// Prevent simultaneous checkout requests&lt;/span&gt;
    &lt;span class="c1"&gt;// from the same user.&lt;/span&gt;
    &lt;span class="k"&gt;await&lt;/span&gt; &lt;span class="n"&gt;client&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;query&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
      &lt;span class="sb"&gt;`SELECT id FROM users WHERE id = $1 FOR UPDATE`&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
      &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;userId&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
    &lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

    &lt;span class="kd"&gt;const&lt;/span&gt; &lt;span class="n"&gt;cartResult&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="k"&gt;await&lt;/span&gt; &lt;span class="n"&gt;client&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;query&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
      &lt;span class="sb"&gt;`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`&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
      &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;userId&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
    &lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

    &lt;span class="kd"&gt;const&lt;/span&gt; &lt;span class="n"&gt;items&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;cartResult&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;rows&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

    &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;items&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;length&lt;/span&gt; &lt;span class="o"&gt;===&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
      &lt;span class="k"&gt;throw&lt;/span&gt; &lt;span class="k"&gt;new&lt;/span&gt; &lt;span class="nb"&gt;Error&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"CART_EMPTY"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
    &lt;span class="p"&gt;}&lt;/span&gt;

    &lt;span class="k"&gt;for&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="kd"&gt;const&lt;/span&gt; &lt;span class="n"&gt;item&lt;/span&gt; &lt;span class="k"&gt;of&lt;/span&gt; &lt;span class="n"&gt;items&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
      &lt;span class="kd"&gt;const&lt;/span&gt; &lt;span class="n"&gt;stockResult&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="k"&gt;await&lt;/span&gt; &lt;span class="n"&gt;client&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;query&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;
        &lt;span class="sb"&gt;`UPDATE products
         SET stock = stock - $1
         WHERE id = $2 AND stock &amp;gt;= $1
         RETURNING id`&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
        &lt;span class="p"&gt;[&lt;/span&gt;&lt;span class="n"&gt;item&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;quantity&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;item&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;product_id&lt;/span&gt;&lt;span class="p"&gt;]&lt;/span&gt;
      &lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

      &lt;span class="k"&gt;if&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;stockResult&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;rowCount&lt;/span&gt; &lt;span class="o"&gt;===&lt;/span&gt; &lt;span class="mi"&gt;0&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
        &lt;span class="k"&gt;throw&lt;/span&gt; &lt;span class="k"&gt;new&lt;/span&gt; &lt;span class="nb"&gt;Error&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"INSUFFICIENT_STOCK"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
      &lt;span class="p"&gt;}&lt;/span&gt;
    &lt;span class="p"&gt;}&lt;/span&gt;

    &lt;span class="c1"&gt;// Calculate total and create the order.&lt;/span&gt;
    &lt;span class="c1"&gt;// Insert order items.&lt;/span&gt;
    &lt;span class="c1"&gt;// Clear the user's cart.&lt;/span&gt;

    &lt;span class="k"&gt;await&lt;/span&gt; &lt;span class="n"&gt;client&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;query&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"COMMIT"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

  &lt;span class="p"&gt;}&lt;/span&gt; &lt;span class="k"&gt;catch&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;error&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
    &lt;span class="k"&gt;await&lt;/span&gt; &lt;span class="n"&gt;client&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;query&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="s2"&gt;"ROLLBACK"&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
    &lt;span class="k"&gt;throw&lt;/span&gt; &lt;span class="n"&gt;error&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;

  &lt;span class="p"&gt;}&lt;/span&gt; &lt;span class="k"&gt;finally&lt;/span&gt; &lt;span class="p"&gt;{&lt;/span&gt;
    &lt;span class="n"&gt;client&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="nf"&gt;release&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
  &lt;span class="p"&gt;}&lt;/span&gt;
&lt;span class="p"&gt;}&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;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.&lt;/p&gt;

&lt;p&gt;There are two important locking mechanisms here:&lt;/p&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;Mechanism&lt;/th&gt;
&lt;th&gt;Purpose&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;&lt;code&gt;SELECT ... FOR UPDATE&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;Prevents simultaneous checkout operations for the same user&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Conditional &lt;code&gt;UPDATE&lt;/code&gt;
&lt;/td&gt;
&lt;td&gt;Prevents stock from being deducted below zero&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;These solve two different problems.&lt;/p&gt;

&lt;p&gt;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.&lt;/p&gt;

&lt;h2&gt;
  
  
  7. Testing both concurrency scenarios
&lt;/h2&gt;

&lt;p&gt;Implementing concurrency handling is one thing. Verifying that it actually works is another.&lt;/p&gt;

&lt;p&gt;I created a concurrency test script to simulate both situations.&lt;/p&gt;

&lt;h3&gt;
  
  
  Test 1: Different users competing for the last unit
&lt;/h3&gt;

&lt;p&gt;Initial conditions:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Two different users.&lt;/li&gt;
&lt;li&gt;One product.&lt;/li&gt;
&lt;li&gt;Stock = 1.&lt;/li&gt;
&lt;li&gt;Both users attempt checkout.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Expected result:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;One checkout succeeds.&lt;/li&gt;
&lt;li&gt;The other returns &lt;code&gt;INSUFFICIENT_STOCK&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;Final stock = 0.&lt;/li&gt;
&lt;li&gt;Exactly one order is created.&lt;/li&gt;
&lt;/ul&gt;

&lt;h3&gt;
  
  
  Test 2: Same user submitting checkout twice
&lt;/h3&gt;

&lt;p&gt;Initial conditions:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;One user.&lt;/li&gt;
&lt;li&gt;Two checkout requests.&lt;/li&gt;
&lt;li&gt;Cart contains a product with quantity 1.&lt;/li&gt;
&lt;li&gt;Product stock = 5.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Expected result:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;One checkout succeeds.&lt;/li&gt;
&lt;li&gt;The second returns &lt;code&gt;CART_EMPTY&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;Final stock = 4.&lt;/li&gt;
&lt;li&gt;Exactly one order is created.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Both tests help verify that the database operations behave correctly under concurrent requests.&lt;/p&gt;

&lt;h2&gt;
  
  
  8. What I learned
&lt;/h2&gt;

&lt;p&gt;Working on these scenarios helped me understand several important backend and database concepts:&lt;/p&gt;

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

&lt;p&gt;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.&lt;/p&gt;

&lt;h2&gt;
  
  
  Conclusion
&lt;/h2&gt;

&lt;p&gt;Building Acheter Hub gave me an opportunity to go beyond basic CRUD operations and work with transactional database operations.&lt;/p&gt;

&lt;p&gt;The two scenarios taught me how to approach concurrency from different angles:&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;Different users, same product:&lt;/strong&gt; Conditional stock updates and PostgreSQL row-level locking prevent overselling.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Same user, simultaneous checkout:&lt;/strong&gt; Explicit user-row locking prevents duplicate checkout processing.&lt;/li&gt;
&lt;/ol&gt;

&lt;p&gt;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.&lt;/p&gt;




&lt;p&gt;&lt;strong&gt;Project:&lt;/strong&gt; Acheter Hub&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;Live Demo: &lt;a href="https://acheterhub.vercel.app/" rel="noopener noreferrer"&gt;https://acheterhub.vercel.app/&lt;/a&gt;
&lt;/li&gt;
&lt;li&gt;GitHub Repository: &lt;a href="https://github.com/tanqdev/Acheter" rel="noopener noreferrer"&gt;https://github.com/tanqdev/Acheter&lt;/a&gt;
&lt;/li&gt;
&lt;/ul&gt;

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

&lt;h1&gt;
  
  
  postgresql #nodejs #backend #webdev #typescript #database #concurrency #buildinpublic
&lt;/h1&gt;

</description>
      <category>webdev</category>
      <category>programming</category>
      <category>javascript</category>
      <category>discuss</category>
    </item>
  </channel>
</rss>
