1 / 40

Transaction Management

Transaction Management. Database recovery Concurrency control. 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.

nerys
Download Presentation

Transaction Management

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 Management Database recovery Concurrency control

  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.

  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.

  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.

  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

  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

  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.

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

  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

  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.

  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.

  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.

  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.

  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.

  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).

  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)

  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)

  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)

  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)

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

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

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

  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

  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

  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.

  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

  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.

  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.

  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.

  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)

  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)

  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.

  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.

  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)

  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

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

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

  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.

  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.

  40. Example of Deadlock Detection T1 T2 Record A Record B

More Related