Monday, 30 April 2018

Two phase locking 2PL protocol in concurrency control mechanisms

Two phase locking 2PL protocol in concurrency control mechanisms

Two phase locking protocol

Consistency of database should be maintained during transactions. To ensure consistency, the transaction schedules must be serial schedules or serializable schedules. One way to ensure serializability is
to access the required data items in a mutually exclusive manner. That is,
while one transaction is accessing a data item, the other transactions cannot
modify that particular data item. This can be achieved by using locks.

Two phase locking protocol (2PL) ensures
serializability using locks. According to this protocol, each transaction must
request lock on data items from lock manager and it happens in two phases as
follows;

Growing phase – in this phase, a transaction can
request and acquire a lock but not release (unlock) a lock.

Shrinking phase – in this phase, a transaction can
release (unlock) a lock but not acquire a lock.

Example:

Transaction T1

1:lock-X(B);

2:read(B);

3:B := B − 50;

4:write(B);

5:lock-X(A);

6:read(A);

7:A := A + 50;

8:write(A);

9:unlock(B);

10:unlock(A);

According to 2PL, locking of all data items must happen
before releasing a lock. You can observe from transaction T1 that all lock requests
(lock-X(B) and lock-X(A)) have happened before the first unlock request (unlock(B)).
This
transaction is a two-phase transaction.

Transaction T2

1:lock-X(B);

2:read(B);

3:B := B − 50;

4:write(B);

5:unlock(B);

6:lock-X(A);

7:read(A);

8:A := A + 50;

9:write(A);

10:unlock(A);

On the other hand, transaction T2 is not a two phase
transaction. In T2, data item B is locked in exclusive mode (instruction 1),
data is consumed, and data is released (instruction 5). Then, data item A is
locked (instruction 6), consumed, and released (instruction 10). As per 2PL,
all required data items must be locked before releasing any locks. But in T2,
acquiring and releasing of locks for individual data items happened
alternatively. Hence, transaction T2 is not two phase transaction.

The following GIF image shows the working of simple 2 phase locking protocol;

Variants of 2PL:

Though the simple 2PL can handle consistency of the database, they have few problems. Hence, the following two variants of 2PL has proposed;