Database Locks

What are Locks in Database?

In programming, Locks are used to having controlled access to resources i.e to prevent multiple threads from accessing the resources at the same time. Likewise, Database Locks provide mechanisms to access shared resources such as tables and rows in a multi-user environment.

Why do we need locks?

Let's take an example of a money transfer operation where user A transfers money to user B and user C transfers money to user A:

The above example produces an inconsistent result thus User A is unaware of the User C updates.

In this case, User A acquired a lock 🔒, so User C was not able to access the rows.

Lock Modes

Shared Locks: Shared locks, also known as read locks, are used to allow multiple users to read a resource simultaneously while preventing any user from modifying it. Shared locks are useful when several users need to access the same data for reading purposes.

Exclusive Locks: Exclusive locks, also known as write locks, are used to grant exclusive access to a resource to a single user or application, preventing any other users from accessing or modifying it. Exclusive locks are useful when a user needs to modify a resource without interference from other users.

Update Locks: Update locks are a combination of shared and exclusive locks. They allow multiple users to read a resource simultaneously but require exclusive access when a user needs to modify the resource. Update locks are useful when a resource needs to be frequently read but modified less frequently.

Intent Locks: Intent locks are used to indicate that a transaction or user intends to acquire a lock on a resource. Intent locks can be used to coordinate the acquisition of shared or exclusive locks between multiple resources to prevent deadlocks.

Schema Locks: Schema locks are used to control access to the schema of a database, including tables, views, and stored procedures. Schema locks prevent other users from modifying the schema while a user is making changes to it.

Range Locks: Range locks are used to lock a range of values in an index, preventing other users from modifying the same range of values concurrently. Range locks are useful when multiple users need to access the same range of values in an index.

Problems associated with locks

Deadlocks: Deadlocks occur when two or more transactions are waiting for each other to release locks on shared resources, leading to a situation where none of the transactions can proceed. Deadlocks can cause a system to become unresponsive, and can only be resolved by aborting one or more of the transactions involved.

Lock Contention: Lock contention occurs when multiple transactions are waiting to acquire the same lock on a shared resource, leading to delays and reduced system performance. Lock contention can be mitigated by using lock escalation, where a database system converts multiple low-level locks into a higher-level lock.

Locking Overhead: Locking overhead occurs when a database system needs to manage a large number of locks, leading to increased memory and processing requirements. Locking overhead can be reduced by using lock-free data structures, where multiple users or applications can access shared resources concurrently without the need for locks.

Reduced Concurrency: Locks can reduce concurrency by preventing multiple users or applications from accessing shared resources simultaneously. This can lead to delays and decreased system performance, especially in scenarios where concurrent access is frequent.