Database Transaction Management and Recovery: Best Practices

1. Transaction ManagementDatabase recovery Concurrency control www.AssignmentPoint.com www.assignmentpoint.com

2. Transactions • A transaction is a sequence of operations that perform a single logical task. • The database must be in a consistent state before and after a transaction but may become inconsistent during execution. • Transaction management ensures that the database can be restored to a consistent state if something goes wrong during the transaction. www.assignmentpoint.com

3. ACID Properties of a Transaction • Atomicity • either all or none of the operations of a transaction are performed. • Consistency • db is in a consistent state before and after a executing a transaction (may be inconsistent during execution). • Isolation • Transactions are isolated from one another. • Durability • Once transaction commits, the changes it has made to db persist, even if system fails. www.assignmentpoint.com

4. Atomic Transactions • Either all or none of the operations of the transaction must be performed. • If all transactions were atomic, the database would always be in a consistent state. Unfortunately, they are not. www.assignmentpoint.com

5. Example • Transfer $500 from savings account to checking account. Begin transaction Read Sav_Amt Sav_Amt := Sav-Amt - 500 Write Sav_Amt Read Chk_Amt Chk_Amt := Chk_Amt + 500 Write Chk-Amt End transaction www.assignmentpoint.com

6. Structure of a Transaction Begin transaction Read input message Perform processing against database If successful send output message(s) and COMMIT else send error message and ROLLBACK End transaction www.assignmentpoint.com

7. Commit • A commit • Signals successful completion of a transaction to the DBMS • Frees any locks, created for example to avoid another user from accessing the same data • Makes all changes permanent and visible to other users • A commit does not mean that data has been written to disk. • DBMS keeps track of which changes have been saved. www.assignmentpoint.com

8. Rollback • A rollback • Signals unsuccessful completion of a transaction to the DBMS • Undoes all changes made by the transaction www.assignmentpoint.com

9. Database Recovery • After failure of some kind, database must be restored to some state known to be correct. • Recovery should be done • Quickly • With minimal transaction loss • To ensure possibility of recovery, one needs to perform • Database backup • Database logging • Checkpointing www.assignmentpoint.com

10. Database Backup • Periodically, the entire database should be copied to archival storage (e.g., tape, CD-ROM). • This copy should be stored in a safe place, preferably off-site. www.assignmentpoint.com

11. Database Logging • Whenever a change is made to the database, write a record to a special log file or journal. • Record in log consists of • Transaction name • Data item name • New value of data item • Old value of item. www.assignmentpoint.com

12. Checkpointing • When failure occurs, need to determine which transactions must be redone or undone. • Too time-consuming to search the entire log. • Solution: Use checkpointing • Synchronize log and the database by performing all pending writes • Once checkpointing has been done, write a checkpoint to the log. • Do recovery from last checkpoint. www.assignmentpoint.com

13. Types of Failure and the Recovery Mechanism I • Transaction - local failure • As only one transaction is affected, perform a ROLLBACK. • System-wide, no damage to DB • All transactions in progress are affected, and hence undo changes made by transactions in progress. • Redo every committed transaction for which it is not known whether all changes have been physically written to database. • If possible, restart transactions that were rolled back. www.assignmentpoint.com

14. Types of Failure and the Recovery Mechanism II • System-wide failure with damage to database • Restore database from latest backup, and redo all committed transactions from the log file. • Clearly, this is a very slow process. www.assignmentpoint.com

15. Concurrency Control • Concurrently executed transactions may interleave with each other in such a way that they produce an incorrect overall result, even though each transaction is correct. • Consider the following example: • User1 wants to transfer $50 from A to B(T1). User 2 wants to transfer 10% from A to B (T2). www.assignmentpoint.com

16. Serial Execution I T1 T2 read(A) A := A - 50 write(A) read(B) B := B + 50 write(B) read(A) temp = A *.1 A := A - temp write(A) read(B) B := B + temp write(B) www.assignmentpoint.com

17. Serial Execution II T1 T2 read(A) temp = A *.1 A := A - temp write(A) read(B) B := B + temp write(B) read(A) A := A - 50 write(A) read(B) B := B + 50 write(B) www.assignmentpoint.com

18. Good Interleaving T1 T2 read(A) A := A - 50 write(A) read(A) temp = A *.1 A := A - temp write(A) read(B) B := B + 50 write(B) read(B) B := B + temp write(B) www.assignmentpoint.com

19. Bad Interleaving T1 T2 read(A) A := A - 50 read(A) temp = A *.1 A := A - temp write(A) read(B) write(A) read(B) B := B + 50 write(B) B := B + temp write(B) www.assignmentpoint.com

20. The Lost Update Problem Transaction A time Transaction B - - - - RETRIEVE t - - - - RETRIEVE t - - UPDATE t - - - - UPDATE t - - t1 t2 t3 t4 www.assignmentpoint.com

