Inside the Transaction: How Databases HOLD Records

Prerequisites

This article is the close-up view of what happens inside a transaction.

Need the concert trip first? Database Transactions & ACID Guarantees →

Now we zoom in further.

What actually happens inside the database the exact moment a fan clicks: Buy Ticket

The HOLD Plan

The moment a fan clicks “Buy Ticket,” the database starts a transaction — a group of related operations that must either all succeed together or all fail together.

For a concert ticket purchase, the database transactions are:

• reserve the seat
• charge the card
• reduce remaining inventory

If the server crashes halfway through, we cannot allow this:

Money charged ✅
Seat reserved ❌

or worse:

One seat
Two owners

Here’s how the database HOLDS everything together — even during crashes:

HOLD

Hold the lock

Lock Manager

Stops double-booking by locking out rivals.

Seat H-01

Operate in memory

Page Cache

Keeps updates fast by staging changes in RAM.

Seat O-02

Log the changes

Write-Ahead Log

Protects the transaction against crashes.

Seat L-03

Durable storage

Disk Flush

Archives changes into permanent storage.

Seat D-04

One Transaction

Read GARAGE model HOLD is close up of GE from the GARAGE Model →

---

🔒 H — Hold the Lock

Imagine two fans wanting the exact same perfect seat: A7.

Without safeguards, both fans could see Seat A7 = Available and continue toward payment.

That creates the database nightmare:

One physical seat.
Two angry owners.

To prevent this collision, the transaction must first pass through the Lock Manager.

Think of the Lock Manager as a ruthless stadium bouncer holding the only official seating clipboard.

Seat A7 may look available to you, but another fan might already be in the process of grabbing it.

So databases follow a strict rule:

⚠️ Trust what you see. Verify before you touch.

Before taking the seat, the transaction must first ask the bouncer for an Exclusive Lock (X Lock) 🔒.

Having an Exclusive Lock is like the bouncer physically placing his thumb over Seat A7 on the clipboard so nobody else can claim it.

Seat A7 free?

No thumb on A7?      
        ──► Lock Granted 🔒

Thumb already there? 
        ──► Wait ⏳ or Abort ❌

Sometimes transactions still fail unexpectedly.

Like getting permission for Seat A7, then spilling your drink before you can sit down.

The seat is released, the attempt is abandoned, and the process safely starts over from the beginning.

Transaction Aborted → Restart 🔄

At the H — Hold the Lock stage, the database is only protecting ownership:

• no money transferred
• no inventory updated
• nothing written permanently to disk

The database is simply preventing multiple transactions from modifying the same seat at the same time.

---

🧠 O — Operate in Memory

Now that the bouncer has his thumb over Seat A7, the transaction moves to O — Operate in Memory.

Here is the surprise: the database usually does not update the permanent disk copy immediately.

Because disk is safe, but slow.

In the stadium, that means the bouncer cannot radio the back office and wait for confirmation every time a fan takes a seat. Angry fans would line up fast.

So instead, the bouncer updates his local clipboard first.

More fans can take their seats, and the line keeps moving.

But there is a catch: the local clipboard is fast, but it is not the official back-office record yet.

That is the database trick.

The local clipboard is RAM: fast and temporary — the database’s Page Cache, also known as the Buffer Pool.

The back office record is disk: slower, but permanent.

When Seat A7 is taken, the database updates the memory copy:

[ RAM: Page Cache / Buffer Pool ]

Seat A7: Available ──► Reserved
Inventory: 42 ──► 41

This changed memory page is called a Dirty Page.

Dirty because the memory version and disk version no longer match:

RAM:  Seat A7 = Reserved
Disk: Seat A7 = Available

That mismatch is useful for speed, but dangerous for safety.

If the server crashes now, RAM can forget everything.

So the transaction cannot stop here.

It must move from O to L, where the database writes down what memory might forget.

If the database wrote every ticket purchase to disk right away, the whole website would crawl.

---

📜 L — Log the Changes

Seat A7 is reserved on the bouncer’s local clipboard, but the official back-office record is not updated yet.

A drink can spill on the clipboard. A page can tear.
Or the classic excuse: “The dog ate my clipboard.”

So the stadium does not rely on the clipboard alone.

Beside the bouncer, an assistant writes a stamped receipt for every approved seat change.

The bouncer’s clipboard keeps the line moving.

The assistant’s stamped receipt becomes the proof.

But this receipt cannot stay in the assistant’s hand.

Before the stadium can say Seat A7 is yours ✅, the receipt must be safely stored where a spilled drink or lost clipboard cannot erase it.

In database terms, that safely stored receipt is the Write-Ahead Log (WAL).

It is still a disk write — but a very specific kind of disk write.

It records what changed:

Transaction 4821
Seat A7: Available → Reserved
Inventory: 42 → 41
Commit ✅

This may sound strange because earlier we said disk writes are slow.

But WAL writes are different.

The database is not rewriting scattered database pages all over disk.

It is appending one small record to the end of a log.

That difference matters:

