Database Transactions & ACID Guarantees
Millions of fans. One perfect seat. Learn how ACID transactions prevents double-sales, freeze time like referees, and beat goldfish memory 🐠.
Your favorite concert
Think about your favorite artist and the concert you have always wanted to attend.
Your favorite song. One unforgettable night out with friends!
Ticket sales start at 10:00 AM. Millions of fans rush to get tickets.
One payment succeeds. Another fails halfway through. A server crashes. Someone gets charged but never receives a ticket.
And despite all this chaos, the system still has to answer one critical question:
Will you get into the concert with your friends?
This is the problem transactions were created to solve.
Behind the concert website
Ticket sales open.
The page freezes.
You wait for it to load and keep refreshing.
Your group chat suddenly lights up:
“I GOT MINE!”
You click purchase, but nothing happens - you try again.
Some fans get charged twice.
Others lose their tickets after payment.
Meanwhile, tickets are disappearing every second.
And despite millions of people rushing in simultaneously, the system still has to keep everything correct.
Because once the concert starts, nobody wants to hear:
“Sorry, your ticket does not exist.”
Transactions were created to keep systems from ending up in this kind of chaos.
Pinky Promise with ACID ✦
Making an all-or-nothing promise is easy.
Keeping that promise reliably for every single purchase is the hard part.
Instead of forcing applications to deal with partial failures themselves, databases handle much of this complexity through four guarantees known as the ACID properties:
- Atomicity
- Consistency
- Isolation
- Durability
Atomicity: The All-or-Nothing Pact
The first guarantee is Atomicity.
The word atomic comes from the idea of something indivisible — something that cannot be broken down further.
In databases, Atomicity means that multiple related actions can be grouped together and treated as one single unit. This grouping is called a transaction.
The database guarantees that either everything inside the transaction succeeds together, or none of it happens at all.
For a fan trying to buy a ticket, a transaction may include:
- Processing the payment with the bank.
- Reserving the exact seat in the arena.
- Generating the confirmation code.
Either all of these actions must succeed together.
Or none of them should happen at all.
You should not get charged without receiving a ticket.
And the system should not reserve a ticket if the payment failed.
Atomicity removes chaos from the programming model by preventing partial states.
If the transaction succeeds, the database commits it.
If something fails midway, the database rollsback entire transaction as if it never happened at all.
The application can safely retry again without worrying about partial failures.
Example Transaction
Here is a simplified example of how applications group multiple operations into a database transaction:
BEGIN; -- Transaction begins
UPDATE seats SET reserved = true WHERE seat_id = 'A12'; -- Reserve the concert seat
INSERT INTO payments (...); -- Charge the fan
INSERT INTO tickets (...); -- Generate the concert ticket
COMMIT; -- Permanently save all changes if everything succeeds
-- If any step fails before COMMIT,
-- the database performs a ROLLBACK
-- and erases the entire transactionInsight: A transaction is like a parent taking their child to a concert. If the parent cannot go, the child cannot go alone. And if the child gets sick, the parent stays home too. Either everyone walks through the gate together, or the whole night is canceled.
Consistency: The Rules Must Stay True
Atomicity prevents a purchase from stopping halfway.
Consistency ensures that once the transaction finishes, the system has not broken its own rules.
For the concert system, following rules are absolute:
- Total tickets sold cannot exceed venue capacity.
- One seat cannot belong to two different fans.
- A confirmed ticket must be linked to a valid purchase.
These unbreakable rules are called invariants.
A transaction is allowed to change the data, but it should move the database from one valid state to another.
However, Consistency is still a shared responsibility between the application and the database.
The database can only enforce the rules you explicitly declare.
If the application writes logically incorrect data that still passes all constraints, the database cannot magically detect the mistake.
Example Constraints and Triggers
The database acts as a line of defense by enforcing rules through:
- constraints for simple validation
- triggers for more complex validation
| Constraint | Trigger |
|---|---|
| PRIMARY KEY uniquely identifies each ticket | Prevent overselling stop purchases after venue capacity is reached |
| UNIQUE prevents selling the same seat twice | Auto confirmation generate confirmation after successful purchase |
| NOT NULL prevents missing important data | Restrict cancellation prevent cancellation after concert starts |
| CHECK enforces validation rules like positive ticket price | Audit logging track payment status changes |
| FOREIGN KEY ensures tickets belong to a valid concert | State validation reject impossible states like refunded + active |
Insight: The database is like a calculator — it can correctly calculate the ticket price, but it cannot know whether the application accidentally gave every fan the VIP discount.
Isolation: Millions of Fans Clicking at Once
You have been to concerts before.
You already know the perfect seat - one that is close to your artist and lets you feel the vibration of crowd.
Luckily, you spot the perfect seat and instantly click Buy.
At the same time, another fan browsing the concert decides to buy the very same seat too.
Both of you proceed to buying because it is available.
But there is only one physical chair.
The database must somehow ensure that only one ticket for the perfect seat is sold.
This is the problem Isolation was created to solve.
Isolation prevents both of you from successfully purchasing the exact same ticket.
When multiple transactions compete for the same thing at the same time, they are called concurrent transactions.
Isolation acts like a referee — it safely decides who actually gets the ticket.
As soon as your transaction starts reserving the seat, the competing purchase from the second fan is temporarily forced to wait.
Once your purchase fully completes, the database reveals the final result to the waiting transaction:
The seat is gone.
The second purchase is safely rejected, preventing the same physical chair from being sold twice — and you walk away with the best seat in the arena.
Some textbooks call this idea serializability — meaning the database behaves as if it is only doing one thing at a time, even while handling many transactions simultaneously behind the scenes.
Common Isolation Levels
Different isolation levels provide different tradeoffs between correctness and performance.
READ UNCOMMITTED 🐇
Other fans may temporarily see a seat marked as sold even before the purchase fully finishes.
Performance Impact: Fastest, but least reliable.
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
COMMIT;READ COMMITTED ⭐
Fans only see the seat as sold after the purchase successfully completes.
Performance Impact: Good balance of speed and safety.
Most Common: Default isolation level in many popular databases.
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
COMMIT;REPEATABLE READ
The same fan keeps seeing stable seat information throughout the transaction.
Performance Impact: More stable reads, but higher locking cost.
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
COMMIT;SERIALIZABLE 🐢
The database behaves like a referee and safely gives the seat to only one fan.
Performance Impact: Strongest correctness, but slowest concurrency.
Safest Option: Best correctness guarantees for highly competitive transactions.
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
COMMIT;Insight: Isolation is like two cars trying to squeeze into the last parking spot at a concert at the exact same moment.
In real life, you get a shouting match. In a database, Isolation acts like an invisible referee that freezes time, awards the spot to one driver, and sends the other away.
Durability: Your Ticket Must Still Exist on Concert Night
Isolation finally gave you the perfect seat. You won the high-speed race.
Weeks later, concert night finally arrives.
You stand in a massive line surrounded by thousands of excited fans. The stadium lights are flashing, the bass is vibrating through the pavement, and your favorite artist is minutes away from stepping onto the stage.
The staff scans your ticket.
❌ Ticket not found.
What do you mean, “not found”?
You even received the confirmation email weeks ago.
That ticket must exist.
This is the exact nightmare Durability was created to prevent.
Durability guarantees that once a transaction is successfully committed, the data is permanently written to storage. It should survive crashes, power failures, restarts, and pretty much anything except the asteroid that ended the dinosaurs.
Once the database tells the application the purchase succeeded, it can never suddenly “forget” your ticket later.
How To Enable Durability
Surprise: you already saw this earlier in the Atomicity section.
That same COMMIT statement is also what activates Durability.
Once the database confirms a transaction with COMMIT, the database takes responsibility for remembering that data safely — even after crashes or restarts.
BEGIN;
INSERT INTO tickets (...);
COMMIT; -- Database now guarantees durabilityThe durability machinery is usually built directly into the database through logs, crash recovery systems, and persistent storage engines.
Insight: Durability is the database’s long-term memory. Once it promises your ticket exists, it should remember that purchase forever — not forget it three seconds later like a goldfish 🐠.
Note to Future Me 🐠
Transaction
A logical unit of work that groups multiple database actions together into one indivisible operation.
Transaction. A logical unit of work that groups multiple database actions together into one indivisible operation.
Quiz
86% of people love quizzes after learning. Are you one of them?
Question text
Quiz complete