Monday, 30 April 2018

Strict two phase locking protocol in database transactions concurrency control

Strict two phase locking protocol in database transactions concurrency control

Strict Two Phase Locking protocol

In addition to a serial/serializable schedule, a schedule should be a cascadeless schedule. But, in 2PL, cascading rollback may occur in few cases. To overcome this, strict two phase locking protocol was proposed.
According to strict 2PL, the protocol acquires all locks as in the case of basic 2PL but releasing all exclusive (write) locks at the end of the transaction commit. This ensures that any data written by an uncommitted transaction are locked in exclusive mode until the transaction commits, preventing any other transaction from reading the data.

Strict 2PL - In strict 2PL, all write locks are released at the end of commit of the transaction whereas all read locks are released immediately after the data are consumed.

Transaction T1
Transaction T2
Transaction T3
Lock-X(x)
Read(x)
Lock-S(y)
Read(y)
Write(x)
Unlock(x)
Unlock(y)
.
.
.
.
.
.
.
.
.
.







Lock-X(x)
Read(x)
Write(x)
Unlock(x)
.
.
.
.
.
.











Lock-S(x)
Read(x)
Unlock(x)
.
.
.

For example, let us consider the partial schedule S1 given above with three transactions T1, T2, and T3. In all the three transactions, first all the locks are acquired and then they are released one by one upon consumption. Hence, all the transactions in schedule S1 are two phase transactions.
Let us assume that, for some reasons transaction T1 of schedule S1 is failed. In that case, T1 has to be rolled back to ensure consistency of the database. Hence, T2 and T3 also have to be rolled back as they have consumed the data items that are modified by T1. So, schedule S1 is not a cascadeless schedule. The failure leads to cascading rollback.
To overcome the cascading problem, according to strict 2PL we release all the write locks at the end of the transaction and all read locks whenever they are consumed. In our example schedule S1, if we release lock on y in transaction T1 once it is done and lock on x at the end of the transaction then, transaction T2 can only consume the committed data. Hence, the cascading rollback will be avoided in case of failure.

The following GIF image shows the working of strict 2PL;


**********





strict 2pl
why strict two phase locking is used
how to eliminate cascading rollback in database transactions
difference between simple and strict two phase locking
strict 2pl example
strict 2pl animation
 







No comments:

Post a Comment

Lossless join decomposition one more example

Lossless Join Decomposition Question: Let R = {ssn, ename, pnumber, pname, plocation, hours} and R is decomposed into three re...