Using locks for concurrency control in DBMS

Locks are an integral part of maintaining concurrency control in a DBMS. Transactions in any system that implements lock-based concurrency control cannot read or write statements until they acquire the required locks.

There are two types of locks in lock-based protocols. They are:

• Binary locks - They can only be in one of two states, locked or unlocked.
• Shared/exclusive locks - Acquire shared locks only when performing read operations. Shared locks can be shared between multiple transactions because no data is changed. Use an exclusive lock when performing write operations. Only transactions holding an exclusive lock are allowed to change data values.

The different locking protocols are -

Simple locking protocol

A transaction acquires a lock on a data value before performing a write operation. After the write operation is completed, the lock can be released. An example of a simple locking protocol is:

# R(A)Lock(B) R(B)W(B)##Unlock (B)SubmitTwo transactions T1 and T2 are shown above. Read operations do not require locks, but each transaction acquires a lock before a write operation and releases the lock afterwards.

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

Two-phase locking protocol

The two-phase locking protocol has two phases, namely the growth phase and the shrinkage phase. A transaction can acquire locks only during the growth phase. When entering the shrink phase, it can release previously acquired locks, but cannot acquire new locks. Exclusive locks are represented by X and shared locks are represented by S. An example of a two-phase locking protocol is-

T1T2S (A)R(A) tr>##R(A)X(B)##X(C)##Submit

##S(A)
	##R(B)
	W(B)
	##R(C)
	W(C)
	Unlock (C)
	Unlock (A)
Unlock (B)
Unlock (A)
	Submit
In the above example, T1 and T2 share the lock using the shared variable A because only read operations are performed on A. T1 acquires the exclusive lock on B for the write operation and releases it soon after. T2 does the same thing as C. Strict two-phase locking protocol	The strict two-phase locking protocol is similar to the two-phase locking protocol. The only difference is that in a strict 2PL protocol, all exclusive locks acquired by the protocol need to be retained until the protocol commits or aborts. An example of a strict two-phase locking protocol is:

T1

T2

S(A) tr>R(A )X(B)##X(C)##R(C)W(C)##Unlock (A)

##S(A)

##R(A)

##R(B)

W(B)

Unlock(A)

##Submit

Unlock (B)

Submit

Unlock(C)

In the above example, T1 and T2 use a shared lock to share variable A, because only read operations are performed on A. T1 acquires an exclusive lock on B for write operations, and T2 acquires an exclusive lock on C. The exclusive lock is released only after the transaction commits. However, shared locks have no such restrictions.

Strict two-phase locking protocol

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

tr>##R(A )X(B)##R(B)W(B)##Unlock(C)

T1	T2
S(A)
R(A )
	S(A)
##X(C)
##R(C)
W(C)	Submit
	Unlock (A)
	Unlock (B)
Submit
Unlock (A)
	In the above example , T1 and T2 use a shared lock to share variable A because only read operations are performed on A. T1 acquires an exclusive lock on B for write operations, and T2 performs the same operation on C. Both shared locks and exclusive locks will only be released after the transaction is committed.

The above is the detailed content of Using locks for concurrency control in DBMS. For more information, please follow other related articles on the PHP Chinese website!