Chapterb19 Transaction Management

1. Chapterb19Transaction Management Transaction: An action, or series of actions, carried out by a single user or application program, which reads or updates the contents of the database. Also a logical unit of work on the database. Single statement, part or all of a program

2. Transaction Management Consistent/inconsistent state of DB Consistency implies all integrity constraints are satisfied. If a transaction does not complete properly, update all affected components, referential integrity may be lost placing the database in an inconsistent state.

3. Transaction Management Outcomes of a transaction: committed - successful completion with DB in a new consistent state. Aborted - transaction did not execute successfully

4. Transaction Management Aborted transaction DB must be returned to the consistent state it was in before the transaction started. Transaction must be rolled back. DBMS systems usually provide: BEGIN TRANSACTION, COMMIT, ROLLBACK

5. Transaction Management Transaction Properties: ACID Atomicity - all or nothing. A transaction is indivisible - performed in its entirety or not performed at all. Consistency - a transaction must move the DB from one consistent state to another consistent state. Isolation - Transactions execute independently of one another. Transactions do not interfere with one another. Durability - completed transactions are permanently recorded

6. ACID example • Transfer $50 from account A to account B • 1. Read A • 2. A = A – 50 • 3. Write A • 4. Read B • 5. B = B + 50 • 6. Write B

7. ACID example Consistency: the sum of A and B is unchanged by the transaction Atomicity : if the system fails after step 3 and before step 6, the system should ensure that its updates are not reflected in the database, else inconsistency will result. Duribility : once the user has been notified that the transaction has been completed, the updates to the database by the transaction must persist despite failures. Isolation : if between steps 3 and 6, another transaction is allowed to access the partially updated database, it will see an inconsistent database. ACID properties can be ensured by running the transactions serially. However, loose the benefits of executing multiple transactions Concurrently.

8. Transaction Management Concurrency Control managing simultaneously operations on the database without having them interfere with one another. Interleaved transactions from multiple users must satisfy the ACID properties producing results as if they were executed serially.

9. Transaction Management Transaction Schedule a sequence of operations by a set of concurrent transactions that preserves the order of the operations in each of the individual transactions. Serial schedule a schedule where the operations of each transaction are executed consecutively without any interleaved operations from other transactions. Nonserial schedule a schedule where the operations from a set of concurrent transactions are interleaved.

10. Example Schedule T1 transfers $50 from A to B, and T2 transfers 10% of the balance From A to B. Schedule 1: T1 T2 read A A = A – 50 write A read B B = B + 50 write B read A tmp = A*0.1 A = A – tmp write A read B B = B + tmp write B

11. Concurrent Trancactions T1 T2 read A A = A –50 Write A Read A tmp = A*0.1 A = A – tmp write A Read B B = B + 50 Write B read B B = B + tmp write B

12. Transaction Management Serializable schedule a nonserial schedule of concurrent transactions that produces the same results as some serial execution.

13. Nonserial Schedule • Schedule where operations from set of concurrent transactions are interleaved. • Objective of serializability is to find nonserial schedules that allow transactions to execute concurrently without interfering with one another. • In other words, want to find nonserial schedules that are equivalent to some serial schedule. Such a schedule is called serializable.

14. Transaction Management Locking a procedure used to control concurrent access to data. Used to deny access to a database component being used by another transaction. Shared lock: transaction is permitted reads but not updates Exclusive lock: transaction can both read and update the item

15. Transaction Management To guarantee serializability a protocol known as two-phase locking is often used. (2PL) all locking operations precede the first unlock operation growing phase - acquiring all needed locks but not releasing any locks. Shrinking phase - releases locks but not allowed to to acquire new locks.

16. Database recovery • The process of restoring the database to a correct state in the event of a failure • Failure causes: • System crashes media failures • Application errors user errors • Sabotage natural disasters

17. Database recovery • Transactions represent the basic unit of recovery in a database system • Recovery manager must ensure the ACID properties are maintained.

18. Database recovery • Transaction processing involves locating a record on disk, transferring it to a main memory buffer, updating the buffer data and writing the buffer contents back to disk. • Once the buffer is flushed the changes can be considered permanent. (commit)

19. Database recovery • Failure at various stages of a transaction may require: • a redo – a commit has occurred but the failure prevented the disk from being updated properly (durability) • an undo (rollback) – failure before a commit and transaction effects to date must be reversed.

20. Recovery Facilities • DBMS should provide following facilities to assist with recovery: • Backup mechanism, which makes periodic backup copies of database. • Logging facilities, which keep track of current state of transactions and database changes. • Checkpoint facility, which enables updates to database in progress to be made permanent. • Recovery manager, which allows DBMS to restore database to consistent state following a failure.

