Exclusive and Shared Lock in DBMS

Share

In Shared/Exclusive locks, data items can be locked in two modes:

  1. Exclusive(X) mode: Data items can be both read as well as written.
  2. Shared(S) mode: Data items can only be read.

A lock associated with an item A, Lock(A) now has three possible states: read-locked, write-locked or unlocked. A read-locked item is also called share-locked because other transactions are allowed to read the item. whereas a write-locked item is called exclusive-locked because a single transaction exclusively holds the lock on the item.

One method for implementing the preceding operations on a read/write lock is to keep track of the number of transactions that holds a shared(read) lock on an item in the lock table as well as a list of transactions IDs that hold a shared lock. each record in the lock table will have four fields: <Data_item_name, LOCK, No_of_reads, Locking_transaction(s)>. The system needs to maintain lock records only for locked items in the lock table. The value of LOCK is either read-locked or write-locked.

when we use the shared/exclusive locking scheme, the system must enforce the following rules:

  1. The transaction must issue the operation read_lock(X) or write_lock(X) before any read_item(X) operation performed.
  2. The transaction must issue the operation write_lock(X) before any write_item(X) operation is performed.
  3. The transaction must issue the operation unlock(X) after all read_item(X) and write_item(X) operations are performed.
  4. The transaction will not issue a read_lock(X) operation if it already holds a read(shared) lock or write lock on item X.
  5. The transaction will not issue a write_lock(X) operation if it already holds a read(shared) lock or write lock on item X.
  6. The transaction will not issue unlock(X) operation unless it already holds a read(shared) lock or write lock on item X.

A transaction may be granted a lock on an item if the requested lock is compatible with locks already held on the item by other transactions. Any number of transactions can hold shared locks on an item. But, if any transaction holds an exclusive on the item no other transaction may hold any lock on the item. if a lock cannot be granted, the requesting transaction is made to wait till all incompatible locks held by other transactions have been released. Then the lock is granted. The use of these lock modes allows multiple transactions to read a data item but limits write access to just one transaction at a time.

Binary Locks: https://codewithsudeep.com/sudeep/csit/binary-locks-for-concurrency-control-in-dbms/

Share
Sudeep Mishra

Sudeep Mishra

Healing

%d bloggers like this: