CS411 Concurrency control Database Systems Recovery Logging - PDF document

Outline • Transaction • Atomicity CS411 – Concurrency control Database Systems – Recovery • Logging – Redo 13: Logging and Recovery – Undo – Redo/undo Kazuhiro Minami Users and DB Programs Transaction • DB applications are designed as a set of • End users don't see the DB directly transactions – are only vaguely aware of its design • Execute a number of steps in sequence – may be acutely aware of part of its contents – Those steps often modify the database – SQL is not a suitable end-user interface • Maintain a state • A single SQL query is not a sufficient unit of DB – Current place in the transaction’s code being executed work – Local variables – May need more than one query • Typical transaction – May need to check constraints not enforced by the DBMS – starts with data from user or from another transaction – May need to do calculations, realize “business rules”, – includes DB reads/writes etc. – ends with display of data or form, or with request to start another transaction

Requirements for Atomicity Atomicity • Recovery • Transactions must be "atomic" – Prevent a transaction from causing inconsistent – Their effect is all or none database state in the middle of its process – DB must be consistent before and after the • Concurrency control transaction executes (not necessarily during!) – Control interactions of multiple concurrent • EITHER transactions – a transaction executes fully and "commits" to – Prevent multiple transactions to access the same all the changes it makes to the DB record at the same time – OR it must be as though that transaction never executed at all A Typical Transaction "Commit" and "Abort" • User view: “Transfer money from savings • A transactions which only READs expects to checking” DB to be consistent, and cannot cause it to become otherwise. • Program: Read savings; verify balance is • When a transaction which does any WRITE adequate * , update savings balance and finishes, it must either rewrite ** ; read checking; update checking – COMMIT: "I'm done and the DB is consistent balance and rewrite *** . again" OR – ABORT: "I'm done but I goofed: my changes * DB still consistent must be undone." ** DB inconsistent *** DB consistent again

System failures But DB Must Not Crash • Problems that cause the state of a transaction to be • Can't be allowed to become inconsistent lost – A DB that's 1% inaccurate is 100% unusable. – Software errors, power loss, etc. • Can't lose data • The steps of a transaction initially occur in main • Can't become unavailable memory, which is “volatile” – A power failure will cause the content of main memory A matter of life or death! to disappear – A software error may overwrite part of main memory Can you name information processing systems that are more error tolerant? Transaction Manager Solution: use a log • May be part of OS, a layer of middleware, or part of the DBMS • Log all database changes in a separate, nonvolatile • Main duties: log, coupled with recovery when necessary – Undo – Starts transactions – Redo • locate and start the right program – Undo/redo • ensure timely, fair scheduling • However, the mechanisms whereby such logging – Logs their activities can be done in a fail-safe manner are surprising • especially start/stop, writes, commits, aborts intricate – Detects or avoids conflicts – Logs are also initially maintained in memory – Takes recovery actions

Elements Correctness Principle • There exists a notion of correctness for the database • Assumption: the database is composed of – Explicit constraints (e.g. foreign keys) elements – Implicit conditions (e.g. sum of sales = sum of invoices) – Usually 1 element = 1 block • Correctness principle : if a transaction starts in a correct – Can be smaller (=1 record) or larger (=1 relation) database state, it ends in a correct database state • Assumption: each transaction reads/writes some • Consequence: we only need to guarantee that transactions are atomic , and the database will be correct elements forever • A database has a state , which is a value for each of its elements Primitive Operations of Transactions Primitive Operations of Transactions • INPUT(X) – read element X to memory buffer • READ(X,t) – copy element X to transaction local variable t INPUT(X) READ(X, t) • WRITE(X,t) X t X – copy transaction local variable t to element X WRITE(X, t) • OUTPUT(X) OUTOUT(X) Transaction’s – write element X to disk Main memory local variable Disk buffers

The Log Example • An append-only file containing log records READ(A,t); t := t*2;WRITE(A,t) READ(B,t); t := t*2;WRITE(B,t) • Note: multiple transactions run concurrently, log records are interleaved Action t Mem A Mem B Disk A Disk B INPUT(A) 8 8 8 • After a system crash, use log to: READ(A,t) 8 8 8 8 – Redo some transaction that committed t:=t*2 16 8 8 8 – Undo other transactions that didn’t commit WRITE(A,t) 16 16 8 8 READ(B,t) 8 16 8 8 8 t:=t*2 16 16 8 8 8 WRITE(B,t) 16 16 16 8 8 OUTPUT(A) 16 16 16 16 8 OUTPUT(B) 16 16 16 16 16 Undo logs don’t need to save after- images Log records: • <START T> Undo Logging – transaction T has begun • <COMMIT T> – T has committed • <ABORT T> – T has aborted • <T,X,v> – T has updated element X, and its old value was v

