Backend Engineering Interview Concepts for Production - Part 3: Transactions and Isolation on AWS
Learn how database transactions, ACID, and isolation levels protect your app when two customers try to buy the last item. Follow clear examples using Amazon RDS, Aurora, and DynamoDB to understand how concurrent requests can succeed safely.
Series: Backend Interview Concepts to Know for Production (4 articles)
- …
- 3Backend Engineering Interview Concepts for Production - Part 3: Transactions and Isolation on AWS This article
A database transaction groups related operations so they all succeed or all fail together. Isolation controls what one transaction can see while another is still running. Together they decide what happens when two people try to buy the same item at the same moment. On AWS, managed databases such as Amazon RDS for PostgreSQL, Amazon Aurora, and Amazon DynamoDB enforce these guarantees, and your code picks which rule to apply.
Two customers. One bag of coffee left. Both click Buy.
Who gets it?
Please don’t say both. Your database cannot manifest additional coffee through positive thinking.
Alice sees “1 bag left.” Bob sees the same thing. They submit their orders almost simultaneously. A badly designed checkout can tell both customers they’ve bought the last bag.
That’s a race condition, and grounds for a refund.
This is part three of Backend Engineering Interview Concepts for Production. We’ll follow those two requests through the app, explain transactions and isolation as they come up, and finish with how to handle this on AWS.
What are transactions and isolation?
A database transaction groups operations into one logical unit. Its all-or-nothing property means a checkout’s related database changes can succeed together or be undone together.
Isolation governs what transactions can observe and how their work interacts when they overlap. The isolation level determines which kinds of interference the database prevents.
For our coffee shop, we need to answer two questions:
- What if reducing the stock succeeds, but saving the order fails?
- What if two checkouts both try to claim the same remaining stock?
Those are different failure modes. We need to handle both.
First, seeing “one left” doesn’t reserve it
Alice and Bob’s phones aren’t the inventory database. They’re displaying information the app retrieved earlier. Even a perfectly accurate response can become out of date before the next tap.
Clicking Buy sends a purchase request. It does not, by itself, complete a purchase.
The starting point:
Place | What it currently says |
|---|---|
Alice’s screen | One coffee bag available |
Bob’s screen | One coffee bag available |
Inventory record in the database | available = 1 |
Orders table | No order for either customer yet |
The backend code must check availability and claim the stock safely in the database before confirming an order. Everything in this story is automated: the website sends requests, backend code handles them, and the database enforces the operations. No employee is manually reviewing Bob’s caffeine requirements.
For now, “purchase succeeds” means the inventory change and order record are saved. We’ll return to actual card payments later; a bank is a separate system.
What does a transaction protect?
Pause Bob’s request and follow Alice’s.
Her checkout needs to change two things:
- Reduce the available coffee bags from one to zero.
- Create an order record saying Alice ordered one bag.
Imagine saving those independently. The inventory update succeeds. Then the order insert fails.
The database now says there’s no coffee available, but there’s no order explaining where the bag went. The coffee has entered witness protection.
A transaction gives those related changes one outcome. Commit means successfully finish the transaction and save its changes. Rollback means undo its uncommitted changes. If creating Alice’s order fails and the transaction is rolled back, the stock reduction is undone too. PostgreSQL’s transaction introduction explains this all-or-nothing behavior.
The same idea applies to transferring $20 between two balances held in one database: subtract $20 from one account and add $20 to the other, together. Saving only the subtraction would be a rather unpopular banking feature.
This all-or-nothing property is called atomicity. It’s one of the four properties in ACID:
Term | Meaning in our coffee shop |
|---|---|
Atomicity | Save the stock change and order together, or undo both. |
Consistency | Preserve the rules we’ve defined, such as a constraint preventing negative stock. The database cannot invent missing business rules. |
Isolation | Control how overlapping checkouts observe and interact with database changes. |
Durability | Once the database confirms a successful commit, its saved changes survive a database process crash. |
Isolation is already part of a transaction’s behavior. We’re separating the explanations so we can understand what each property does. Writing
BEGIN doesn’t automatically select the strongest isolation level.What is isolation, and why can two transactions still go wrong?
Bring Bob back into the story.
Alice’s transaction reads one bag available. Before it finishes, Bob’s transaction also reads one bag available. Their work overlaps; developers call this concurrency.
An isolation level sets the rules for that overlap. Can Bob read Alice’s unfinished changes? Can a value change between two of his reads? What happens if their decisions conflict?
Consider one broken approach: each checkout reads
1 , calculates 1 − 1 in application code, then tells the database to set the stock to 0 and inserts its own order.Moment | Alice’s checkout | Bob’s checkout |
|---|---|---|
Read | Reads 1 | Reads 1 |
Calculate | Plans to write 0 | Plans to write 0 |
Save with insufficient protection | Writes 0 ; creates Alice’s order | Also writes 0 ; creates Bob’s order |
Final result | Stock: 0. Orders: 2. Coffee bags sold: 1 too many. | |
Both checkouts could save all their changes. Neither is half-finished. Yet together they produce a result the business can’t honor.
Even a “stock must never be negative” constraint wouldn’t catch this particular bug: zero passes that test. That’s why the exact update logic matters alongside the isolation level.
What is Serializable isolation?
Serializable isolation makes the outcome of successfully committed transactions equivalent to some order in which they ran one at a time. The actual work can still overlap. PostgreSQL can reject a transaction when allowing it to finish would break that guarantee. See its transaction settings .
Imagine our correctly written checkout running one customer at a time:
- Alice goes first: she checks stock, buys the bag, and leaves zero. Bob then checks stock and cannot buy it.
- Bob goes first: he buys the bag. Alice then finds zero.
There’s no valid turn-taking order in which both buy the only bag, provided each checkout checks availability and obeys that result.
Serializable protects that kind of reasoning. It won’t fix checkout code that ignores stock entirely. Incorrect code running one at a time is still incorrect, just more politely organized.
What actually happens to Bob’s order?
Suppose both transactions use PostgreSQL’s Serializable isolation and both have read
1 . Consider one possible execution, with Alice succeeding:- Alice changes the inventory and creates her order inside her transaction.
- Alice’s transaction commits. The saved inventory is now zero, and her order exists.
- Bob’s attempt to make a conflicting update fails. His transaction cannot commit, and the database rolls back any changes from that failed attempt.
- The backend starts Bob’s whole transaction again, including the stock check.
- This new attempt reads zero, creates no order, and returns “Sold out.”
Bob doesn’t have a confirmed order that silently disappears. His first database attempt never successfully completed. His website can show “Processing…” until the backend has a result to return.
Alice gets coffee. Bob gets character development.
First click doesn’t decide the winner. Browser timing, network delays, and database scheduling all matter. Serializable guarantees a valid outcome, not a fair queue of customers.
A serialization failure also doesn’t mean “sold out.” It means the attempted transaction couldn’t safely finish. The fresh stock check establishes whether anything remains. PostgreSQL requires the application to retry the complete transaction, including decisions based on its reads, not just repeat the final failed statement. In production, those retries need limits and an overall deadline. PostgreSQL retry guidance
Are there other isolation levels?
Yes. This is a beginner-friendly view, specific to PostgreSQL. Similar names can mean different behavior in other database engines.
Level | What to remember |
|---|---|
Read Uncommitted | The SQL name permits reading unfinished changes. PostgreSQL treats it as Read Committed instead. |
Read Committed | The usual PostgreSQL default. Each ordinary query sees committed data as of that query’s start. A later query may see newer committed changes. |
Repeatable Read | Ordinary reads share a stable snapshot, while still seeing the transaction’s own changes. Some conflicting updates require a retry; not every multi-record business rule is protected. |
Serializable | Adds protection so committed transactions have an outcome equivalent to running them one at a time. Conflicting work may need a retry. |
PostgreSQL also prevents phantom reads at Repeatable Read, even though the SQL standard permits them there. This is why checking your engine’s isolation semantics matters more than memorizing a generic table.
The interview vocabulary becomes easier when you attach each term to a small story:
- Dirty read: Bob reads Alice’s unfinished stock change to zero. Alice later rolls back. Bob relied on a change that never became final. PostgreSQL prevents this at all its supported levels.
- Non-repeatable read: Bob reads stock as one, Alice commits a purchase, and Bob’s next read in the same transaction returns zero. The same row changed between reads.
- Phantom read: A transaction queries available seats, then repeats the query and gets a different set because another transaction committed a change. Think changing query results, not haunted SQL.
- Lost update: Two workers read stock as ten. Each calculates nine and writes nine. Two intended decrements happened in the code, but only one remains in the saved value.
Do I need Serializable for every checkout?
No. Start with the rule you need to preserve, then choose an appropriate mechanism.
For a single inventory counter, an atomic conditional update can be enough to claim stock safely. This short SQL fragment expresses the idea:
UPDATE inventorySET available = available - 1WHERE product_id = 'coffee-001' AND available > 0RETURNING available;It means: “Subtract one from this product’s stock, but only if at least one is available.” The check and decrement belong to one database statement.
At PostgreSQL’s Read Committed level, the database coordinates competing updates to this row. After waiting for a competing update to commit, the waiting update rechecks its condition against the changed row. Alice can reduce one to zero; Bob then updates no row. PostgreSQL’s Read Committed behavior
Create the order only if that update returned a row, and put both changes in the same transaction. Otherwise you can still create an order without claiming stock. This is an illustration of the database rule, not a complete checkout implementation.
Another option is a row lock. With Read Committed and
SELECT … FOR UPDATE , Alice can lock the inventory row while checking and updating it. Bob’s competing locking read waits; after Alice commits, it can read the updated stock. Ordinary readers aren’t all blocked by that row lock. PostgreSQL row locking 
You don’t need to stack every mechanism onto every request. A single counter, a unique username, and a rule spanning several records are different problems.
Same problem, different app: seats and usernames
Two customers reserve the last concert seat. Both may see Seat A12 as available. The backend must claim it safely. One design updates the seat only while its status is “available.” A uniqueness rule on the event-and-seat pair can also prevent duplicate active claims when the reservation data model supports it. Browsing a seat map is not a reservation, regardless of how emotionally attached Bob has become to the legroom.
Two people claim the same username. Both may pass an “Is this available?” check. The final insert needs a database uniqueness constraint on the chosen username representation. One claim succeeds; the database rejects the competing duplicate, and the app asks that person to choose another name. PostgreSQL supports both single-column and multi-column unique constraints .
These examples share a concurrency problem, but don’t all require Serializable. Often the clearest protection is a rule enforced directly at the write.
And none of this activates only when stock is low. The same safeguards apply with one bag or one thousand. Low stock simply makes the bug easier to notice.
What does stronger protection cost?
More protection can mean waiting, conflict detection, retries, or fewer operations making progress at once. The practical guidance is to keep transactions short and choose protection based on the business rule you must preserve.
Design choice | What you gain | What you pay for |
|---|---|---|
Conditional update | A small, direct way to protect one write | It doesn’t express every rule involving multiple records. |
Row locking | Competing writers coordinate around the same row | Waiting; long-held locks can become a bottleneck or contribute to deadlocks. |
Serializable | Reason about committed results as if transactions took turns | Conflict tracking and the work of retrying aborted transactions. |
Unique constraint | Database-enforced uniqueness | The app must handle a rejected duplicate as an expected outcome. |
“Short” means doing the database work promptly. Don’t keep a transaction open while a customer fills out a form or an external service takes a leisurely coffee break. For locking designs, acquire multiple locks in a consistent order to reduce deadlocks. PostgreSQL locking guidance
Also distinguish database errors from business outcomes. “Try this transaction again” and “no stock remains” should lead to different application behavior.
How does this work on AWS?
AWS gives you managed databases that enforce these operations, plus services to run your checkout code. Your job is to express the purchasing rule and handle the outcomes.
Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL-Compatible
For our relational coffee shop, one request might travel through these services:
Service | Its job in this example |
|---|---|
Amazon API Gateway | Receive the checkout API request and pass it to backend code. |
AWS Lambda | Run that code: begin the transaction, issue database operations, and handle the result. |
Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible | Store inventory and orders, and enforce database transactions, locks, and constraints. |
Lambda can receive requests through API Gateway integration . Each checkout uses a database connection for its transaction; running in Lambda does not create that transaction for you.
With the Serializable approach, the application explicitly selects that level before its transaction’s reads and writes, then retries serialization failures appropriately. With the conditional-update approach, it checks whether stock was claimed before inserting the order.
The AWS value is the managed database operation around this: services such as RDS handle work including backups and database maintenance, while availability features depend on the deployment you select. Aurora PostgreSQL-Compatible provides a managed PostgreSQL-compatible option as well. You still design the database rules. Amazon RDS overview , Aurora PostgreSQL overview
For a Lambda workload with many short database connections, Amazon RDS Proxy can pool connections and help avoid exhausting the database’s connection capacity. It helps manage access to the database; the transaction logic determines whether an order may be saved. Using Lambda with RDS
Amazon DynamoDB: express the condition in the write
For an app using DynamoDB, a conditional update can express the stock rule directly:
Update this product: subtract 1 from available only if available >= 1DynamoDB evaluates the condition as part of the write, so two requests cannot both reduce the same stock value of one. That avoids a separate application-level “read, decide, then blindly write” gap. DynamoDB condition expressions
But that operation changes only the inventory item. To also create an order item, DynamoDB offers TransactWriteItems:
One transaction: UPDATE inventory, with the condition available >= 1 PUT the new order
Both actions succeed, or neither changes the database.Put the stock condition on the update itself. The order and inventory are different items, and can be in different tables in the same account and Region. DynamoDB provides serializable isolation between these transactions. A cancellation can mean a failed condition, a transaction conflict, or another error; the backend must inspect the reason rather than report every cancellation as “sold out.” Conflicts may warrant a bounded retry. DynamoDB transaction behavior
DynamoDB transactions also consume additional read/write work compared with individual operations. Its transaction capacity guidance explains the prepare-and-commit work. Use them when those items need one shared outcome.
The boundary: a database rollback cannot uncharge a card
An RDS transaction or DynamoDB transaction covers its database operations. It doesn’t automatically include a payment provider, an email, or another external service.
If a card charge succeeds and the database work later fails, a rollback doesn’t reach into the payment provider and undo the charge.
That’s where part one, idempotency, comes back. Use the payment provider’s supported idempotency mechanism, keep track of payment and order states, and plan how to recover or compensate when one step succeeds and another fails. A reservation-then-payment workflow is one possible design, with its own expiry and recovery rules.
AWS describes this broader coordination problem in its saga pattern guidance . It deserves its own explainer; a database transaction should not be stretched into a promise about the whole internet.
How I’d explain it in an interview
A transaction groups database operations into one logical unit. Atomicity keeps the stock update and order creation together. Isolation controls interference between concurrent checkouts. I’d identify the business rule, use an appropriate conditional update, lock, constraint, or isolation level to enforce it, and handle transaction failures correctly. External payments need separate coordination and idempotency.
Start with the failure you’re preventing. Then explain the mechanism and its cost.
For our coffee shop, the goal is simple: two people can want the last bag. Only one confirmed order can claim it.
The database doesn’t need to settle who deserves coffee more. It just needs to keep the promises your checkout makes.
Series: Backend Interview Concepts to Know for Production (4 articles)
- …
- 3Backend Engineering Interview Concepts for Production - Part 3: Transactions and Isolation on AWS This article
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article