Understanding Database Failure Types

At 3:47 AM, a power surge hits a data center. In 0.003 seconds, before any circuit breaker can react, the voltage spike reaches a database server. The CPU halts mid-instruction. Fifty-three active transactions—representing orders, payments, and inventory updates—are suspended in various states of completion. The RAM, holding gigabytes of modified but unflushed data pages, loses power. Every bit vanishes.

This is a system failure—a catastrophic event where the entire DBMS instance stops abruptly. Unlike transaction failures (which affect individual transactions) or media failures (which damage persistent storage), system failures destroy the volatile state while leaving persistent storage intact.

System failures are both terrifying and manageable. Terrifying because they affect everything simultaneously. Manageable because the log-based recovery mechanisms we'll study are specifically designed to handle them. This page explores system failures in comprehensive detail.

A system failure (also called a crash or soft failure) occurs when the DBMS instance terminates abnormally, losing all volatile (in-memory) state. The key characteristics are:

Formal Definition:

A system failure is an event where:

1. The DBMS process(es) terminate unexpectedly
2. All contents of main memory (RAM) are lost
3. Contents of persistent storage (disk, SSD) remain intact
4. All active transactions at the time of failure are affected
5. The database is in an inconsistent state and requires recovery

The distinction between volatile and persistent state is crucial. System failures are 'soft' failures precisely because they don't damage the disk—they only lose what was in memory. This creates a recovery problem (how do we restore consistent state?) but also provides the solution (the log on disk tells us what happened).

What Exactly Is Lost?

When a system failure occurs, the following volatile structures are lost:

1. Buffer Pool (Buffer Cache): The buffer pool holds copies of disk pages in memory for fast access. Many of these pages have been modified ('dirtied') by transactions but not yet written back to disk. All dirty pages that weren't flushed are lost.

2. Lock Table: The lock manager's record of which transactions hold which locks on which objects. After a crash, there are no locks—which is actually helpful for recovery.

3. Transaction Table: The list of active transactions, their states, and their progress. After a crash, we must reconstruct this from the log.

4. Query Execution State: Partially executed queries, cursors, intermediate results. All lost and must be re-executed.

5. Connection State: All client connections are broken. Clients must reconnect after recovery.

6. Cached Plans: Query plans compiled by the optimizer. Must be regenerated.

7. Statistical Caches: Buffer pool hit rates, query timings, performance metrics. Lost and reset.

System failures arise from various sources, spanning hardware, software, and environmental domains. Understanding these causes helps in both prevention and in designing recovery procedures.

Categories of System Failure Causes:

2.1 Power Failures

Power failures are perhaps the most common cause of abrupt system failures, especially in environments without adequate power protection.

Types of Power Problems:

Protection Mechanisms:

1. Uninterruptible Power Supply (UPS): Provides battery backup for orderly shutdown or brief outages
2. Generator Backup: For extended outages, automatic transfer to generator power
3. Redundant Power Supplies: Multiple PSUs in servers; single failure doesn't cause outage
4. Power Distribution Units (PDUs): Managed power distribution with monitoring
5. Battery-Backed Write Cache: Preserves write buffer during brief power loss

2.2 Operating System Crashes

The OS is the foundation on which the DBMS runs. When the OS crashes, the DBMS crashes with it.

Common OS Crash Causes:

• Kernel Bugs: Race conditions, null pointer dereferences, memory corruption in kernel code
• Device Driver Issues: Faulty drivers for storage, network, or other devices
• Resource Exhaustion: Out of memory, out of process IDs, file descriptor limits
• Deadlocks: Kernel-level deadlocks, especially in I/O paths
• Security Exploits: Kernel vulnerabilities being exploited

Operating systems are generally more stable than application software, but they're not immune to failures. A single faulty kernel module can bring down the entire system.

2.3 DBMS Software Bugs

Database software, despite extensive testing, can contain bugs that cause crashes:

• Memory Corruption: Buffer overflows, use-after-free, double-free errors
• Assertion Violations: Internal consistency checks that fail, triggering abort
• Infinite Loops/Hangs: Bugs that cause the system to become unresponsive
• Stack Overflow: Excessively deep recursion
• Concurrency Bugs: Race conditions in multi-threaded DBMS code

2.4 Hardware Failures (Non-Media)

Hardware failures that don't damage persistent storage but halt the system:

ECC Memory Is Essential:

For database servers, Error-Correcting Code (ECC) memory is mandatory, not optional. Non-ECC memory experiences bit flips due to cosmic rays, electrical noise, and aging. A single bit flip in a data page could corrupt data silently. ECC memory detects and corrects single-bit errors, and detects (but cannot correct) multi-bit errors—triggering a crash rather than silent corruption.

The fundamental challenge of system failures lies in the boundary between volatile memory and persistent storage. Understanding this boundary is crucial for understanding database recovery.

The Problem of Uncommitted Data on Disk:

DBMS systems don't write every change immediately to disk—that would be far too slow. Instead, they use a buffer pool:

1. Data pages are read from disk into memory
2. Transactions modify pages in memory
3. Modified ('dirty') pages are written back to disk periodically

This creates a problem: dirty pages from uncommitted transactions may be written to disk (this is called 'stealing' the buffer). If the system crashes:

