Chapter 20 Transaction Management Agenda Transaction Concurrent Processing

Chapter 20 Transaction Management

Agenda • Transaction • Concurrent Processing • Backup and Recovery

Transaction - I • Definition – An action or actions to read or update the contents of the database • Types – Committed – Aborted – Compensating

Transaction - II • Property of Transactions (ACID) – Atomicity (all or nothing) – Consistency (state by database constraints and applications) – Isolation (independent) – Durability (permanent)

Concurrent Processing • Definition • Problems • Control

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

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

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

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

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

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

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

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

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

Optimistic Technique • Read phase • Validate phase • Write phase

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)

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)

Recovery Methods • Reprocessing – Record all transactions since last backup and replay the following transactions • Rollfoward – 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

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

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

Assignments • Review chapters 5 -6, 11 -14, and 19 -20 • Read chapter • Exam 3 – Date: 5/17/07 • Project – Due date: 5/22/07 – Place: Tahoe 2090 – Time: 12 noon

End of MIS 150 • Study! • There is no easy way out! • Have a happy and safe summer!!