What is a Transaction?

1. What is a Transaction? • Logical unit of work • Must be either entirely completed or aborted • No intermediate states are acceptable Figure 9.1 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

2. Example Transaction • Examine current account balance • Consistent state after transaction • No changes made to Database SELECT ACC_NUM, ACC_BALANCEFROM CHECKACCWHERE ACC_NUM = ‘0908110638’; Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

3. Example Transaction • Register credit sale of 100 units of product X to customer Y for $500 • Consistent state only if both transactions are fully completed • DBMS doesn’t guarantee transaction represents real-world event UPDATE PRODUCTSET PROD_QOH = PROD_QOH - 100WHERE PROD_CODE = ‘X’; UPDATE ACCT_RECEIVABLE SET ACCT_BALANCE = ACCT_BALANCE + 500WHERE ACCT_NUM = ‘Y’; Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

4. Transaction Properties (ACID) • Atomicity • All or nothing • Consistency provided • Database is consistent before and after transaction • Database not guaranteed consistent during a transaction • Isolation • Transaction data isolated from other transactions until its execution is complete • Durability • Permanently recorded in DB and must be protected Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

5. Transaction Management with SQL • Transaction support • COMMIT • ROLLBACK • Transaction begins with a BEGIN TRANSACTION and ends with COMMIT or ROLLBACK • At COMMIT point (synch point) all updates made permanent and locks released • Requires the use of a log or journal Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

6. Transaction Log • Tracks all transactions that update database • Needed in ROLLBACK operation • May be used to recover from system failure • Log stores • Record for beginning of transaction • Each transaction component • Type of action (insert, delete, update) • Names of objects involved • Before and after images of affected objects • Pointers to previous and next entries • Commit Statement Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

7. Transaction Log Example Table 9.1 Write-ahead Log Rule: Log is physically written before COMMIT completes to enable restart Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

8. Checkpoints • How to know at restart time which transactions to undo and which ones to redo? • Checkpoints periodically taken • “Taking a checkpoint” involves force-writing buffers and writing a checkpoint record to the log consisting of all transactions in progress at checkpoint time • For example ... Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

9. Algorithm for Undo/Redo • At restart from checkpoint, set UNDO list to transactions that were in progress at the time • Set REDO list to null • Search forward through log starting from checkpoint • If BEGIN TRANSACTION found, add to UNDO list • If COMMIT found, move from UNDO to REDO Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

10. Concurrency Control • Coordinates simultaneous transaction execution in multiprocessing database • Potential problems in multiuser environments • Lost updates • Uncommitted data • Inconsistent retrievals Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

11. Lost Updates Table 9.2 Normal execution of two transactions Table 9.3 Lost update Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

12. Uncommitted Data Table 9.4 Table 9.5 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

13. Inconsistent Retrievals • Also known as “dirty reads” or “unrepeatable reads” • Occurs when a transaction reads several values, some of which are being updated • Example: T1 sums the total quantity on hand while T2 transfers an amount on hand from one item to another (correcting an incorrect posting, for instance) Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

14. Inconsistent Retrievals The two transactions T2 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

15. Inconsistent RetrievalsResults with interleaved transactions Table 9.8 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

16. Serializability • It is possible for T1 followed by T2 to result in a different state than T2 followed by T1 • But both would be correct (consistent) from the DB point of view • Transaction serializabilitymeans that transactions executing concurrently must be interleaved in such a way that the resulting DB state is equal to someserial execution of the same transactions • Goal is to avoid the concurrency problems (lost update, uncommitted data, inconsistent retrieval) Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

17. The Scheduler • Establishes order of concurrent transaction execution • Interleaves execution of database operations to ensure serializability • Uses a protocol for producing serializable schedules: • Locking • Time stamping • Optimistic • Ensures efficient use of computer’s CPU Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

18. Concurrency Control with Locking Methods • Lock guarantees current transaction exclusive use of data item • Acquire lock prior to access • Lock released when transaction is completed • DBMS automatically initiates and enforces locking procedures • Lock granularity indicates level of lock use Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

