Transcript Database recovery - HAAGA

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
x
LRU
Checkpoint:
x
read
write
before image / after image
Commit/Rollback:
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
before images
undo /redo data
Update table
undo / redo data
Delete from table
undo / redo data
undo
undo data
undo data
Select ..
Commit / Rollback
commit / rollback
?
On Rollback
LGWR
OK ?
continue
Redo log files
Marking Checkpoint into Transaction Log
tc
Begin
time
Commit
T1
Begin
Rollback
T2
Begin
T3
Begin
Commit
T4
Begin
T5
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
T1
Rollback
T2
T3
Commit
T4
T5
Checkpoint
record
“Soft Crash”
System
Failure
Helia / Martti Laiho, 1998-2005
Rollback recovery using transaction log
2. Find the last checkpoint record
tc
tf
time
Commit
T1
Rollback
T2
T3
Commit
T4
T5
Checkpoint
record
3. Add transactions
on undo list
Rollback Recovery:
Undo list: T1, T2, T3 , T4, T5
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
T1
Rollback
T2
T3
Commit
T4
T5
Checkpoint
record
Rollback Recovery
Undo list: T1, T2, T3, T4, T5
Redo list:
T1, T4
3
4. Move committed transactions
to Redo list
Helia / Martti Laiho, 1998-2005
Rollback recovery using transaction log
tc
tf
time
Commit
T1
redo
T2
T3
Rollback
undo
undo
T4
Commit
redo
T5
undo
Checkpoint
record
Rollback Recovery
Undo list: T1, T2, T3, T4, T5
Redo list:
T1, T4
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