Lock in Database

Introduction

In the database world, a lock is a mechanism that helps the system manage simultaneous access and modification of data by multiple users or processes at a single point in time.
This is the exact solution to ensure privacy and safety, because allowing unrestricted, uncontrolled access and modification of data will cause everything to become chaotic.
Locks are not created to slow down the system, but rather a mandatory blocking mechanism to keep data accurate and consistent when thousands of users access it simultaneously.

Why Do We Need Locks

Without locks, the system will fall into a data conflict state leading to data corruption, which can cause three classic problems:

Lost Update: Two users modify the same row of data at the same time and the user who saves later will overwrite and completely lose the data of the previous user.
Dirty Read: You read data that is being modified by someone else, but they have not committed yet. If they perform a rollback afterwards, the data you just read is completely meaningless.
Non-repeatable Read: Within the same session, you first read a value as A and perform logic processing on it, but the very next second someone else changes the data, you read it again and the value becomes B, making the initial operations with value A meaningless because the latest value to be processed is now B.

Lock Types

In most database management systems (such as PostgreSQL, MySQL, SQL Server), locks are usually divided into the following two basic modes:

Shared Lock (Read Lock)
- Meaning: Used when you only want to read data.
- Characteristics: Multiple users can hold a Shared Lock on the same row or table of data to read together. However, when someone holds a Shared Lock, no one is allowed to write to that data until everyone finishes reading.
Exclusive Lock (Write Lock)
- Meaning: Used when you want to write, modify or delete data.
- Characteristics: True to its name, once a process holds an Exclusive Lock, no one else has the right to read or modify that data. They are forced to queue up and wait for this process to complete via commit or rollback.

Lock Granularity

In PostgreSQL, the lock management system is divided into several main levels depending on the command and the object being locked:

Table-level Locks: Locks the entire data table. For example, when you want to change the table structure like adding or deleting columns, the system will lock the whole table, causing other read or write processes to stop and wait.
Row-level Locks: Locks only the exact row of data you are modifying. This is the most optimal type of lock because it allows other users to still modify different rows within the same table.
Page-level Locks: Locks data pages (each page is 8KB by default) to control data reading and writing at the physical layer, which happens very quickly and automatically.
Advisory Locks: Locks defined by the developers themselves. You can create a lock based on any arbitrary number in your code, for example, locking to prevent two processes from concurrently handling a specific User ID at the application layer, using the pg_advisory_lock() function.

Most of the time when programming or optimizing databases, we will work with Table Locks and Row Locks.

Race condition

This is a classic concept in programming and databases. It occurs when two or more processes (or threads/transactions) simultaneously access and want to modify a shared piece of data, and the final result depends on whoever is faster.
In other words, due to a lack of proper synchronization, data is overwritten or incorrectly calculated, leading to an inconsistent state.
Lock is one of the most primitive and common mechanisms designed to solve race conditions in databases. When data conflicts occur, databases typically employ two main locking strategies
- Pessimistic Locking: When starting a Transaction, you can choose to lock the rows before modifying the data. Other transactions wanting to read or write must queue and wait until the first transaction completes (Commit/Rollback).
- Optimistic Locking: No locks are held during the reading or modifying phase, but before the Commit, the database checks whether the data has been modified by someone else (usually using a version or timestamp column). If changes are detected, it rejects the operation and throws an error for the user to retry.

MVCC

MVCC (Multi-Version Concurrency Control) is an extremely clever method to resolve race condition issues without relying on the traditional Lock mechanism that causes system bottlenecks.
Instead of forcing one user to wait for another (which degrades performance), MVCC allows multiple users to read and write data simultaneously based on one main principle: "Readers do not block Writers, Writers do not block Readers".
MVCC serves as the core foundation for most popular databases today, such as PostgreSQL, MySQL or Oracle.

How it works

Instead of directly overwriting old data, every time an UPDATE or DELETE command is executed, MVCC creates a new version of that data row and assigns it a Transaction ID (TXID).

Here is the handling process when two Transactions (Tx1 and Tx2) modify the same row:

Block Phase (Locking for ownership)
- When Tx1 executes an UPDATE command, the database places an Exclusive Lock on that row.
- When Tx2 runs later and tries to UPDATE the same row, it is blocked by the lock from Tx1 and enters a blocked state.
- At this point, the Lock plays the role of preventing direct physical conflicts to avoid chaotic overwrites.
Handling for Tx1:
- MVCC manages data using versions. When Tx1 modifies a row, it actually creates a draft row, which is not yet public.
- When Tx1 finishes (COMMIT or ROLLBACK), Tx2 is unblocked, then MVCC will check the consistency based on the Isolation Level of Tx2 to proceed.
Handling for Tx2:
- Tx2 uses the READ COMMITTED level (The default level for most DBs)
  - When Tx1 COMMIT succeeds, the new version modified by Tx1 officially takes effect and MVCC updates the snapshot of Tx2 at the moment the command of Tx2 resumes running.
  - At this point, Tx2 sees the latest version just committed by Tx1, and the UPDATE command of Tx2 will continue modifying that new version.
  - Example: The initial price is 10, Tx1 modifies it to 20 -> Commit, Tx2 will see the price as 20, then adds 5 to become 25.
- Tx2 uses the REPEATABLE READ or SERIALIZABLE level
  - This is when MVCC most clearly demonstrates its role in Concurrency Control. When Tx1 COMMIT occurs, the Database will have two data versions
    - One new version after Tx1 COMMIT succeeds
    - One old version which Tx2 is currently reading
  - Because it uses the REPEATABLE READ/SERIALIZABLE level, Tx2 cannot see the new data version.
  - MVCC detects this, so when Tx2 attempts a modification based on the old data version, the statement is ignored and throws a conflict error, forcing Tx2 to ROLLBACK.