Undo-Logging Rules Action T Mem A Mem B Disk A Disk B Log U1: If T modifies X, then <T,X,v> must be written <START T> to disk before X is written to disk REAT(A,t) 8 8 8 8 t:=t*2 16 8 8 8 U2: If T commits, then <COMMIT T> must be WRITE(A,t) 16 16 8 8 <T,A,8> written to disk only after all changes by T are READ(B,t) 8 16 8 8 8 written to disk t:=t*2 16 16 8 8 8 WRITE(B,t) 16 16 16 8 8 <T,B,8> • Hence: OUTPUTs are done early FLUSH LOG OUTPUT(A) 16 16 16 16 8 OUTPUT(B) 16 16 16 16 16 <COMMIT T> FLUSH LOG Crash recovery is easy with an undo log. Detailed algorithm for undo log recovery 1. Scan log, decide which transactions T From the last entry in the log to the first: completed. – <COMMIT T>: mark T as completed � <START T>….<COMMIT T>…. – <ABORT T>: mark T as completed � <START T>….<ABORT T>……. – <T,X,v>: if T is not completed � <START T>……………………… then write X=v to disk 2. Starting from the end of the log, undo all else ignore modifications made by incomplete transactions. – <START T>: ignore The chance of crashing during recovery is relatively high! But undo recovery is idempotent: just restart it if it crashes.

Scanning a year-long log is SLOW and Undo recovery practice businesses lose money every minute their Which actions do we … DB is down. undo, in which order? <T6,X6,v6> What could go wrong if … Solution: checkpoint the database periodically. we undid them in a <T4,X4,v4> different order? <START T5> Easy version: <START T4> 1.Stop accepting new transactions <T1,X1,v1> 2.Wait until all current transactions complete <T5,X5,v5> <T4,X4,v6> 3.Flush log to disk <COMMIT T5> 4.Write a <CKPT> log record, flush <T3,X3,v3> 5.Resume transactions <T2,X2,v2> This “quiescent checkpointing” isn’t During undo … … good enough for 24/7 applications. recovery, stop <T9,X9,v9> other Instead: … at first transactions … checkpoint. (all completed) 1. Write <START CKPT(T1,…,Tk)>, <CKPT> where T1,…,Tk are all active transactions <START T2> <START T3 2. Continue normal operation <START T5> <START T4> 3. When all of T1,…,Tk have completed, write <T1,X1,v1> T2,T3,T4,T5 <END CKPT> <T5,X5,v5> <T4,X4,v4> <COMMIT T5> <T3,X3,v3> <T2,X2,v2>

Crash recovery algorithm with undo log, Example of undo recovery … nonquiescent checkpoints. … with … 1. Scan log backwards until the start of the latest … nonquiescent completed checkpoint, deciding which earlier transactions plus T4, T5, T5 transactions T completed. checkpointing … � <START T>….<COMMIT T>…. <START CKPT T4, T5, T6> � <START T>….<ABORT T>……. … T4, T5, T6, plus � <START CKPT {T…}>….<COMMIT T>…. … later transactions … � <START CKPT {T…}>….<ABORT T>……. What would go What would go … wrong if we � <START T>……………………… wrong if we <END CKPT> didn’t use … didn’t use 2. Starting from the end of the log, undo all … <END CKPT> ? <END CKPT> ? later transactions … modifications made by incomplete transactions. Example <START T1> <START T1> <T1, A, 5> <T1, A, 5> <START T2> <START T2> <T2, B, 10> <T2, B, 10> Redo Logging <START CKPT(T1, T2)> <START CKPT(T1, T2)> <T2, C, 15> <T2, C, 15> <START T3> <START T3> <T1, D, 20> <T1, D, 20> <COMMIT T1> <COMMIT T1> <T3, E, 25> <T3, E, 25> <COMMIT T2> <END CKPT> <T3, F, 30>