Open In App
Related Articles

Difference between Shared Lock and Exclusive Lock

Improve Article
Save Article
Like Article

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):

  • Another transaction that tries to read the same data is permitted to read, but a transaction that tries to update the data will be prevented from doing so until the shared lock is released.
  • Shared lock is also called read lock, used for reading data items only.
  • Shared locks support read integrity. They ensure that a record is not in process of being updated during a read-only request.
  • Shared locks can also be used to prevent any kind of updates of record.
  • It is denoted by Lock-S.
  • S-lock is requested using Lock-S instruction.

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) :

  • When a statement modifies data, its transaction holds an exclusive lock on data that prevents other transactions from accessing the data.
  • This lock remains in place until the transaction holding the lock issues a commit or rollback.
  • They can be owned by only one transaction at a time.
  • With the Exclusive Lock, a data item can be read as well as written. Also called write lock.
  •  Any transaction that requires an exclusive lock must wait if another transaction currently owns an exclusive lock or a shared lock against the requested resource.
  • They can be owned by only one transaction at a time.
  • It is denoted as Lock-X.
  • X-lock is requested using Lock-X instruction.

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

  • If the transaction T1 is holding a shared lock in data item A, then the control manager can grant the shared lock to transaction T2 as compatibility is TRUE, but it cannot grant the exclusive lock as compatibility is FALSE.
  • In simple words if transaction T1 is reading a data item A, then same data item A can be read by another transaction T2 but cannot be written by another transaction.
  • Similarly if an exclusive lock (i.e. lock for read and write operations) is hold on the data item in some transaction then no other transaction can acquire Shared or Exclusive lock as the compatibility function denoted FALSE.

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
Level Up Your GATE Prep!
Embark on a transformative journey towards GATE success by choosing Data Science & AI as your second paper choice with our specialized course. If you find yourself lost in the vast landscape of the GATE syllabus, our program is the compass you need.

Last Updated : 17 Mar, 2023
Like Article
Save Article
Similar Reads