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

*********

Go to Strict 2PL in database transactions page

Go to Transaction Management in DBMS page

Go to important properties of 2PL page

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