Page 161 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes A Serializable Schedule

Even though the actions of equivalent to first running T and T are interleaved, the result of this
1 2
schedule is entirely and then running T, entirely. Actually, Ts read and write of B is not influenced
by T ’s actions on B, and the net effect is the same if these actions are the serial schedule First T ,
2 1
then T . This schedule is also serializable schedule if first T , then T . Therefore, if T, and T are
2 2 1 2
submitted concurrently to a DBMS, either of these two schedules could be chosen as first.
A DBMS might sometimes execute transactions which is not a serial execution i.e., not serializable.
This can happen for two reasons as follows:
1. The DBMS might use a concurrency control method that ensures the executed schedule
itself.
2. SQL gives programmers the authority to instruct the DBMS to choose non-serializable
schedule.

9.9 Lock based Protocols

A DBMS must be able to ensure that only serializable, recoverable, schedules are allowed, and
that no actions of committed transactions are lost, while undoing aborted transactions. So, a
DBMS uses a locking protocol to achieve this.
Locking Protocol: A locking protocol is a set of rules to be followed by each transaction to
ensure that, even-though actions of several transactions might be interleaved, the net effect is
same as executing all transactions in some serial order.

9.9.1 Two-phase Locking (2 PL) Protocol

The two-phase locking protocol ensures serializability. This protocol requires that each
transaction issues lock and unlock requests in two phases as follows:
1. Growing Phase: A transaction may obtain locks but may not release any lock.
2. Shrinking Phase: A transaction may release locks, but may not obtain any new locks.

Initially, a transaction is in the growing phase. Once the transaction releases a lock, it enters the
shrinking phase, and it can issue no more lock requests. Thus, the transaction acquires lock and
releases lock as needed. A modification of two-phase locking is called the Strict Two-Phase
Locking Protocol.

9.9.2 Strict Two-phase Locking (Strict 2PL) Protocol

The strict two-phase locking protocol, requires, that, in addition to locking being two-phase, all
exclusive-mode lock. (Exclusive lock is the mode of locking to provide exclusive use of the
data - item to one and only one transaction) taken by a transaction, must be held until that
transaction commits successfully, preventing any other transaction from reading the data.
Thus, the most widely used locking protocol is the Strict Two-Phase Locking Protocol, also
called as Strict 2PL, has following two rules:

RULE 1: If a transaction T wants to read an object, it first requests a Shared lock [which is the
mode of locking to ensure that the data-item does not undergoes incorrect any modifications
while it is locked in this mode on the object]. But, if a transaction T wants to modify an object, it
first requests an exclusive lock on the object.

154 LOVELY PROFESSIONAL UNIVERSITY