DBMS Transactions and Rollback Recovery

1. DBMS Transactions and Rollback Recovery Helia / Martti Laiho

2. 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 Control Buffers Table pages and index pages • Connections • Transaction queueing • Locking List • etc x LRU Checkpoint: Log Buffer x read write before image / after image Commit/Rollback: write Data file Data file File Manager Disk Manager Transaction Log files Helia / Martti Laiho, 1998-2003

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

4. Marking Checkpoint into Transaction Log tc time Begin Commit T1 T2 T3 T4 T5 Begin Rollback Begin Begin Commit Begin Write Checkpoint record into the log of the current transactions Write all after images Into the database Helia / Martti Laiho, 1998-2005

5. A Soft Crash tf tc time Commit T1 T2 T3 T4 T5 Rollback Commit Checkpoint record System Failure “Soft Crash” Helia / Martti Laiho, 1998-2005

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

7. Rollback recovery using transaction log tf tc time Commit T1 T2 T3 T4 T5 Rollback Commit Checkpoint record Rollback Recovery Undo list: T1, T2, T3, T4, T5 Redo list: T1, T4 4. Move committed transactions to Redo list 3 Helia / Martti Laiho, 1998-2005

8. Rollback recovery using transaction log tc tf time Commit T1 T2 T3 T4 T5 redo Rollback undo undo Commit redo undo Checkpoint record 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 5 Rollback Recovery Undo list: T1, T2, T3, T4, T5 Redo list: T1, T4 Helia / Martti Laiho, 1998-2005