Durability Techniques

1. Write-Ahead Logging (WAL):

Write-ahead logging is crucial for ensuring durability. It involves maintaining a transaction log (also known as a write-ahead log) that records changes before they are applied to the database. Here's an example:

-- Begin a transaction
BEGIN TRANSACTION;

-- Update data in the database
UPDATE Orders SET Status = 'Shipped' WHERE OrderID = 123;
INSERT INTO OrderHistory (OrderID, Status, Timestamp) VALUES (123, 'Shipped', NOW());

-- Commit the transaction
COMMIT;

In this example, the transaction log records the changes made during the transaction, including the update and insertion. If a system failure occurs after the COMMIT, the log can be used to recover and ensure the changes are permanent.

2. Use of Durable Storage:

Durable storage refers to storing data on a medium that can withstand system failures. Here's an example:

Imagine that a DBMS stores its data on a hard disk drive (HDD) or solid-state drive (SSD). Even in the event of a power outage or system crash, the data on the durable storage medium remains intact, ensuring durability.

3. Checkpointing:

Checkpointing involves periodically creating checkpoints to enhance durability. Let's illustrate this with an example:

-- Begin a transaction
BEGIN TRANSACTION;

-- Update data in the database
UPDATE Inventory SET Quantity = Quantity - 10 WHERE ProductID = 789;
-- More operations...

-- Create a checkpoint to make changes durable
CHECKPOINT;

-- Commit the transaction
COMMIT;

In this example, a checkpoint is created after a series of database operations. The checkpoint ensures that all changes made up to that point are flushed to durable storage. If a failure occurs, the DBMS can start recovery from the last checkpoint, reducing the amount of work needed to ensure durability.

These examples demonstrate how write-ahead logging, durable storage, and checkpointing contribute to the durability of database transactions, making sure that committed changes are permanent and survive system failures.