DBMS Transactions and Rollback Recovery Helia Martti Laiho

DBMS Transactions and Rollback Recovery Helia / Martti Laiho

Application programs - Sessions (connections) - Transactions - SQL commands Database Instance Data Buffer (Buffer pool) DBMS Listener / Server Transaction Manager SQL Engine (parser) Security Manager Query Optimizer Concurrency Manager (Lock Manager) Recovery Manager Relational Engine Memory Manager Table pages and index pages Control Buffers - Connections - Transaction queueing - Locking List - etc Log Buffer LRU Checkpoint: x before image / after image Commit/Rollback: x read write Data file File Manager Data file Disk Manager Transaction Log files Helia / Martti Laiho, 1998 -2003

Processing a Transaction User process: Data buffer Redo log buffer (Rollback Segments Oracle only) after images Insert into table Update table before images undo /redo data undo / redo data Delete from table Select. . Commit / Rollback undo / redo data commit / rollback undo data ? On Rollback LGWR OK ? continue Redo log files

Marking Checkpoint into Transaction Log tc Begin time Commit T 1 Begin Rollback T 2 T 3 Begin Commit T 4 Begin T 5 Write all after images Into the database Write Checkpoint record into the log of the current transactions Helia / Martti Laiho, 1998 -2005

A Soft Crash tf tc time Commit T 1 Rollback T 2 T 3 Commit T 4 T 5 Checkpoint record “Soft Crash” System Failure Helia / Martti Laiho, 1998 -2005

Rollback recovery using transaction log tc 2. Find the last checkpoint record tf time Commit T 1 Rollback T 2 T 3 Commit T 4 T 5 Checkpoint record 3. Add transactions on undo list Rollback Recovery: Undo list: T 1, T 2, T 3 , T 4, T 5 Redo list: 1. Start up From checkpoint record From the log after the checkpoint Helia / Martti Laiho, 1998 -2005

Rollback recovery using transaction log tf tc time Commit T 1 Rollback T 2 T 3 Commit T 4 T 5 Checkpoint record Rollback Recovery Undo list: T 1, T 2, T 3, T 4, T 5 Redo list: T 1, T 4 3 4. Move committed transactions to Redo list Helia / Martti Laiho, 1998 -2005

Rollback recovery using transaction log tc tf time Commit T 1 T 2 T 3 redo Rollback undo T 4 Commit redo T 5 undo Checkpoint record Rollback Recovery Undo list: T 1, T 2, T 3, T 4, T 5 Redo list: T 1, T 4 5 5. Rollback transactions of the Undo list - writing the before images into the database Redo transactions of the Redo list - writing the after images into the database 6. Open the DBMS service to applications Helia / Martti Laiho, 1998 -2005