Unit 6: Transaction Management

A transaction is a single logical unit of work which accesses and possibly modifies the contents of a database. Transactions access data using read and write operations.
In order to maintain consistency in a database, before and after transaction, certain properties are followed. These are called ACID properties.
Atomicity
By this, we mean that either the entire transaction takes place at once or doesn’t happen at all. There is no midway i.e. transactions do not occur partially. Each transaction is considered as one unit and either runs to completion or is not executed at all. It involves following two operations.
—Abort: If a transaction aborts, changes made to database are not visible.
—Commit: If a transaction commits, changes made are visible.
Atomicity is also known as the ‘All or nothing rule’.
Consider the following transaction T consisting of T1 and T2: Transfer of 100 from account X to account Y.

If the transaction fails after completion of T1 but before completion of T2.( say, after write(X) but before write(Y)), then amount has been deducted from X but not added to Y. This results in an inconsistent database state. Therefore, the transaction must be executed in entirety in order to ensure correctness of database state.
Consistency
This means that integrity constraints must be maintained so that the database is consistent before and after the transaction. It refers to correctness of a database. Referring to the example above,
The total amount before and after the transaction must be maintained.
Total before T occurs = 500 + 200 = 700.
Total after T occurs = 400 + 300 = 700.
Therefore, database is consistent. Inconsistency occurs in case T1 completes but T2 fails. As a result T is incomplete.
Isolation
This property ensures that multiple transactions can occur concurrently without leading to inconsistency of database state. Transactions occur independently without interference. Changes occurring in a particular transaction will not be visible to any other transaction until that particular change in that transaction is written to memory or has been committed. This property ensures that the execution of transactions concurrently will result in a state that is equivalent to a state achieved these were executed serially in some order.
Let X= 500, Y = 500.
Consider two transactions T and T”.

Suppose T has been executed till Read (Y) and then T’’ starts. As a result , interleaving of operations takes place due to which T’’ reads correct value of X but incorrect value of Y and sum computed by
T’’: (X+Y = 50, 000+500=50, 500)
is thus not consistent with the sum at end of transaction:
T: (X+Y = 50, 000 + 450 = 50, 450).
This results in database inconsistency, due to a loss of 50 units. Hence, transactions must take place in isolation and changes should be visible only after a they have been made to the main memory.
Durability:
This property ensures that once the transaction has completed execution, the updates and modifications to the database are stored in and written to disk and they persist even is system failure occurs. These updates now become permanent and are stored in a non-volatile memory. The effects of the transaction, thus, are never lost.
The ACID properties, in totality, provide a mechanism to ensure correctness and consistency of a database in a way such that each transaction is a group of operations that acts a single unit, produces consistent results, acts in isolation from other operations and updates that it makes are durably stored.

Concurrency Control

concurrency control techniques which are used to ensure the isolation property of concurrently executing transactions. All the schemes presented here assume that the schedules are serializable.

Lock based protocol:-

One way through which we can assume serializability is to access the variables in mutually exclusive manner. i.e. if one transaction is accessing a data item, no other transaction can modify that data item. To implement the notion mentioned above, we implement a technique of using lock on data item .i.e. transaction can access a data item only if it holds lock on that data item.
Types of lock:-
1. Binary lock:- A binary lock can have two states i.e. locked and unlocked (1 for locked state and 0 for unlocked state). A transaction requests access to an item Q by first issuing LOCK (Q) operations. If LOCK (Q) =1, the transaction is forced to wait and if LOCK (Q) = 0, it is set to 1 and the transaction is allowed to access item Q. When transaction finishes with the operation, it finally issues unlock operation by setting LOCK (Q) to 0. The rule followed by binary locking scheme is as follows:

• • A transaction T must issue the operation lock (Q) before any read (Q) or write (Q) operations.
• • A transaction T must issue unlock (Q) after all read (Q) and write (Q) operations are completed.
• A transaction T will not issue any lock (Q) if it already holds lock on item Q.
• • A transaction T will issue unlock (Q) iff it already holds a lock on data item Q.

2. Shared/Exclusive or Read/Write lock:-
Binary lock as discussed earlier is too restrictive for database items. But we should allow multiple transactions to access the same item if they all access Q for reading purpose only. Hence shared/exclusive lock is desired.
Shared: – if Ti holds shared mode lock on Q, it can only read and cannot write. Multiple shared mode lock can exist for the same data item Q.
Exclusive: – if Ti holds exclusive mode lock on Q, then it can both read as well as write. There can exist only one exclusive mode lock on a data item Q.
Let A and B are the two arbitrary lock modes. If transaction Ti can be granted a lock on Q immediately inspite of the presence of the mode B lock on Q, then we say mode A is compatible with mode B. Hence lock compatibility matrix can be summarized as shown below:

locking protocol:

Two phase locking protocol:- This is a protocol which ensures conflict-serializable schedules. This protocol requires that each transaction issue lock and unlock requests in different phases. There are two phases as discussed below:
Phase 1: Growing Phase
• transaction may obtain locks
• transaction may not release locks
Phase 2: Shrinking Phase
• transaction may release locks
• transaction may not obtain locks
For example: Transaction T20 and T30 are the example of two phase locking protocol. Initially a transaction is in growing phase and can acquire locks as per its need. Then the transaction reaches its lock point i.e. it is the point where a transaction acquires its final lock. Finally when transaction unlocks any data item, it enters into shrinking phase and no more locks can be acquired again.
The protocol assures serializability. It can be proved that the transactions can be serialized in the order of their lock points (i.e. the point where a transaction acquired its final lock). Two-phase locking does not ensure freedom from deadlocks.
Cascading roll-back is possible under two-phase locking. To avoid this, follow a modified protocol called strict two-phase locking. Here a transaction must hold all its exclusive locks till it commits/aborts. Rigorous two-phase locking is even stricter: here all locks are held till commit/abort. In this protocol transactions can be serialized in the order in which they commit.
Lock conversion: Two-phase locking with lock conversions:
– Growing Phase:
• can acquire a lock-S on item
• can acquire a lock-X on item
• can convert a lock-S to a lock-X (upgrade)