Lock based Protocol in DBMS

A lock is kind of a mechanism that ensures that the integrity of data is maintained. It does that, by locking the data while a transaction is running, any transaction cannot read or write the data until it acquires the appropriate lock. There are two types of a lock that can be placed while accessing the data so that the concurrent transaction can not alter the data while we are processing it.

1. Shared Lock(S)
2. Exclusive Lock(X)

1. Shared Lock(S): Shared lock is placed when we are reading the data, multiple shared locks can be placed on the data but when a shared lock is placed no exclusive lock can be placed.

To understand the lock mechanism let’s take an example of conflict:

You and your brother have a joint bank account, from which you both can withdraw money. Now let’s say you both go to different branches of the same bank at the same time and try to withdraw 5000 INR, your joint account has only 6000 balance.

Now if we don’t have concurrency control in place you both can get 5000 INR at the same time but once both the transactions finish the account balance would be -4000 which is not possible and leaves the database in inconsistent state.

We need something that controls the transactions in such a way that allows the transaction to run concurrently but maintaining the consistency of data to avoid such issues.

Solution of the above problem using Shared lock:

For example, when two transactions are reading Steve’s account balance, let them read by placing shared lock but at the same time if another transaction wants to update the Steve’s account balance by placing Exclusive lock, do not allow it until reading is finished.

2. Exclusive Lock(X): Exclusive lock is placed when we want to read and write the data. This lock allows both the read and write operation, Once this lock is placed on the data no other lock (shared or Exclusive) can be placed on the data until Exclusive lock is released.

For example, when a transaction wants to update the Steve’s account balance, let it do by placing X lock on it but if a second transaction wants to read the data(S lock) don’t allow it, if another transaction wants to write the data(X lock) don’t allow that either.

So based on this we can create a table like this:

Lock Compatibility Matrix

__________________________
|     |   S    |    X    |
|-------------------------
|  S  | True   |   False |
|-------------------------
|  X  | False  |   False |
--------------------------

How to read this matrix?:
There are two rows, first row says that when S lock is placed, another S lock can be acquired so it is marked true but no Exclusive locks can be acquired so marked False.
In second row, When X lock is acquired neither S nor X lock can be acquired so both marked false.

Types of Lock Protocols

1. Simplistic lock protocol

This protocol is simplest form of locking the data while a transaction is running. As per simplistic lock protocol any transaction needs to acquire the lock on the data before performing any insert, update or delete operation. The transaction releases the lock as soon as it is done performing the operation. This prevents other transactions to read the data while its being updated.

2. Pre-claiming lock protocol

• As the name suggests, this protocol checks the the transaction to see what all locks it requires before it begins.
• Before the transaction begins, it places the request to acquire all the locks on data items.
• If all the locks are granted, the transaction begins execution and releases all the locks once it’s done execution.
• If all the locks are not granted this transaction waits until the required locks are granted.

3. Two Phase locking protocol (2PL)

In two phase locking protocol the locking and unlocking of data items is done in two phases.

Growing Phase: In this phase, the locks are acquired on the data items but none of the acquired locks can be released in this phase.

Shrinking Phase: The existing locks can be released in this phase but no new locks can be acquired in this phase.

Note: The point at which the transaction acquires final lock and the growing phase ends is called lock point.

2 PL Example: Let’s take an example to understand how two phase locking protocol works: In the following example there are two transaction T1 and T2 running concurrently.

Transaction T1: In this example, growing phase of T1 is from Step 1 to Step 5. Shrinking phase is from Step 7 to Step 9. Lock point is at step 5.

Transaction T2: Growing phase of T2 is from Step 2 to Step 10. Shrinking phase is from Step 11 to Step 13. Lock point is at step 10.

          T1            T2 
         ----          ----  
Step 1 lock-S(A)   
Step 2 ..             lock-S(A)
Step 3 lock-S(B)
Step 4 ...            lock-S(B)
Step 5 lock-X(C)
Step 6 ..
Step 7 Unlock(A)
Step 8 Unlock(B)
Step 9 Unlock(C)
Step 10               lock-S(C)
Step 11               Unblock(A)
Step 12               Unblock(B)
Step 13               Unblock(C)

4. Strict Two Phase Locking Protocol (Strict – 2PL)

It is somewhat similar to 2PL except that it doesn’t have a shrinking phase. This protocol releases all the locks only after the transaction is completed successfully and used the commit statement to make the changes permanent in the database.

It doesn’t release locks after performing an operation on data items. It releases all the locks at the same time once the transaction commit successfully.