19. Locks • Read (sharing) or Write (exclusive) • At various levels: DB, table, page, row, field • Many Read locks simultaneously possible for a given item, but only one Write lock • Transaction that requests a lock that cannot be granted must wait • Possible to upgrade Read lock to Writelock or downgrade Writelock to Read lock • Locks released at commit point (or earlier) Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

20. Shared/Exclusive Locks • Shared (Read) • Exists when concurrent transactions granted READ access • Issued when transaction wants to read and exclusive lock not held on item • Exclusive (Write) • Exists when access reserved for locking transaction • Used when potential for conflict exists • Issued when transaction wants to update unlocked data Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

21. Problems with Locking • Transaction schedule may not be serializable • Managed through two-phase locking • Schedule may create deadlocks • Managed by using deadlock detection and prevention techniques Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

22. Two-Phase Locking Protocol (2PL) • Growing phase: acquire all locks needed • Shrinking phase: after releasing a lock, acquire no new locks • Consequently • No unlock operation can precede a lock operation in the same transaction • No data are affected until all locks are obtained • 2PL solves the 3 problems of concurrency Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

23. Two-Phase Locking Protocol Figure 9.6 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

24. Deadlock • Also called deadly embrace • “Occurs when two transactions wait for each other to unlock data” • Wrong! • eg, T1 waits for T2, T2 waits for T3, T3 waits for T1 • Notation: T1  T2 means T1 waits for data held by T2 • A system is in deadlock if there is a set of waiting transactions {T0, T1, …, Tn} such that T0  T1, T1  T2, … , Tn  T0 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

25. Wait-for-graph Deadlock Detection Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

26. Recovery from Deadlock One or more transactions must be aborted • Determine transactions to roll back • Want to incur minimum “cost” • May be based on time running, time left, amount of data used, how many transactions are involved in rollback (cascades) • Total or partial rollback • Starvation • Can happen that same transaction is always chosen as victim • Use the number of times rolled back in determining the cost Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

27. Deadlock Prevention • Could require all locks to be acquired at once • but may not always know what is needed • potentially inefficient -- many items locked unnecessarily for possibly long time • Ordering of data items • once a transaction locks an item, it cannot lock anything occurring earlier in the ordering • Preemption and rollback with timestamps • wait-die • wound-wait Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

28. Concurrency Control with Time Stamping Methods • Assigns global unique time stamp to each transaction • Produces order for transaction submission • Properties • Uniqueness • Monotonicity • Some time stamping necessary to avoid “livelock”: where a transaction cannot acquire any locks even though the DBMS is not deadlocked (eg, unfair waiting algorithm) Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

29. Deadlock Prevention with Time Stamps • Wait-die • If T1 requests item locked by T2, then T1 is allowed to wait only if it is older than T2 (smaller time stamp). Otherwise T1 is rolled back (dies) • Wound-wait • If T1 requests item locked by T2, then T1 is allowed to wait only if T1 is younger than T2 (larger time stamp). Otherwise T2 is rolled back (wounded by the older transaction) • Both avoid starvation, since eventually a failing transaction will be the oldest Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

30. Concurrency Control with Optimistic Methods • Assumes most database operations do not conflict • Transaction executed without restrictions until committed • Transactions execute in 3 Phases in order: • Read Phase • Validation Phase • Write Phase • Transactions are still interleaved, but may have to be rolled back Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

31. Phases in Validation-based Control • Read phase • Transaction reads data and stores in local variables • Any writes are made to local variables without updating the actual DB • Validation phase • Validation test performed to see whether DB can be changed without violating serializability • Relies on time stamping each transaction at each phase • Write phase • If the validation test is successful, the transaction updates the actual DB. Otherwise it is rolled back. Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

32. Database Recovery Management • Restores a database to previously consistent state • Based on the atomic transaction property • Level of backup • Full backup • Differential • Transaction log Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

33. Transaction Recovery • Deferred-write and Deferred-update • Changes are written to the transaction log • Database updated after transaction reaches commit point • Write-through • Immediately updated by during execution • Before the transaction reaches its commit point • Transaction log also updated • Transaction fails, database uses log information to ROLLBACK Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel