Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
10K+ Views
