Hot Rows, Random Selection and OCC: Aurora DSQL
In my previous article, No Locks, Still Conflicts, I explored why concurrent transactions can conflict in Aurora DSQL.
Lycore left a thoughtful comment about retrying transactions and keeping external side effects outside the retry loop. They also suggested splitting inventory across multiple counter rows for popular products.
That suggestion gave me something useful to explore.
How would we divide the stock? Which bucket should each request update? And how could we check that concurrent requests do not oversell?
I built a small demo to understand this.
When one inventory row becomes busy
Imagine a product with 40 units stored in one row. Every purchase updates that same row.
When many requests arrive together, they compete to modify it. This is the hot row problem.
Aurora DSQL uses optimistic concurrency control. Transactions can proceed concurrently, but conflicting writes are checked at commit. A transaction can receive SQLSTATE 40001 when another transaction modifies the same row and commits first. AWS concurrency documentation
There may still be plenty of stock. A transaction conflict does not mean the product is sold out.
While researching this, I read Building scalable applications on Amazon Aurora DSQL by Tejas Dubey, Edigar Chikwama and Rajesh Kantamani.
Their discussion helped me understand how schema design affects contention. Separating independently updated information can prevent unrelated operations from competing over one row.
For inventory, we have another situation. Many requests need to update the same stock counter.
Dividing inventory into buckets
For the demo, I divided 40 units across four rows, with 10 units in each bucket.
The table uses (product_id, bucket_id) as its primary key.
Each successful decrement updates one bucket. To read the product’s remaining stock, we sum the buckets. We do not maintain a separate total row that every request must update.
This is a form of counter sharding. I’ll call it inventory bucketing here.
These are logical buckets within one database. Four bucket rows do not guarantee four separate physical storage shards.
Creating buckets is only part of the solution
The application first reads buckets that still have stock:
SELECT bucket_id
FROM inventory.buckets
WHERE product_id = $1 AND remaining > 0
ORDER BY bucket_id;We could choose the first result, but consider what happens when several requests do that together.
They all choose bucket 0.
We would recreate contention on one row while the other buckets still had stock.
Instead, the application randomly chooses from the available buckets:
const bucketId =
candidates.rows[randomInt(candidates.rows.length)].bucket_id;Splitting inventory across rows only helps if we also spread writes across those rows.
Random selection does not guarantee that requests choose different buckets. Conflicts remain possible.
As buckets empty, fewer choices remain. If only one bucket has stock, requests compete over that row again.
Two ways to perform the decrement
Get Anuradha Ranasinghe’s stories in your inbox
Join Medium for free to get updates from this writer.
The first implementation selects available buckets, chooses one in JavaScript, then updates it:
UPDATE inventory.buckets
SET remaining = remaining - 1
WHERE product_id = $1
AND bucket_id = $2
AND remaining > 0
RETURNING remaining;The selection and update run inside the same transaction.
This makes the steps easy to follow. However, it requires two query round trips between Lambda and DSQL. Lambda waits for the candidate list before sending the update.
We can also move the selection into the update statement:
UPDATE inventory.buckets
SET remaining = remaining - 1
WHERE product_id = $1
AND remaining > 0
AND bucket_id = (
SELECT bucket_id
FROM inventory.buckets
WHERE product_id = $1 AND remaining > 0
ORDER BY random()
LIMIT 1
)
RETURNING
bucket_id AS "bucketId",
remaining AS "bucketRemaining";This removes one query round trip on a successful decrement.
DSQL still needs to find eligible buckets and perform random selection. Combining the statements does not automatically mean less database work.
DSQL documents support for the required SQL constructs, and we tested this statement against the deployed database. Supported SQL documentation
Both implementations use the connector’s bounded transaction retries. If an OCC conflict aborts an attempt, the entire transaction runs again, including bucket selection.
We only acknowledge a successful decrement after the transaction commits.
Testing concurrent requests
The demo uses API Gateway, separate Lambda functions for the two approaches, and Aurora DSQL.
I ran the functional API checks through Newman, then used ApacheBench to send concurrent HTTP requests through API Gateway.
For each approach, I prepared four buckets with 10 units each and sent 60 requests at concurrency 5.
ResultTwo queriesSingle querySuccessful decrements4040Out of stock responses2020Other errors or transport failures00Final stock00Total duration6.72 seconds5.75 secondsMedian latency422 ms410 msp95 latency1,003 ms500 ms
The stock check mattered as much as the timing. The starting stock minus the final stock must equal the number of successful decrements.
All four buckets finished at zero. No overselling occurred in either run.
The single query version was faster in this run. Removing a round trip is a plausible contributor, but cold starts, network conditions and execution order can also affect the result.
This was a small experiment. We need repeated tests before claiming a consistent performance advantage.
An earlier run also reminded me to check Lambda concurrency. Our account’s regional limit was 10, and a test at concurrency 20 caused invocation throttling. API Gateway can return HTTP 500 when Lambda rejects an invocation. Those responses should not be mistaken for DSQL conflicts. AWS error handling documentation
Try it in your own AWS account
Github — https://github.com/anuradha151/dsql-hot-row-contention
You can have this serverless demo running in a few minutes. The CDK code is already provided, so you do not need to write infrastructure code.
Clone Github dsql-hot-row-contention repo, open the project directory, and follow the README’s prerequisites and initial AWS setup.
Then install and deploy:
npm cinpx cdk deploy DsqlInventoryDemo --profile YOUR_PROFILE --region YOUR_REGIONThe deployment creates the API, Lambda functions, database and initial inventory.
The README includes commands to set different quantities in individual buckets, test both approaches and inspect the results. Check your regional Lambda concurrency quota before increasing the ApacheBench concurrency.
Aurora DSQL currently includes 100,000 DPUs and 1 GB of storage free each month. Other AWS services can incur charges, so this is not a promise of a completely free deployment. AWS pricing details
When finished, remove the demo stack:
npx cdk destroy DsqlInventoryDemo --profile YOUR_PROFILE --region YOUR_REGIONThe README explains what gets deleted and which CDK bootstrap resources remain. This is an unauthenticated educational demo, so use a disposable test environment.
Thank you to Lycore for suggesting this direction, and to the AWS authors whose explanations helped me explore it.
Which approach would you choose here: selecting the bucket in the application, or combining selection and decrement in one SQL statement? Why?
AWS documentation reviewed on 3 November 2026.
