9 What is a Transaction Logical unit of

9 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, 5 th Edition, Rob & Coronel

9 Example Transaction • Examine current account balance SELECT ACC_NUM, ACC_BALANCE FROM CHECKACC WHERE ACC_NUM = ‘ 0908110638’; • Consistent state after transaction • No changes made to Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel

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

9 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, 5 th Edition, Rob & Coronel

9 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, 5 th Edition, Rob & Coronel

9 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, 5 th Edition, Rob & Coronel

9 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, 5 th Edition, Rob & Coronel

9 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, 5 th 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, 5 th Edition, Rob & Coronel

9 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, 5 th Edition, Rob & Coronel

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

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

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

9 Inconsistent Retrievals The two transactions T 2 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 14

9 Inconsistent Retrievals Results with interleaved transactions Table 9. 8 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 15

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

9 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, 5 th Edition, Rob & Coronel

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, 5 th Edition, Rob & Coronel 9

9 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 Write lock or downgrade Write lock to Read lock • Locks released at commit point (or earlier) Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel

9 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, 5 th Edition, Rob & Coronel

9 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, 5 th Edition, Rob & Coronel

9 Two-Phase Locking Protocol (2 PL) • 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 • 2 PL solves the 3 problems of concurrency Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel

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

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

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

Recovery from Deadlock 9 One or more transactions must be aborted 1 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) 2 Total or partial rollback 3 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, 5 th Edition, Rob & Coronel

9 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, 5 th Edition, Rob & Coronel

9 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, 5 th Edition, Rob & Coronel

9 Deadlock Prevention with Time Stamps • Wait-die – If T 1 requests item locked by T 2, then T 1 is allowed to wait only if it is older than T 2 (smaller time stamp). Otherwise T 1 is rolled back (dies) • Wound-wait – If T 1 requests item locked by T 2, then T 1 is allowed to wait only if T 1 is younger than T 2 (larger time stamp). Otherwise T 2 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, 5 th Edition, Rob & Coronel

Concurrency Control with Optimistic Methods 9 • 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, 5 th Edition, Rob & Coronel

9 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, 5 th Edition, Rob & Coronel

9 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, 5 th Edition, Rob & Coronel

9 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, 5 th Edition, Rob & Coronel