1. Chapters 20 Transaction Management

2. Agenda • Properties of Transaction • Concurrent Processing • Database Protection • Recovery

3. Property of Transactions (ACID) • Atomicity • Consistency • Isolation • Durability

4. Concurrent Processing • Definition • Problems • Control

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

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

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

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

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

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

11. Deadlock • Definition • Tow or more transactions each wait for locks held by other transaction • Livelock • Control • Wait-Die • Wound-wait

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

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

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

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

16. Database Protection • Back up • 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)

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

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

19. Failure & Recovery • Aborted transaction • Rollback • Incorrect data • Rollback or restart from checkpoint • System failure • Rollback or restart from checkpoint • Database destroyed • Rollforward from last backup

20. Points To Remember • Properties of Transaction • Concurrent Processing • Database Protection • Recovery

21. Assignments • Review chapters 5-6, 11-13, 18-19, 24-26 • Exam 3 • Date: 5/13/04 • Project • Due date: 5/20/04 • Place: MIS Department Office

22. End of MIS150 • Exam date: 5/13/03 • Study! Study! Study! • Have a happy and safe holiday!!