Transaction Management: Concurrency Control and Recovery

1. Chapters 20 Transaction Management

2. Agenda • Transaction • Concurrent Processing • Backup and Recovery

3. Transaction - I • Definition • An action or actions to red or update the contents of the database • Types • Committed • Aborted (rolled back or undone) • Compensating

4. Transaction - II • Property of Transactions (ACID) • Atomicity (all or nothing) • Consistency (state) • Isolation (independent) • Durability (permanent)

5. Concurrent Processing • Definition • Problems • Control

6. Concurrent Processing • Multiprogramming • Interleaved between two transactions • CPU • I/O • Logical unit of work

7. Concurrent Processing Problem • No problem • Write different data • Update different data • Read the same data • Problem • Write the same data • Update the same data

8. Concurrent Processing Problems • Lost update • Two transactions simultaneously update the same files • Uncommitted update • Transaction 2 uses the result updated by transaction 1 • Transaction 1 aborts and rolls back • Transaction 2 commits • Inconsistent Analysis • Transaction 1 reads • Transaction 2 reads and uses for calculation • Transaction 1 updates and commits • Transaction 2 updates and commits

9. SERIALIZABILITY • Transaction results form concurrent processing are the same as if stand-alone sequential processing was used • Ensure no anomalies arise from concurrent processing

10. Concurrency Control • Locking • Deadlock • Two-phase locking • Timestamping • Optimistic technique

11. Locking • Types • Shared Locks vs. Exclusive Locks • Read Locks vs. Write Locks • Upgrade vs. Downgrade • Granularity • Database • file • page • record • field

12. Deadlock • Definition • Tow or more transactions each wait for locks held by other transaction • Livelock • Control • Wait-Die • Wound-wait • Time out • Conservative 2PL

13. Two-phase Locking • Growing phase • Get all locks • Upgrade locks • Shrinking phase • Downgrade locks • Once starting to release a lock - no more new locks

14. Timestamping • Timestamp • unique identifier as relative starting time of a transaction • Read-timestamp & write timestamp • Timestamp protocol • Transactions with smaller timestamps get priority in the event of conflict • Transaction is only allowed on the item with smaller read-timestamp or write timestamp

15. Optimistic Technique • Read phase • Validate phase • Write phase

16. Database Recovery • Definition • Restoring the database to its correct state in the event of a failure • Reasons • Physical (fire, flood, etc.) • Sabotage • Carelessness • Hardware • Software (application/system)

17. Database Backup • Backup • Copy of the database • Transaction log • Transaction ID, time, operation, object, before image, after image, prior pointer, next pointer • Checkpoint • Synchronize transaction log and the database • Write data from buffers to database on the disk • Write checkpoint to log identify current transaction(s)

18. Recovery Methods • Reprocessing • Record all transactions since last backup and replay those transactions • Rollforward • Use the transaction log to change any committed transactions on the database or since last checkpoint • Rollback • Use transaction log to undo any aborted transactions

19. Shadow Paging Method • Current page table vs. Shadow page table • Pros & cons • Faster • Less overhead • Data fragmentation • Reclaim inaccessible blocks

20. Points To Remember • Properties of Transaction • Concurrent Processing • Backup and Recovery

21. Assignments • Review chapters 5-6, 11-20, and 25-27 • Read chapter • Exam 3 • Date: • Project • Due date: • Place:

22. End of MIS150 • Exam date: • Study! Study! Study! • Have a happy and safe summer!!