1 / 40

Transaction Management Database recovery Concurrency control

Transaction Management Database recovery Concurrency control. www.AssignmentPoint.com. Transactions. A transaction is a sequence of operations that perform a single logical task.

Download Presentation

Transaction Management Database recovery Concurrency control

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related