Open In App

Difference between Shared Lock and Exclusive Lock

One of the method to ensure isolation property in transaction is to require data items be accessed in a mutually exclusive manner. That means, while one transaction is accessing a data item, no other transaction can modify that data item. So, the most common method used to implement requirement is to allow a transaction to access a data item only if it is currently holding a lock on that item. Thus, the lock on operation is required to ensure isolation of transaction. 

1. Shared Lock (S):

For example, consider a case where initially A=100 and there are two transactions which are reading A. If one of transaction wants to update A, in that case other transaction would be reading wrong value. However, Shared lock prevents it from updating until it has finished reading. 

2. Exclusive Lock (X) :

For example, consider a case where initially A=100 when a transaction needs to deduct 50 from A. We can allow this transaction by placing X lock on it. Therefore, when the any other transaction wants to read or write, exclusive lock prevent it. Lock Compatibility Matrix :

Compatibility matrix for locks

Difference between Shared Lock and Exclusive Lock :

S.No. Shared Lock Exclusive Lock
1. Lock mode is read only operation. Lock mode is read as well as write operation.
2. Shared lock can be placed on objects that do not have an exclusive lock already placed on them. Exclusive lock can only be placed on objects that do not have any other kind of lock.
3. Prevents others from updating the data. Prevents others from reading or updating the data.
4. Issued when transaction wants to read item that do not have an exclusive lock. Issued when transaction wants to update unlocked item.
5. Any number of transaction can hold shared lock on an item. Exclusive lock can be hold by only one transaction.
6. S-lock is requested using lock-S instruction. X-lock is requested using lock-X instruction.
7. Example: Multiple transactions reading the same data Example: Transaction updating a table row
Article Tags :