Understanding Database Locks: Managing Concurrency in Databases

Consistency in a database is crucial when building a system that serve many users. Imagine maintaining a concert booking system, and a very famous artist has just started selling assigned seats to the concert. Many people start purchasing seats as soon as the selling time begins. What you don't want to happen in this case is for seats to be oversold, resulting in more than one person being assigned to a single seat. This could cause a disaster at the event as people might fight for their seats, ultimately damaging your reputation as the person managing the booking system. In this article, we will explore how concurrency works in databases and how to prevent the scenario mentioned earlier from happening. We'll cover: Why Databases Use Locks : We'll delve more thoroughly into the previous example to understand what's happening and why database locks can prevent such issues. Shared Lock and Exclusive Lock : There are two types of locks based on how they lock processes. We'll learn about them. Read Phenomenon and Isolation Levels : The more consistency you want in your database, the stricter the database lock process will be. Inconsistent reading in a database is known as a read phenomenon, and it can be managed by adjusting the isolation level of a database. Types of Locks : We'll check out different types of database locks, how they behave, and when to use them. ℹ️ This article focuses on SQL databases. Not all concepts are applicable to their NoSQL counterparts. Why Databases Use Locks For starters, let's explore our concert booking system and break down how race conditions can occur. Suppose we have the following simplified schema for the database: Let's create a simplified query of how we can book the seat: BEGIN; SELECT * FROM seats WHERE id = '1'; UPDATE seats SET remainingQty = remainingQty - 1 WHERE id = '1'; COMMIT;

Apr 23, 2025 - 16:46

Understanding Database Locks: Managing Concurrency in Databases

Consistency in a database is crucial when building a system that serve many users. Imagine maintaining a concert booking system, and a very famous artist has just started selling assigned seats to the concert. Many people start purchasing seats as soon as the selling time begins. What you don't want to happen in this case is for seats to be oversold, resulting in more than one person being assigned to a single seat. This could cause a disaster at the event as people might fight for their seats, ultimately damaging your reputation as the person managing the booking system.

In this article, we will explore how concurrency works in databases and how to prevent the scenario mentioned earlier from happening. We'll cover:

Why Databases Use Locks : We'll delve more thoroughly into the previous example to understand what's happening and why database locks can prevent such issues.
Shared Lock and Exclusive Lock : There are two types of locks based on how they lock processes. We'll learn about them.
Read Phenomenon and Isolation Levels : The more consistency you want in your database, the stricter the database lock process will be. Inconsistent reading in a database is known as a read phenomenon, and it can be managed by adjusting the isolation level of a database.
Types of Locks : We'll check out different types of database locks, how they behave, and when to use them.

ℹ️ This article focuses on SQL databases. Not all concepts are applicable to their NoSQL counterparts.

Why Databases Use Locks

For starters, let's explore our concert booking system and break down how race conditions can occur. Suppose we have the following simplified schema for the database:

Let's create a simplified query of how we can book the seat:

BEGIN;
SELECT * FROM seats WHERE id = '1';
UPDATE seats SET remainingQty = remainingQty - 1 WHERE id = '1';
COMMIT;