Chapter 9 9 Transaction Management and Concurrency Control

Chapter 9 9 Transaction Management and Concurrency Control Database Systems: Design, Implementation and Management 4 th Edition Peter Rob & Carlos Coronel

What Is a Transaction? 9 4 A transaction is a logical unit of work that must be either entirely completed or aborted; no intermediate states are acceptable. u Most real-world database transactions are formed by two or more database requests. u A database request is the equivalent of a single SQL statement in an application program or transaction. u A transaction that changes the contents of the database must alter the database from one consistent database state to another. u To ensure consistency of the database, every transaction must begin with the database in a known consistent state.

Example Of A Transaction 9 Figure 9. 1

What Is a Transaction? 4 Evaluating Transaction Results 9 u Examining the current balance for an account: SELECT ACC_NUM, ACC_BALANCE FROM CHECKACC WHERE ACC_NUM = ‘ 0908110638’; l The database remains in a consistent state after the transaction, because it did not alter the database.

What Is a Transaction? 4 Evaluating Transaction Results 9 u An accountant wishes to register the credit sale of 100 units of product X to customer Y in the amount of $500. 00: l l Reducing product X’s Quantity on hand by 100. Adding $500. 00 to customer Y’s accounts receivable. UPDATE PRODUCT SET PROD_QOH = PROD_QOH - 100 WHERE PROD_CODE = ‘X’; UPDATE ACCREC SET AR_BALANCE = AR_BALANCE + 500 WHERE AR_NUM = ‘Y’; l If the above two transactions are not completely executed, the transaction yields an inconsistent database.

What Is a Transaction? 4 Evaluating Transaction Results 9 u The DBMS does not guarantee that the semantic meaning of the transaction truly represents the real-world event. l Although the syntax of the following UPDATE command is correct, its use yields incorrect results. UPDATE PRODUCT SET PROD_QOH = PROD_QOH + 10 WHERE PROD_CODE = ‘X’;

What Is a Transaction? 4 Transaction Properties 9 u Atomicity requires that all operations of a transaction be completed; if not, the transaction is aborted. u Durability indicates the permanence of the database’s consistent state. u Serializability describes the result of the concurrent execution of several transactions. This property is important in multi-user and distributed databases. u Isolation means that the data used during the execution of a transaction cannot be used by a second transaction until the first one is completed.

What Is a Transaction? 4 Transaction Management with SQL 9 u Transaction support is provided by two SQL statements: COMMIT and ROLLBACK. u When a transaction sequence is initiated, it must continue through all succeeding SQL statements until one of the following four events occurs: l l A COMMIT statement is reached. A ROLLBACK statement is reached. The end of a program is successfully reached (COMMIT). The program is abnormally terminated (ROLLBACK).

What Is a Transaction? 4 Transaction Management with SQL 9 u Example: UPDATE PRODUCT SET PROD_QOH = PROD_QOH - 100 WHERE PROD_CODE = ‘ 345 TYX’; UPDATE ACCREC SET AR_BALANCE = AR_BALANCE + 3500 WHERE AR_NUM = ‘ 60120010’; COMMIT;

What Is a Transaction? 4 The Transaction Log 9 u A transaction log keeps track of all transactions that update the database. u The information stored in the log is used by the DBMS for a recovery requirement triggered by a ROLLBACK statement or a system failure. u The transaction log stores before-and-after data about the database and any of the tables, rows, and attribute values that participated in the transaction. u The transaction log is itself a database, and it is managed by the DBMS like any other database.

A Transaction Log 9 Table 9. 1

Concurrency Control 9 4 Concurrency control coordinates simultaneous execution of transactions in a multiprocessing database. u The objective of concurrency control is to ensure the serializability of transactions in a multi-user database environment. u Simultaneous execution of transactions over a shared database can create several data integrity and consistency problems: l l l Lost Updates. Uncommitted Data. Inconsistent retrievals.

