Transactions and Concurrency Control

Transaction Management: A sequence of many actions which are considered to be one atomic unit of work. A transaction is a collection of operations involving data items in a database. There are four important properties of transactions that a DBMS must ensure to maintain data in the face concurrent access and system failures.

Atomicity: Atomicity requires that each transaction is all or nothing. If one part of .the transaction fails, the entire transaction fails and the database state is left unchanged.

Consistency: If each transaction is consistent and the data base starts on as consistent, it ends up as consistent

Isolation: Execution of one transaction is isolated from that of another transactions. It ensures that concurrent execution of transaction results in a system state that would be obtained if transaction were executed serially, i.e., one after the other.

Durability: Durability means that once a transaction has been committed, it will remain even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently.

If a transaction commits, its effects persist.

• A transaction starts with any SQL statement and ends with a COMMIT or ROLLBACK.
• COMMIT statement makes changes permanent to the database.
• ROLLBACK statement reverses changes.
• A transaction includes one or more database access operations. These can include insertion, deletion, modification or retrieval operations.

Concurrency Control: Process of managing simultaneous execution of transactions in a shared database, is known as concurrency control. Basically, concurrency control ensures that correct results for concurrent operations are generated, while getting those results as quickly as possible.

Need of Concurrency Control: Simultaneous execution of transactions over a shared database can create several data integrity and consistency problems.

Lost Update: This problem occurs when two transactions that access the same database items, have their operations interleaved in a way that makes the value of some database items incorrect.

Dirty Read Problems: This problem occurs when one transaction reads changes the value while the other reads the value before committing or rolling back by the first transaction.

Inconsistent Retrievals: This problem occurs when a transaction accesses data before and after another transaction(s) finish working with such data.

We need concurrence control, when

• The amount of data is sufficiently great that at any time only fraction of the data can be in primary memory and rest should be swapped from secondary memory as needed.
• Even if the entire database can be present in primary memory, there may be multiple processes.

Schedule: A schedule (or history) is a model to describe execution of transactions running in the system. When multiple transactions are executing concurrently in an interleaved fashion, then the order of execution of operations from the various transactions is known as a schedule or we can say that a schedule is a sequence of read, write, abort and commit operations from a set of transactions.

Classification of Schedules Based on Serializability: The schedules can be classified as Serial Schedule. A schedule in which the different transactions are not interleaved (i.e., transactions are executed from start to finish one-by-one).

Complete Schedule: A schedule that contains either a commit or an abort action for each transaction. Consequently, a complete schedule will not contain any active transaction at the end of the schedule.

Non-serial Schedules: Non-serial schedules are interleaved schedules and these schedules improve performance of system (i.e., throughput and response time). But concurrency or interleaving operations in schedules, might lead the database to an inconsistent state. We need to seek to identify schedules that are:

1. As fast as interleaved schedules.
2. As consistent as serial schedules.

Conflicting Operations: When two or more transactions in a non-serial schedule execute concurrently, then there may be some conflicting operations. Two operations are said to be conflicting, if they satisfy all of the following conditions.

• The operations belong to different transactions.
• Atleast one of the operations is a write operation.
• The operations access the same object or item.

The following set of operations is conflicting

While the following sets of operations are not conflicting

Serializable Schedule: A schedule S of n transactions is serializable, if it is equivalent to some serial schedule of the same n transactions.

A non-serial schedule S is serializable is equivalent to saying that it is correct, because it is equivalent to a serial schedule. There are two types of serializable schedule

1. Conflict serializable schedule
2. View serializable schedule

Conflict Serializable Schedule: When the schedule (S) is conflict equivalent to some serial schedule (Sï), then that schedule is called as conflict serializable schedule. In such a case, we can reorder the non-conflicting operations in S until we form the equivalent serial schedule S’.

Conflict Equivalence: The schedules S₁ and S₂ are said to be conflict equivalent, if the following conditions are satisfied:

• Both schedules S₁ and S₂ involve the same set of transactions (including ordering of operations within each transaction).
• The order of each pair of conflicting actions in S₁ and S₂ are the same.

Testing for Conflict Serializability of a Schedule: There is a simple algorithm that can be used to test a schedule for conflict serializability. This algorithm constructs a precedence graph (or serialization graph), which is a directed graph. A precedence graph for a schedule S contains

1. A node for each committed transaction in S.
2. An edge from T_i to T_j, if an action of T_i precedes and conflicts with one of T_j’s operations.

A schedule S is conflict serializable if and only if its precedence graphs is acyclic.

Note: The above example of schedule is not conflict serializable schedule.

View Serializable Schedule: A schedule is view serializable, if it is view equivalent to some serial schedule. Conflict serializable ⇒ View serializable, but not vice-versa.

View Equivalence: Two schedules S₁ and S₂ are view equivalent,

1. T_i reads initial value of database object A in schedule S₁ then T_i also reads initial value of database object A in schedule S₂.
2. T_i reads value of A written by T_j in schedule S₁ then T_i also reads value of A written by T_j in schedule S₂.
3. T_i writes final value of A in schedule S_l then T_i also writes final value of A in S₂.

In the above example, both schedules S₁ and S₂ are view equivalent. So, they are view serializable schedules. But S₁ schedule is not conflict serializable schedule and S₂ is not conflict serializable schedule because cycle is not formed.

Recoverable Schedule: Once a transaction T is committed, it should never be necessary to rollback T. The schedules under this criteria are called recoverable schedules and those do not, are called non-recoverable. A schedule S is recoverable, if no transaction T in S commits until all transactions T’, that have written an item that T reads, have committed.

