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;

  • Strict two phase locking protocol
  • Rigorous two phase locking protocol

*********





two phase locking protocol
simple 2PL
strict 2PL
rigorous 2PL
variants of two phase locking protocol
how to handle consistency of a database during transactions
how to ensure conflict serializablity using locking protocol
2PL guarantees conflict serializability
2PL example in database transactions

No comments:

Post a Comment

Find candidate key and normalize the relation into 2nf and 3nf

Find candidate key and normalize the relation into 2nf and 3nf Question: A relation R is defined as follows. R = (name, stre...