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.

Happy coding!

See more articles here.