21. Uncommitted Dependency Problem Transaction A time Transaction B - - - - - UPDATE t - - RETREIVE t - - - - ROLLBACK - t1 t2 t3 www.assignmentpoint.com

22. Uncommitted Dependency Problem Transaction A time Transaction B - - - - - UPDATE t - - UPDATE t - - - - ROLLBACK - t1 t2 t3 www.assignmentpoint.com

23. The Inconsistent Analysis Problem • Example: • Acc 1 = 40; Acc 2 = 50; Acc 3 = 30 • Transaction A : Sum all account balances • Transaction B : Transfer 10 from 3 to 1 www.assignmentpoint.com

24. The Inconsistent Analysis Problem Transaction A time Transaction B RETRIEVE Acc 1 : - Sum = 40 - - - RETRIEVE Acc 2 : - Sum = 90 - - - - RETRIEVE Acc 3: - - - UPDATE Acc3: - 30 -> 20 - - - RETRIEVE Acc 1: - - - UPDATE Acc 1: - 40 -> 50 - - - COMMIT - RETRIEVE Acc 3 : - Sum = 110 (not 120) - t1 t2 t3 t4 t5 t6 t7 t8 www.assignmentpoint.com

25. Serializable Executions • An interleaved execution of some transactions is correct if it produces the same result as some serial execution of the transactions. • Such an execution is called serializable. • A concurrency control scheme must prevent non-serializable execution from occurring. • One possibility is locking. www.assignmentpoint.com

26. Locks • While one transaction accesses a data item, no other transaction should modify it. • Locking ensures that a data item can be updated only if the transaction holds a lock on the data item. • Two types of lock: • Shared locks • Exclusive locks www.assignmentpoint.com

27. Shared locks • If a transaction holds a shared lock (S-lock) on an object, other transactions can also request S-locks. • However, a transaction cannot acquire an exclusive lock on the object. • If a transaction has the only shared lock on an object, it can be promoted to an exclusive lock. www.assignmentpoint.com

28. Exclusive Locks • If a transaction holds an exclusive lock (X-lock) on an object, no other transaction can acquire a lock on the object, or access it. • To update a record R through some transaction T • T must obtain an X-lock on R • The X-lock must be retained until the end of T, either through COMMIT or ROLLBACK. www.assignmentpoint.com

29. Granting of Locks • Care must be taken to ensure that a transaction will not be starved. Lock Compatibility Matrix • A lock request should never get blocked by a lock request that is made later. www.assignmentpoint.com

30. Good Interleaving with X-locks I T1 T2 lock-X(A) read(A) A := A - 50 write(A) unlock(A) lock-X(A) read(A) temp = A *.1 A := A - temp write(A) unlock(A) www.assignmentpoint.com

31. Good Interleaving with X-locks II T1 T2 lock-X(B) read(B) B := B + 50 write(B) unlock(B) lock-X(B) read(B) B := B = temp write(B) unlock(X) www.assignmentpoint.com

32. X-locks Block Bad Interleaving T1 T2 lock-X(A) read(A) A := A - 50 read(A) • The read(A) of T2 cannot be executed because T1 has an X-lock on A. www.assignmentpoint.com

33. Deadlocks • X-locks may lead to deadlocks. • This arises when two transactions are mutually excluded from accessing the next record required to computer their transaction. www.assignmentpoint.com

34. Our Example Slightly Changed T1 T2 read(B) B := B + 50 write(B) read(A) temp = A *.1 A := A - temp write(A) read(A) A := A - 50 write(A) read(B) B := B = temp write(B) www.assignmentpoint.com

35. Example of Deadlock T1 T2 lock-X(B) update B lock-X(A) update A request lock(A) wait for T2 to release lock on A request lock(B) waiting for T1 to release lock on B www.assignmentpoint.com

36. Deadlocks • Deadlocks can be overcome by: • Prevention • Detection and Recovery www.assignmentpoint.com

37. Technique 1 - Preventing Deadlocks • A transaction must lock all the data items it needs before execution begins. • Data-item utilization may be slow. www.assignmentpoint.com

38. Technique 2 -Preventing Deadlocks • Ordering • If a transaction requires access to two records, make sure that they are always accessed in the same order. • In our case, always access A before B. • May slow down operations considerably. www.assignmentpoint.com

39. Detection and Recovery • Periodically, let DBMS check to determine if line waiting for resource exceeds some limit. • Use graph manipulation to detect deadlocks: • Draw arrow from transaction to record being sought, and from record to transaction using it. • If graph has cycles, then we have deadlock. • If deadlock detected, cancel one transaction and advance other. www.assignmentpoint.com

40. Example of Deadlock Detection T1 T2 Record A Record B www.assignmentpoint.com