BE - Database Management Systems

locks are mechanisms used in concurrency control to synchronize access to the database by multiple transactions. They ensure that the database remains consistent when transactions execute simultaneously. A lock restricts access to a data item by allowing only one transaction (or a compatible set of transactions) to use it at a time. The common types of locks are:

1. Binary Locks (Simple Lock):
   A binary lock has only two states – locked (1) or unlocked (0). If a data item is locked, no other transaction can access it until it is unlocked. While simple to implement, binary locks are too restrictive for practical use.

2. Shared and Exclusive Locks (Read–Write Locks):

   • Shared Lock (S-lock): When a transaction wants to read a data item, it obtains a shared lock. Multiple transactions can hold shared locks on the same item simultaneously since reading does not cause conflicts.

   • Exclusive Lock (X-lock): When a transaction wants to write (update, delete, insert) a data item, it must acquire an exclusive lock. Only one transaction can hold an exclusive lock on a data item, and it is not compatible with shared locks.

3. Multiple Granularity Locks:
   These allow locking at different levels of granularity such as database, table, page, or record. For example, a transaction may lock an entire table instead of individual rows. This approach improves efficiency but may reduce concurrency.

4. Intention Locks:
   Used in combination with multiple granularity locks. Before placing a shared or exclusive lock at a lower level, a transaction places an intention lock at a higher level to indicate its intention. For example, placing an intention-shared (IS) lock on a table before acquiring a shared lock on one of its rows. This prevents conflicts between coarse-grained and fine-grained locks.

5. Two-Phase Locking (2PL) Protocol:
   Although not a type of lock itself, it is a method of using locks. In this protocol, a transaction goes through two phases: a growing phase (acquiring locks without releasing any) and a shrinking phase (releasing locks without acquiring new ones). This guarantees conflict serializability.