Concurrency Control 4 Lost Updates 9 u Two concurrent transactions update PROD_QOH: TRANSACTION COMPUTATION T 1: Purchase 100 units T 2: Sell 30 units PROD_QOH = PROD_QOH + 100 PROD_QOH = PROD_QOH - 30 u See Table 9. 2 for the serial execution under normal circumstances. u See Table 9. 3 for the lost update problems resulting from the execution of the second transaction before the first transaction is committed.

9 Table 9. 2 Normal Execution Of Two Transactions Table 9. 3 Lost Updates

Concurrency Control 4 Uncommitted Data 9 u Data are not committed when two transactions T 1 and T 2 are executed concurrently and the first transaction is rolled back after the second transaction has already accessed the uncommitted data -- thus violating the isolation property of the transaction. TRANSACTION COMPUTATION T 1: Purchase 100 units T 2: Sell 30 units PROD_QOH = PROD_QOH + 100 (Rolled back) PROD_QOH = PROD_QOH - 30

9 Table 9. 4 Correct Execution Of Two Transactions Table 9. 5 An Uncommitted Data Problem

Concurrency Control 4 Inconsistent Retrievals 9 u Inconsistent retrievals occur when a transaction calculates some summary (aggregate) functions over a set of data while other transactions are updating the data. u Example: l l T 1 calculates the total quantity on hand of the products stored in the PRODUCT table. At the same time, T 2 updates the quantity on hand (PROD_QOH) for two of the PRODUCT table’s products.

Retrieval During Update 9 Table 9. 6

Transaction Results: Data Entry Correction 9 Table 9. 7

Inconsistent Retrievals 9 Table 9. 8

Concurrency Control 4 The Scheduler 9 u The scheduler establishes the order in which the operations within concurrent transactions are executed. u The scheduler interleaves the execution of database operations to ensure serializability. u To determine the appropriate order, the scheduler bases its actions on concurrency control algorithms, such as locking or time stamping methods. u The scheduler also makes sure that the computer’s CPU is used efficiently.

Read/Write Conflict Scenarios: Conflicting Database Operations Matrix 9 Table 9. 9

Concurrency Control with Locking Methods 4 Concurrency can be controlled using locks. 9 4 A lock guarantees exclusive use of a data item to a current transaction. 4 A transaction acquires a lock prior to data access; the lock is released (unlocked) when the transaction is completed. 4 All lock of information is managed by a lock manager.

Concurrency Control with Locking Methods 4 Lock Granularity 9 u Lock granularity indicates the level of lock use. l Database level (See Figure 9. 2) l Table level (See Figure 9. 3) l Page level (See Figure 9. 4) l Row level (See Figure 9. 5) l Field level

A Database-Level Locking Sequence 9 Figure 9. 2

An Example Of A Table-Level Lock 9 Figure 9. 3

An Example Of A Page-Level Lock 9 Figure 9. 4

An Example Of A Row-Level Lock 9 Figure 9. 5

Concurrency Control with Locking Methods 4 Binary Locks 9 u A binary lock has only two states: locked (1) or unlocked (0). u If an object is locked by a transaction, no other transaction can use that object. u If an object is unlocked, any transaction can lock the object for its use. u A transaction must unlock the object after its termination. u Every transaction requires a lock and unlock operation for each data item that is accessed.

An Example Of A Binary Lock 9 Table 9. 10

Concurrency Control with Locking Methods 9 Exclusive Locks Shared Locks 4 An exclusive lock exists when access is specially reserved for the transaction that locked the object. 4 A shared lock exists when concurrent transactions are granted READ access on the basis of a common lock. 4 The exclusive lock must be used when the potential for conflict exists. 4 A shared lock produces no conflict as long as the concurrent transactions are read only. 4 An exclusive lock is issued when a transaction wants to write (update) a data item and no locks are currently held on that data item. 4 A shared lock is issued when a transaction wants to read data from the database and no exclusive lock is held on that data item.