• The disk contains changes from uncommitted transactions
• We must undo these changes during recovery

The Problem of Committed Data in Memory:

Conversely, a transaction may commit but its dirty pages may not yet be on disk:

1. Transaction modifies pages in memory
2. Transaction commits (log is flushed)
3. Dirty pages are still only in memory
4. System crashes before pages are written to disk

Now:

• The disk doesn't contain committed changes
• We must redo these changes during recovery

This dual problem—undo uncommitted changes and redo committed changes—is the essence of crash recovery.

The STEAL/NO-FORCE Policy:

Most production database systems use STEAL/NO-FORCE because:

• STEAL allows the buffer manager to evict any page when memory is needed, providing flexibility
• NO-FORCE allows fast commits (just write the log, not all data pages), providing performance

The cost is that recovery is more complex: we need both UNDO and REDO capabilities. But the performance benefit is substantial—commit latency drops from potentially hundreds of milliseconds (if we had to write all dirty pages) to just a few milliseconds (writing only the commit log record).

Write-Ahead Logging (WAL) Makes This Work:

The key insight making STEAL/NO-FORCE safe is Write-Ahead Logging:

Before any dirty page is written to disk, the log records describing the changes must be written first.

This guarantees that:

1. If a dirty page from an uncommitted transaction is on disk, the log contains the before-image for UNDO
2. If a committed transaction's changes are not on disk, the log contains the after-image for REDO

The log is the source of truth. Combined with the data files, we can always reconstruct the correct database state.

After a system failure, the database on disk is in an inconsistent state. It may contain:

1. Partial changes from uncommitted transactions (need UNDO)
2. Missing changes from committed transactions (need REDO)
3. Partially written pages (torn writes)

The recovery system must detect and correct all of these issues before making the database available for new transactions.

Example Scenario:

Consider a database with three concurrent transactions at the moment of crash:

Torn Writes: A Special Case:

A 'torn write' or 'partial page write' occurs when only part of a page is written to disk before the crash. Consider:

• Database pages are typically 8KB or 16KB
• Disk sectors are typically 512 bytes or 4KB
• Writing one page requires multiple sector writes
• A crash during this sequence leaves a partially written page

A torn page is corrupted—it contains a mix of old and new data that may be internally inconsistent.

Detection and Prevention of Torn Writes:

1. Page Checksums: Each page includes a checksum. If the checksum doesn't match after read, the page is torn.

2. Double-Write Buffer (MySQL InnoDB): Before writing a page to its final location, write it to a special double-write buffer. If crash occurs during final write, the doublewrite buffer has a clean copy for recovery.

3. Full-Page Writes (PostgreSQL): After each checkpoint, the first modification to any page logs the entire page (not just the change). Recovery uses this full-page image.

4. Atomic Write Support: Modern storage (some SSDs, storage arrays) guarantees atomic writes of large blocks, eliminating torn writes.

Recovery from system failures follows a structured process that restores the database to a consistent state. While we'll cover recovery algorithms in detail in later chapters, understanding the high-level process is essential for understanding system failures.

The Recovery Process Overview:

Why Redo Everything, Then Undo?

A common question: Why redo changes from uncommitted transactions if we're just going to undo them?

The answer lies in the log structure and recovery correctness:

1. Page-level consistency: The redo phase brings pages to their exact pre-crash state. This is necessary because the undo phase uses before-images that assume specific page states.

2. Log ordering: The log records reference specific page versions. Skipping some redos would leave pages in states that don't match what the log expects.

3. Nested transactions and savepoints: Some undo operations might need page states that were created by operations we'd otherwise skip.

4. Simplicity and correctness: The 'redo all, then undo uncommitted' approach is simpler to implement correctly and verify than selective redo.

Recovery Time Considerations:

Recovery time depends on several factors:

Understanding the differences between system failures and transaction failures is essential for designing appropriate handling strategies. While both require recovery mechanisms, their characteristics differ significantly:

Key Insight: The Lock Table Problem

One significant difference is lock management:

Transaction Failure: The transaction manager immediately releases all locks held by the failed transaction. This happens while the system is still running, so the lock table in memory is updated directly.

System Failure: The lock table in memory is lost. Upon recovery, there are no locks at all. This is actually helpful—we don't need to worry about reconstructing the lock table. Recovery runs in a single-threaded manner, and once complete, new transactions start fresh with clean lock state.

Key Insight: The Buffer Pool Problem

Transaction Failure: Dirty pages remain in the buffer pool. The rollback operation modifies them back to their original state. No disk I/O is strictly necessary (though it happens eventually).

System Failure: The buffer pool is gone. All dirty pages from all transactions are lost. Some of those dirty pages represented committed work (need redo), others represented uncommitted work (need undo). We must read pages from disk, examine log records, and reconstruct correct state.

While system failures cannot be completely eliminated, their frequency and impact can be significantly reduced through proper infrastructure, configuration, and operational practices:

Let's consolidate the key concepts covered in this page:

What's Next:

We've now covered transaction failures (individual) and system failures (entire instance). In the next page, we'll examine Media Failures—the most severe failure type where persistent storage itself is damaged or lost. Media failures require different recovery strategies involving backups and archives, since the normal log-based recovery from disk is impossible when the disk itself is the problem.