No Locks, Still Conflicts: Understanding Aurora DSQL

While exploring Aurora DSQL, I wanted to understand what happens when two transactions change the same data.

Consider an inventory record. Two requests try to reserve stock for the same product at the same time.

How does the database decide which changes to accept?

Pessimistic and optimistic approaches

With pessimistic locking, a transaction acquires a lock to protect its work. Conflicting operations may need to wait.

With optimistic concurrency control, transactions proceed without holding those locks. Conflicts are checked before their changes are committed.

Aurora DSQL uses optimistic concurrency control, or OCC. Transactions work against a consistent snapshot, and DSQL checks for conflicts at commit time. AWS concurrency overview

For conflicting updates to the same row, the transaction that commits first succeeds. The other can receive:

ERROR: change conflicts with another transaction (OC000)
SQLSTATE: 40001

The database detects the conflict. Our application still needs to handle the outcome. AWS conflict documentation

Press enter or click to view image in full size

A single reservation attempt

Assume pool is an existing DSQL connection pool and product_id uniquely identifies one inventory row.

This function makes one attempt, with no automatic retry.

async function reserveOne(productId) {
  const client = await pool.connect();
  let discardConnection = false;
}

try {
    await client.query("BEGIN");
    const result = await client.query(
      `UPDATE inventory
       SET remaining = remaining - 1
       WHERE product_id = $1 AND remaining > 0
       RETURNING remaining`,
      [productId],
    );
    await client.query("COMMIT");
    return { reserved: result.rowCount === 1 };
  } catch (error) {
    try {
      await client.query("ROLLBACK");
    } catch {
      discardConnection = true;
    }
    throw error;
  } finally {
    client.release(discardConnection);
  }
}

The conditional update prevents a reservation when no stock remains.

After a successful commit, reserved: true means one matching row was updated. reserved: false means no row matched: either the product was missing or its stock was unavailable.

A database failure is passed to the caller after cleanup. If rollback fails, the connection is discarded rather than returned to the pool.

All transaction statements use the same connection, following the node-postgres transaction pattern.

Different columns can still conflict

This was an important detail in my evaluation.

Suppose one transaction updates remaining, while another updates warehouse_note on the same inventory row.

Even though they change different columns, their concurrent updates can conflict. DSQL detects these write conflicts at the row level. AWS conflict matrix

So 40001 does not mean “out of stock.”

Stock might still be available. The application could instead return a conflict response:

“Inventory changed while processing your request. Please try again.”

Returning immediately is a valid choice. But for this reservation flow, returning every conflict to the customer may create avoidable failures.

A bounded retry can give the entire transaction another chance using a fresh snapshot.

In the next article, I’ll add that retry loop and explain which failures we should retry.

This code is illustrative and has not been tested against a live DSQL cluster.

Would you retry this reservation automatically, or ask the customer to try again?