21. Log File • Contains information about all updates to database: • Transaction records. • Checkpoint records. • Often used for other purposes (for example, auditing).

22. Log File • Transaction records contain: • Transaction identifier. • Type of log record, (transaction start, insert, update, delete, abort, commit). • Identifier of data item affected by database action (insert, delete, and update operations). • Before-image of data item. • After-image of data item. • Log management information.

23. Checkpointing Checkpoint Point of synchronization between database and log file. All buffers are force-written to secondary storage. • Checkpoint record is created containing identifiers of all active transactions. • When failure occurs, redo all transactions that committed since the checkpoint and undo all transactions active at time of crash.

24. ORACLE Memory Components Buffer Cache Buffers the size of DB blocks that store data needed by SQL statements Can hold several rows of a table. Data changes to the rows are kept here and can be written later. Redo Log buffer Store in memory the redo entry information generated by DML statements until the changes are written to disk. A redo entry is a small amount of info produced and saved by Oracle to reconstruct, or redo, changes made to the database by insert, update, delete, create, alter and drop statements. If a failure occurs the DBA can use redo info to recover the DB to the point of the DB failure.

25. ORACLE Basics • Moving data changes from memory to disk • Two background process are used • DBW0 and LGWR LGWR – log writer process writes redo log entries from the redo log buffer to online redo log files when a transaction commits the redo log buffer is 1/3 full more than 1MB of changes in the redo log buffer before DBW0 writes dirty blocks in the DB buffer to DB files tells DBWR to write dirty buffers to disk at checkpoints

26. ORACLE Basics • DBW0 database writer process • Writes dirty data blocks from buffer cache to disk • When • The server process needs to make room in the buffer cache to read more data in • Told to write data to disk by the LGWR process • Every 3 seconds due to a timeout • The number of dirty buffers reaches a threshold CKPT (Checkpoint) – causes DBWR to write all the dirty blocks since last checkpoint to the data files and other info to record the checkpoint

27. ORACLE Basics • The LGWR writes the online redo log files in a cyclical fashion. • Wraps around to first when all are filled. ARCH – a process which, when archiving is on, makes a copy of each redo log file before overwriting it.

28. ORACLE Basics Database Recovery requires a backup of the database and, for recovery to the point of the last committed transaction, the archived redo log files since the last backup.

29. Deadlock An impasse that may result when two (or more) transactions are each waiting for locks held by the other to be released.

30. Deadlock • Only one way to break deadlock: abort one or more of the transactions. • Deadlock should be transparent to user, so DBMS should restart transaction(s). • Three general techniques for handling deadlock: • Timeouts. • Deadlock prevention. • Deadlock detection and recovery.

31. Timeouts • Transaction that requests lock will only wait for a system-defined period of time. • If lock has not been granted within this period, lock request times out. • In this case, DBMS assumes transaction may be deadlocked, even though it may not be, and it aborts and automatically restarts the transaction.

32. Deadlock Prevention • DBMS looks ahead to see if transaction would cause deadlock and never allows deadlock to occur. • Could order transactions using transaction timestamps: • Wait-Die - only an older transaction can wait for younger one, otherwise transaction is aborted (dies) and restarted with same timestamp.

33. Deadlock Prevention • Wound-Wait - only a younger transaction can wait for an older one. If older transaction requests lock held by younger one, younger one is aborted (wounded).

34. Deadlock Detection and Recovery • DBMS allows deadlock to occur but recognizes it and breaks it. • Usually handled by construction of wait-for graph (WFG) showing transaction dependencies: • Create a node for each transaction. • Create edge Ti -> Tj, if Ti waiting to lock item locked by Tj. • Deadlock exists if and only if WFG contains cycle. • WFG is created at regular intervals.

35. Example - Wait-For-Graph (WFG)

36. Recovery from Deadlock Detection • Several issues: • choice of deadlock victim; • how far to roll a transaction back; • avoiding starvation.

37. Timestamping • Transactions ordered globally so that older transactions, transactions with smaller timestamps, get priority in the event of conflict. • Conflict is resolved by rolling back and restarting transaction. • No locks so no deadlock.

38. Timestamping Timestamp A unique identifier created by DBMS that indicates relative starting time of a transaction. • Can be generated by using system clock at time transaction started, or by incrementing a logical counter every time a new transaction starts.

39. Timestamping • Read/write proceeds only if last update on that data item was carried out by an older transaction. • Otherwise, transaction requesting read/write is restarted and given a new timestamp. • Also timestamps for data items: • read-timestamp - timestamp of last transaction to read item; • write-timestamp - timestamp of last transaction to write item.