Cascadeless Schedule: These schedules avoid cascading rollbacks. Even, if a schedule is recoverable to recover correctly from failure of transaction T_i, we may have to rollback several transactions. This phenomenon in which a single transaction failure which leads to a series of transaction rollbacks, is called cascading rollbacks.

Cascading rollback is undesirable, since it leads to the undoing of a significant amount of work. It is desirable to restrict the schedules to those, where cascading rollback cannot occur. Such schedules are called cascadeless schedules.

Cascadeless Schedule

Strict Schedule: A schedule is strict,

• If overriding of uncommitted data is not allowed.
• Formally, if it satisfies the following conditions
  1. T_j reads a data item X after T_i has terminated (aborted or committed).
  2. T_j writes a data item X after T_i has terminated (aborted or committed).

Concurrency Control with Locking : If concurrency control with locking technique is used, then locks prevent multiple transactions from accessing the items concurrently.

• Access on data only, if TA ‘has lock’ on data.
• Transactions request and release locks.
• Schedule allows or differs operations based on lock table.

Lock: A lock is a variable associated with a data item that describes the status of the item with respect to possible operations that can be applied to it. (e.g., read lock, write lock). Locks are used as means of synchronizing the access by concurrent transactions to the database items. There is one lock per database item.

Problems Arising with Locks: There are two problems which are possible when using locks to control the concurrency among transactions

• Deadlock: Two or more competing transactions are waiting for each other to complete to obtain a missing lock.
• Starvation: A transaction is continually denying the access to a given data item.

Purpose of Concurrency Control with Locking

• To enforce isolation among conflicting transactions.
• To preserve database consistency.
• To resolve read-write, write-read and write-write conflicts.

Locking is an operation that secures the permission to read, and permission to write a data item.

Lock (X): Data item X is locked on behalf of the requesting transaction.

Unlocking is an operation which removes these permissions from the data item.

Unlock (X): Data item X is made available to all other transactions.

Types of Locks: The two types of Locks are given below

Binary Locks: A binary lock has two states: Locked/Unlocked. If a database item X is locked, then X cannot be accessed by any other database operation.

Shared/Exclusive Locks (Read/Write Locks): There are three states:- Read locked / Writelocked / Unlocked. Several transactions can access the same item X for reading (shared lock), however if any of the transactions want to write the item X, the transaction must acquire an exclusive lock on the item.

Guaranteeing Serializability by Two-phase Locking: A transaction is said to follow the two-phase locking protocol, if all locking operations precede the first unlock operation in the transaction.

Such a transaction can be divided into two phases:

• Expanding or Growing Phase During which new locks on items can be acquired but none can be released.
• Shrinking Phase During which existing locks can be released but no new locks can be acquired.

Variations of Two-phase Locking (2PL) Technique:

• Basic 2PL (2 Phase Locking) Transaction locks data items incrementally. This may cause the problem of deadlock.
• Conservative 2PL (Static 2PL) Prevents deadlock by locking all desired data items before transaction begins execution. However, it is difficult to use in practice because of the need to predeclare the read-set and write-set, which is not possible in most situations.
• Strict 2PL A more stricter version of basic algorithm, where unlocking of write lock is performed after a transaction terminates (commits or aborts and rolled back). Hence, no other transaction can read or write an item that is written by transaction.
• Rigorous 2PL Strict 2PL is not deadlock free. A more restrictive variation of strict 2PL, is rigorous 2PL, which also guarantees strict schedule. In this variation, a transaction does not release any of its locks (exclusive or shared) until after it commits or aborts.

Deadlock: A deadlock is a condition in which two or more transaction are waiting for each other deadlock (T₁ and T₂). An example is given below

Deadlock Prevention: A transaction locks all data items; it refers to before it begins execution. This way of locking prevents deadlock, since a transaction never waits for a data item. The conservative two-phase locking uses this approach.

Deadlock Detection and Resolution: In this approach, deadlocks are allowed to happen. The scheduler maintains a wait-for-graph for detecting cycle. If a cycle exists, then one transaction involved in the cycle is selected (victim) and rolled back.

• A wait-for-graph is created using the lock table. As soon as a transaction is blocked, it is added to the graph.
• When a chain like T_i waits for T_j, T_j waits for T_K and T_k waits for T_i or T_j occurs, then this creates a cycle. One of the transactions of the cycle is selected and rolled back.

Deadlock Avoidance: There are many variations of two-phase locking algorithm. Some avoid deadlock by not lefting the cycle to complete. This is as soon as the algorithm discovers that blocking a translation is likely to create a cycle, it rolls back the transaction.

Following schemes use transaction times-tamps for the sake of deadlock avoidance

Wait-die scheme (Non-preemptive): Older transaction may wait for younger one to release data item. Younger transactions never wait for older ones; they are rolled back instead. A transaction may die several times before acquiring the needed data item.

Wound-wait scheme (Preemptive): Older transaction bounds (forces rollback) of younger transaction instead of waiting for it. Younger transactions may wait for older ones. May be fewer rollbacks than wait-die scheme.

Starvation: Starvation occurs when a particular transaction consistently waits or restarted and never gets a chance to proceed further. In a deadlock resolution scheme, it is possible that the same transaction may consistently be selected as victim and rolled back.

Time-stamp Based Concurrency Control Algorithm: This is a different approach that guarantees serializability involves using transaction time-stamps to order transaction execution for an equivalent serial schedule.

Time-stamp: A time-stamp is a unique identifier created by the DBMS to identify a transaction.

• Time-stamp is a monotonically increasing variable (integer) indicating the age of an operation or a transaction.
• A larger time-stamp value indicates a more recent event or operation.

Starvation versus Deadlock