In the context of databases, a lock is a mechanism used to control concurrent access to data by multiple transactions. When a transaction wants to access a piece of data, it can request a lock on that data to ensure that it has exclusive access to it, or to prevent other transactions from modifying it until it has completed its work.

There are two types of locks commonly used in database systems: shared locks and exclusive locks.

Shared Lock

A shared lock is a type of lock that allows multiple transactions to read a piece of data simultaneously, but prevents any one of them from modifying it until the lock is released. In other words, shared locks are used to ensure that data can be accessed concurrently by multiple transactions, without any of them being able to modify the data. For example, if two transactions want to read a piece of data at the same time, they can both acquire a shared lock on that data to ensure that they can access it simultaneously.

LOCK TABLE my_table READ;

UNLOCK TABLE my_table READ;

Exclusive Lock

An exclusive lock, on the other hand, is a type of lock that grants exclusive access to a piece of data to a single transaction. It prevents other transactions from reading or modifying the data until the lock is released. Exclusive locks are used when a transaction needs to modify a piece of data, to ensure that no other transactions can access it until the modification is complete. For example, if a transaction wants to update a record in a database, it can acquire an exclusive lock on that record to prevent any other transactions from accessing it until the update is complete.

LOCK TABLE my_table WRITE;

UNLOCK TABLE my_table WRITE;

Examples

1. Suppose there is a database table containing information about bank accounts. Multiple transactions may want to read the account balance at the same time, but only one transaction can modify it at any given time. To allow concurrent access to the account balance, each transaction that wants to read the balance can acquire a shared lock on the corresponding row in the table. When a transaction wants to modify the balance, it must first acquire an exclusive lock on the corresponding row to ensure that no other transactions can access it until the update is complete.

2. Consider a database table containing information about customer orders. When a new order is placed, the inventory level of the corresponding product needs to be updated. To prevent multiple transactions from updating the same inventory record simultaneously, each transaction that wants to update the inventory level can acquire an exclusive lock on the corresponding row in the inventory table. This ensures that no other transactions can modify the same inventory record until the update is complete.

Summary

In summary, shared locks allow multiple transactions to read a piece of data simultaneously, while exclusive locks grant exclusive access to a piece of data to a single transaction. Understanding the difference between shared and exclusive locks is important when designing and working with databases, as it can help ensure that data is accessed and modified correctly and efficiently.