Data recovery

1. Data recovery

2. Recovery - introduction • recovery • restoring a system, after an error or failure, to a state that was previously known as correct • have you dealt with “recovery” (in a form or another …)? • principle: redundancy • for databases: redundancy at the physical, not logical, level • recovery must be performed by the DBMS; it should be transparent to the user

3. Failures • local failure (soft crash) • properly dealt with by transaction recovery • global /system failure (soft crash) • all transactions currently in progress are affected • two-phase commit algorithm • media failure (hard crash) • different procedures for recovery (backups / dumps)

4. Recovery in database systems • database recovery from within applications (e.g. errors generated by the backend) • ROLLBACK • recovery from backend failures • RESTART PROCEDURE

5. Recall “transaction” • logical unit of work • sequence of database operations • transforms a consistent state of a db into another consistent state • between operations the db can be inconsistent

6. ACID properties of transactions • Atomicity • all or nothing • Consistency • preserve database consistency • Isolation • transactions are isolated from one another • locking (levels of isolation) • Durability • committed transaction  updates are performed

7. Database recovery from within applications (example in pseudocode) // operations before database transaction r = db_execute(BEGIN TRANSACTION); r = db_execute(INSERT INTO customers VALS (‘v1’, ‘v2’, ‘v3’)); IF (error(r)) THEN db_execute(ROLLBACK TRANSACTION); r = db_execute(INSERT INTO sales VALS (‘v1’, ‘v2’, ‘v5’)); IF (error(r)) THEN db_execute(ROLLBACK TRANSACTION); r = db_execute(UPDATE stock SET a = a + ‘v6’ WHERE ...)); IF (error(r)) THEN db_execute(ROLLBACK TRANSACTION); r = db_execute(COMMIT TRANSACTION); // operations after database transaction

8. How can a transaction be undone? • system log or journal • FIRST: write description of operation in log • SECOND: perform operation • individual statements must be atomic; the system must guarantee that a set level operation is performed on all the corresponding tuples

9. Content of Log • descriptions of all operations and their result • information about each statement • INSERT • <> <INSERT> <inserted tuple(s)> • UPDATE • <before values of attribute> <UPDATE> <after values of attribute> • DELETE • <tuples to be deleted> <DELETE> <>

10. Structure of Log: stack • <><INSERT><tuple1><old attribute1><UPDATE><new attribute1><><INSERT><tuple2><old tuples><DELETE><>...

11. ROLLBACK mechanism • “unload” the stack • <old values> OPERATION <new values> • undo each individual operation • the existence of old and new values stored in the log provide for an easy algorithm; • inverse of INSERT is DELETE • inverse of UPDATE IS UPDATE • inverse of DELETE is INSERT

12. What does COMMIT mean? • all the operations of a transaction are guaranteed to be made permanent; • what does permanent mean? • are operations on a database not permanent, anyway? • buffers

13. Buffers • it would be very expensive for the DBMS to always work directly with the disk • therefore, buffers are employed

14. Buffers internal memory disk DBMS buffers

15. Synchronisation buffers-disk • when are the buffers written on disk? • at regular intervals of time: CHECKPOINTS • what information is written? • all the operations performed on buffers, before the previous checkpoint (disregarding whether the transactions they compose have been completed or not)

16. The Log and the Buffers • is the log stored in buffers or is it always on the disk? • why? • recovery from system crash

17. System crash BEGIN TRANSACTION COMMIT time idle incidentally, this transaction was not completed before the crash

18. Redo/Undo • If a system crash occurs, how does the system know, after it has been restarted, which transactions to redo and which to undo?

19. Types of transactions with regards to system failure time T1 T2 T3 T4 T5 system failure checkpoint

20. T1 – no action time T1 transaction completed here (in buffers) all the operations of T1 are performed on the disk here system failure checkpoint

21. T2 - redo these operations of T1 are performed in buffers and recorded in the log but aren’t physically performed on disk time T2 transaction completed here (in buffers) all these operations of T1 are performed on the disk here system failure checkpoint

22. T3, T4, T5 • homework (or have a look on next page)

23. Redo/Undo • transactions of type • T1 - have already been force-written • T2 and T4 - must be redone (their completion was recorded in the log but they have not been force-written) • T3 and T5 - must be undone • restart procedure

24. Restart procedure - homework • simplifying assumption: there is one log per transaction • no simplifying assumption; there is one log for the DBMS

25. Advanced topic:Physically distributed partitions

26. The co-ordinator • data can be physically distributed • each physical partition can be regarded as an individual database • resource manager (something like a local DBMS) • the DBMS (for the overall database) has a module co-ordinator that manages the transactions

27. Two-phase commit algorithm • suppose that each local transaction was completed successfully, then: • co-ordinator sends: get ready message • each resource manager: force write in log the complete descriptions of their operations; then they answer OK or not OK • all answers OK: co-ordinator decides to commit, force writes it in its own log, and sends COMMIT to all resource managers • at least one answer not OK: co-ordinator writes ROLLBACK in own log and sends message to all resource manages

28. Conclusions • data recovery is done based on TRANSACTIONS