Showing posts with label Transaction Management. Show all posts
Showing posts with label Transaction Management. Show all posts

Tuesday, 1 May 2018

Important properties of two phase locking protocol and its variants

Important properties of two phase locking protocol and its variants


Properties of   2PL


  • Serializability 
    • 2PL ensures conflict serializability.
  • Growing phase
    • All the data items must be locked with the required locks (either read lock or write lock) well before accessing those data items. [called Growing phase]
    • Upgrading of locks can only be done during Growing phase.
  • Shrinking phase
    • Once the lock is released, the transaction cannot get any new locks during the execution of that particular transaction. [called Shrinking phase].
    • Downgrading of locks can only be done during Shrinking phase.
  • Lock point
    • The end of growing phase (acquiring the final lock of a transaction) is called lock point.
  • Deadlock
    • 2PL does not guarantee freedom from deadlock. Deadlock may happen under two phase locking.
  • Cascading rollback
    • Cascading rollback may occur under 2PL.
  • Variants of 2PL
    • strict 2PL and rigorous 2PL.

***********
 


Go to rigorous two phase locking protocol page



properties of two phase locking protocol page
important 2PL properties
facts about two phase locking protocol
simple, strict, and rigorous two phase locking protocol
upgrading and downgrading properties in 2pl
growing and shrinking phases in 2pl







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
 







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

SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...