Concurrency Control Using Locks in DBMS

Locks are an integral part to maintain concurrency control in DBMS. A transaction in any system implementing lock based concurrency control cannot read or write a statement until it has obtained the required locks.

There are two types of locks in Lock based protocols. These are:

Binary Locks - These can only be in one of two states, locked or unlocked.
Shared/Exclusive Locks - Shared locks are acquired when only read operation is to be performed. Shared locks can be shared between multiple transactions as there is no data being altered. Exclusive locks are used when write operation is performed. Only the transaction holding the exclusive lock is allowed to make changes to the data value.

The different locking protocols are −

Simplistic Lock Protocol

A lock is obtained by the transaction on the data value before the write operation is performed. After the write operation, the lock can be released. An example of Simplistic Lock Protocol is:

T1	T2
R(A)
	R(A)
Lock(B)
R(B)
W(B)
Unlock(B)
	Lock(C)
	R(C)
	W(C)
	Unlock(C)
Commit
	Commit

There are two transactions T1 and T2 shown above. There are no locks required for the read operation but before the write operation, each of these transactions acquires a lock and releases it after.

Two-Phase Locking Protocol

The two-phase locking protocol has two phases, namely the growing and shrinking phase. The transaction can only acquire locks when it is in the growing phase. When it enters the shrinking phase, it can release the previously acquired locks but cannot acquire new locks. The exclusive locks are represented by X and the shared locks are represented by S. An example of Two phase locking protocol is −

T1	T2
S(A)
R(A)
	S(A)
	R(A)
X(B)
R(B)
W(B)
	X(C)
	R(C)
	W(C)
	Unlock(C)
	Unlock(A)
Unlock(B)
Unlock(A)
Commit
	Commit

In the above example, T1 and T2 share the variable A using a shared lock as only read operation is performed on A. T1 acquires an exclusive lock on B for the write operation and releases it soon after. T2 does the same with C.

Strict Two-Phase Locking Protocol

Strict two phase locking protocol is similar to two phase locking protocol. The only difference is that in strict 2PL protocol all the exclusive locks acquired by the protocol need to be held until the protocol either commits or aborts. An example of Strict two - phase locking protocol is:

T1	T2
S(A)
R(A)
	S(A)
	R(A)
X(B)
R(B)
W(B)
	X(C)
	R(C)
	W(C)
	Unlock(A)
Unlock(A)
Commit
Unlock(B)
	Commit
	Unlock(C)

In the above example, T1 and T2 share the variable A using a shared lock as only read operation is performed on A. T1 acquires an exclusive lock on B for the write operation and T2 does the same with C. The exclusive locks are released only after the transactions have committed. However, there is no such bound for the shared locks.

Rigorous two-phase locking protocol

Rigorous two phase locking protocol is merely an extension of two phase locking protocol and strict two phase locking protocol. Here, all the locks held by a transaction, whether shared or exclusive, are only released once the transaction commits or aborts. An example of Rigorous two - phase locking protocol is:

T1	T2
S(A)
R(A)
	S(A)
	R(A)
X(B)
R(B)
W(B)
	X(C)
	R(C)
	W(C)
Commit
Unlock(A)
Unlock(B)
	Commit
	Unlock(A)
	Unlock(C)

In the above example, T1 and T2 share the variable A using a shared lock as only read operation is performed on A. T1 acquires an exclusive lock on B for the write operation and T2 does the same with C. Both the shared locks and the exclusive locks are only released after the transactions have committed.