Random page writes    
    → slow, scattered, expensive

Appending to log      
    → fast, ordered, append-only

That is what COMMIT means here:

BEGIN;

UPDATE seats SET status = 'taken' 
WHERE seat_id = 'A7';

UPDATE inventory SET 
available = available - 1;

COMMIT;

COMMIT does not mean every final database page is already updated. It means the database has durable proof it can recover this transaction

If the server crashes after this point, recovery can read the WAL and rebuild what RAM forgot.

🔓 Releasing the Seat

Once the database reaches COMMIT, the transaction is over.

The lead bouncer no longer needs to keep his thumb pinned over Seat A7.

Why?

Because the assistant has already stamped and safely stored the receipt.

That receipt proves what happened, so other transactions can now move forward without risking a double-booking.

In database terms, locks are released when the transaction commits or rolls back.

The important part:

The lock does not wait for the later disk flush.

The final database pages may still be written in the background, but the transaction no longer needs exclusive control of the seat.

Seat A7 is now claimed, and the next transaction can approach.

Insight: At the L stage, the transaction is written to a crash-safe log. And once that proof exists, the system can release the lock.

L = Log and release the lock

---

💾 D — Durable Storage

Even after COMMIT, the final database pages may still not be updated on disk.

That sounds scary.

But remember: the assistant’s stamped receipt - WAL - is the durable proof that Seat A7 was reserved.

So the database does not need to rush the final seating book update while fans are still moving through the line.

Back in the stadium:

• the bouncer’s local clipboard (O) has the newest seat state
• the assistant’s stamped receipt (L) proves what happened
• the official back-office record (D) still needs to catch up

Later, if the clipboard is still available, a quiet back-office worker copies the latest seat state from the bouncer’s clipboard into the official seating book.

That is the normal path.

In database terms, this is called a flush: writing dirty pages from memory to disk:

Page Cache / RAM
      ↓
    Flush
      ↓
Disk / Data Files

If the clipboard was lost before this flush happened, recovery would use the stamped receipt instead.

That is why the WAL mattered first.

After the flush, the memory copy and disk copy finally match:

RAM:  Seat A7 = Reserved
Disk: Seat A7 = Reserved

This background work keeps the system fast.

If every transaction had to update the final disk pages immediately:

• the line would slow down
• disk writes would pile up
• fewer transactions could move through at once

But there is one more problem.

As the database keeps running, the WAL keeps growing.

If the server crashes later, recovery should not need to replay every receipt from the beginning of the concert.

So databases create periodic checkpoints.

Think of a checkpoint as the back office drawing a line in the receipt book:

“Everything before this line is now safely reflected in the official seating book.”

After that point, recovery can start from the latest checkpoint instead of replaying the entire log history.

At the D stage, the transaction finally lands in durable storage.

The WAL protected it while disk caught up.

Now the disk preserves it.

---

🔄 When the Database Crashes

Now imagine the bouncer’s local clipboard gets ruined before the final back-office update.

Someone spills a drink.
The ink smears.
The page becomes unreadable.

That is our stadium version of a crash.

The bouncer’s local clipboard (O) may be gone.

The dirty page in RAM may be lost.

But the assistant’s stamped receipt (L) is still safely stored.

That is why the database can recover from a good OL’ crash.

When the database starts again, it does not immediately open the gates.

First, it runs Crash Recovery.

The database looks for the latest checkpoint — the last place where the back-office record had safely caught up.

Then it reads the Write-Ahead Log (WAL) from that point forward and asks:

Which transactions committed?
Which transactions were not?

For committed transactions, recovery replays the missing changes:

REDO → Seat A7: Available → Taken

For unfinished or aborted transactions, recovery removes or ignores their partial work:

UNDO → Pretend the unfinished never happened

That is how the database protects the all-or-nothing rule even after failure.

Here is the survival timeline:

1. Seat A7 updated on the local clipboard (RAM) ✅
2. Stamped receipt safely stored (WAL) ✅
3. Clipboard ruined before back-office update 💥

Without the WAL, the database would be stuck asking:

Did Seat A7 sell or not?

But because the stamped receipt survived, recovery has an answer.

H → O → L → D
        ↑
Recovery starts from the proof

The clipboard may be gone. The proof survives.

---

A quick recap

A transaction does not stay safe because the database writes everything to disk immediately. That would be too slow.

The trick is that the database works fast in RAM first, then makes the change recoverable with the WAL, and finally lets disk catch up later.

That is how it can feel instant without becoming fragile:

• H — hold ownership with locks
• O — operate fast in memory
• L — log crash-safe proof
• D — durable storage catches up

---

Commit This to Memory

Transaction

The boundary around related database actions. Either the whole ticket purchase succeeds together, or the database rolls it back like it never happened.

Transaction. The boundary around related database actions. Either the whole ticket purchase succeeds together, or the database rolls it back like it never happened.

Quiz

86% of people love quizzes after learning. Are you one of them?

★

Question 1 of 12 🏆 0 / 120 ⚡ Attempt 1 of 2

Question text

Next →