Concurrency Control with Locking Methods 9 4 Potential Problems with Locks The resulting transaction schedule may not be serializable. u The schedule may create deadlocks. u 4 Solutions Two-phase locking for the serializability problem. u Deadlock detection and prevention techniques for the deadlock problem. u

Concurrency Control with Locking Methods 9 4 Two-Phase Locking u The two-phase locking protocol defines how transactions acquire and relinquish locks. It guarantees serializability, but it does not prevent deadlocks. u In a growing phase, a transaction acquires all the required locks without unlocking any data. Once all locks have been acquired, the transaction is in its locked point. u In a shrinking phase, a transaction releases all locks and cannot obtain any new locks.

Concurrency Control with Locking Methods 9 4 Rules for Two-Phase Locking Protocol u Two transactions cannot have conflicting locks. u No unlock operation can precede a lock operation in the same transaction. u No data are affected until all locks are obtained -that is, until the transaction is in its locked point.

Two-Phase Locking Protocol 9 Figure 9. 6

Concurrency Control with Locking Methods 9 4 Deadlocks (Deadly Embrace) u Deadlocks exist when two transactions T 1 and T 2 exist in the following mode: T 1 = access data items X and Y T 2 = access data items Y and X u If T 1 has not unlocked data item Y, T 2 cannot begin; and, if T 2 has not unlocked data item X, T 1 cannot continue. (See Table 9. 11)

How A Deadlock Condition Is Created 9 Table 9. 11

Concurrency Control with Locking Methods 9 4 Three Techniques to Control Deadlocks: u Deadlock Prevention A transaction requesting a new lock is aborted if there is a possibility that a deadlock can occur. u Deadlock Detection The DBMS periodically tests the database for deadlocks. If a deadlock is found, one of the transactions (“victim”) is aborted, and the other transaction continues. u Deadlock Avoidance The transaction must obtain all the locks it needs before it can be executed.

Concurrency Control with Time Stamping Methods 9 4 The time stamping approach assigns a global unique time stamp to each transaction to schedule concurrent transactions. 4 The time stamp value produces an explicit order in which transactions are submitted to the DBMS. 4 Time stamps must have two properties: Uniqueness assures that no equal time stamp values can exist. u Monotonicity assures that time stamp values always increase. u 4 The DBMS executes conflicting operations in time stamp order to ensure the serializability.

Concurrency Control with Optimistic Methods 4 Optimistic Methods 9 u It is based on the assumption that the majority of the database operations do not conflict. u A transaction is executed without restrictions until it is committed. u Each transaction moves through two or three phases: l l l Read Phase: The transaction reads the database, executes the needed computations, and makes the updates to a private copy of the database values. Validation Phase: The transaction is validated to assure that the changes made will not affect the integrity and consistency of the database. Write Phase: The changes are permanently applied to the database.

Database Recovery Management 4 Recovery restores a database from a given state, usually inconsistent, to a previously consistent state. 9 4 Recovery techniques are based on the atomic transaction property: All portions of the transaction must be applied and completed to produce a consistent database. If, for some reason, any transaction operation cannot be completed, the transaction must be aborted, and any changes to the database must be rolled back.

Database Recovery Management 4 Levels of Backup 9 u Full backup of the database It backs up or dumps the whole database. u Differential backup of the database Only the last modifications done to the database are copied. u Backup of the transaction log only It backs up all the transaction log operations that are not reflected in a previous backup copy of the database.

Database Recovery Management 4 Database Failures 9 u Software Operating system, DBMS, application programs, viruses u Hardware Memory chip errors, disk crashes, bad disk sectors, disk full errors u Programming Exemption Application programs, end users u Transaction Deadlocks u External Fire, earthquake, flood

Database Recovery Management 4 Recovery Procedures: 9 u Deferred-write and Deferred-update Transaction operations do not immediately update the database. Instead, all changes are written to the transaction log. The database is updated only after the transaction reaches its commit point. u Write-through The database is immediately updated by transaction operations during the transaction’s execution, even before the transaction reaches its commit point. The transaction log is also updated. If a transaction fails, the database uses the log information to roll back the database.