Atomicity

A transaction is either applied fully (COMMITed) or not at all (ABORTed).

Consistency

Constraints are enforced on the final state of the transaction (and the transaction is ABORTed if they fail).

Isolation

Two transactions running in parallel don't interfere with each other

Durability

Once the database returns from a COMMIT successfully, the data is safe from marmots

2 Problem Scenarios

A long write...

What if the DB fails during a write.

Buffer memory is limited...

What if we need to page out some pages modified by a live transaction?

Simplifying Assumptions

Today: objects are pages

This lets us reason about operations at the IO level

IOs are individually atomic and durable.

Successful writes can be distinguished from fails.

HDD/SSDs are a "safe" storage layer.

No fires, t-rex attacks, or global pandemics*

When the system reboots/the power comes back on, we assume that successful writes are still there and failed writes are not.

* May not be an entirely safe assumption

Failure Analysis

All IOs fail (crash before)

No problem!

All IOs succeed (crash after)

No problem!

At least one IO succeeds, and at least one fails

Problem! (partial write)

Write-Ahead Logging (WAL)

1. "Log" the transaction's actions
2. Wait for the log to be safely on-disk
3. Write the transaction's effects to disk

Failure Analysis

All IOs fail or all succeed

No problem!

At least one Log IO succeeds, but failure before COMMIT.

No problem! DB intact. No COMMIT = don't replay the log.

COMMIT succeeds, no DB writes succeed

No problem. The log lets us replay transaction's effects.

COMMIT succeeds, at least one DB write succeeds

Possible problem. Log replay needs to be idempotent.

COMMIT succeeds, all DB writes succeed

Slow! Still might need to replay the log.

More Failure Analysis

Second failure during replay

Not a problem if log operations are idempotent!

... but could still cause problems if recovery is slow.

Note: The transaction is durable as soon as we can guarantee the transaction's COMMIT is in the log.

We can consider the commit successful even if the data pages aren't on disk yet.

Multiple Transactions

Using only one log is more efficient for writes!

... but recovery is slower.

Recovery

1. Scan through the log to find all transactions with a COMMIT entry.
2. Replay log entries from COMMITed transactions only.

Recovery gets slower with every transaction.

Idea: Periodically mark down the index of the earliest log entry still needed

How do we decide that a log entry is no longer needed.

Buffer Manager

Keep the first log entry to modify any dirty page

Log entries lower than a page's 'first log entry' aren't useful.

Periodically write the lowest 'first log entry' into the log (CHECKPOINT).

WAL Recovery

1. Scan backwards through the log to find the checkpoint.
2. Scan forward starting with the checkpointed 'first log entry' to find all transactions with a COMMIT entry.
3. Replay log entries starting with the checkpointed 'first log entry', ignoring non-COMMITed transactions.

Multi-Write Atomicity

Solved by WAL

Pro-actively Flushing Writes

Problem: There may not be enough memory to store pages modified by uncommitted transactions.

Keep a separate 'uncommitted' copy of the db.

Need to copy data back to main db after commit.

Use the WAL to recover uncommitted pages.

Sloooooooow.

Flush to the DB itself.

What if the transaction aborts?

Undo-Logging

Log

Idea: Record the page's previous value.

If a transaction aborts, go backwards through the log and undo all of the changes

Observation: Going backwards through the log can be slow if you have a lot of concurrent transactions.

Log

Idea: Each log entry records the timestamp of the next most recent log entry for the same transaction.

Log entries form a linked list for each transaction

Putting it together

Transaction Table

Buffer Manager

Types of Log Entries

• Update Page: As above
• ABORT (Start aborting)
• COMMIT (Transaction committed)
• END (Done aborting/committing)
• CHECKPOINT (Snapshot of volatile memory state)

Recovering after a crash

1. Rebuild in-memory state (Analyze)
2. Rebuild buffer manager (Redo)
3. Abort uncommitted transactions (Undo)

ARIES Recovery

Analyze

Redo

As WAL recovery, before

Undo

1. Start ABORTing all 'ACTIVE' transactions
2. Replay transaction log in reverse (correct behavior ensured by serializability)

Problem: Writing out a Checkpoint is SLOOOW (and blocking).

Idea: Snapshot in-memory state, then write in the background.

Record "START_CHECKPOINT" in log when the snapshot is taken.

Record "END_CHECKPOINT" in log when the snapshot is fully written.

Find the last "END_CHECKPOINT" and start recovery from the corresponding "START_CHECKPOINT" entry

Optimization: What if you crash during recovery?

Idea: Log "UNDO" operations as new writes

Compensation Log Records (CLRs)

Analyze