Record Locking In Database

Download PDF
Advertisement

Record Locking In Database

Record Locking In Database: Record Locking mechanism is the most common method to implement the concurrency control in DBMS. Locking means record or set of records must be locked for other users, when it is opened for update possibly by any user.

Suppose the example, as soon as A opens his account through his ATM card. The record is locked for person B. When A completes his transaction, the balance is updated to Rs 2000 and record is unlocked. Now when B opens his account, the system will show the balance of Rs 2000 and thus it cannot be overdrawn by B.

Record locking mechanism is shown as

 

Types of Record Locking

Record locking can be defined as two types

  1. Shared Locks
  2. Exclusive Locks

Shared Locks

Shared Locks are also called Read Locks. When shared lock is applied by a transaction, other user of transaction can read data but not update the locked records. Shared locks are applied in case when you want to open same records for reading such that no other user changes these records during this time. Other transaction can also apply shared locks but no exclusive locks on these records.

Advertisement

Exclusive Locks

Exclusive locks are also called Write Locks. In case of exclusive locking, other transactions cannot be read or update the locked records. Exclusive locks is applied in situations when you want to change or update records during this time. Other transaction cannot apply any type of locks on exclusively locked records.

Noted that the locks are applied for short time only as other users are restricted for certain operations during this time. Record  locking mostly occurs when two or more transaction of different types functioning at the same time for two different purposes. It mostly damages the database.

 

SQL as Data Manipulation Language

Advertisement