Row-level Locks

Introduction

Row-level locks are used to protect specific rows when data is being modified. Unlike table locks, Postgres does any row lock storage outside of RAM, writing them directly into the tuples (data rows) on the hard disk to avoid memory overflow.

Differences

Table-level Lock (8 modes): Protects the table schema and controls broad behaviors, such as full-table reads, table writes, or column alterations. You can imagine these 8 modes as "admission tickets" that control who can enter the building and what they can do inside.
Row-level Lock (4 modes): Protects the actual data values inside specific rows to prevent two people from modifying the same record simultaneously. It is like locking individual small rooms inside the building.

Connections

Although Table-level Lock and Row-level Lock are two distinct sets of lock modes, they always run in parallel and support each other.
When you act on a row, Postgres automatically assigns both a table lock and a row lock under the principle that obtaining a Row Lock requires first creating a Table Lock.
With this separated coordination mechanism, the 8 table-level lock modes handle keeping the table schema stable, while the 4 row-level lock modes protect rows inside that table from being modified haphazardly.
For example, when you run the command UPDATE employee SET name = 'name' WHERE id = 5
- At the table level: Postgres automatically assigns a table lock in ROW EXCLUSIVE mode (1 of the 8 table-level modes). This lock tells the system, "You are modifying a few rows in this table, anyone who wants to modify other rows or read the table can do so freely, but no one is allowed to DROP the table or alter column structures (ALTER) at this moment."
- At the row level: Postgres assigns a row lock in FOR NO KEY UPDATE mode (1 of the 4 row-level modes) to the row where id = 5. This lock tells the system, "This row with id = 5 is locked because someone is using it, other transactions that want to modify this row must line up and wait."

Lock modes

There are the following types:

FOR KEY SHARE: This is the weakest type of Row-level lock
- For rows currently being locked
  - INSERT: allow
  - UPDATE: allow updating regular columns, block updating key columns (Primary key, Foreign key, Unique column)
  - DELETE: block
- For rows with a foreign key linking to the locked rows, it allows INSERT/UPDATE/DELETE.
FOR SHARE: This lock is similar to FOR KEY SHARE but slightly stronger
- For rows currently being locked
  - INSERT: allow
  - UPDATE: block (this is the difference from FOR KEY SHARE, as it does not allow updating any columns at all)
  - DELETE: block
- For rows with a foreign key linking to the locked rows, it allows INSERT/UPDATE/DELETE.
- Multiple sessions can hold the FOR KEY SHARE/FOR SHARE lock on the same row simultaneously to read together and ensure no data is modified or deleted by others.
FOR NO KEY UPDATE: Using this key means telling Postgres that you might modify any column of this row except for the primary key column
- It is activated automatically when using the UPDATE statement for regular columns, not a Primary key or Unique key.
- For rows currently being locked
  - INSERT: allow
  - UPDATE: block
  - DELETE: block
- For rows with a foreign key linking to the locked rows, it allows INSERT/UPDATE/DELETE.
- Up to this point, you will see it is quite similar to FOR SHARE, but the difference lies in:
  - If using FOR SHARE, it allows multiple rows with FOR KEY SHARE/FOR SHARE locks to coexist.
  - If using FOR NO KEY UPDATE, it only allows running alongside the FOR KEY SHARE lock, not other locks (FOR SHARE/FOR NO KEY UPDATE/FOR UPDATE).
FOR UPDATE: Using this key means telling Postgres that you might modify any column of this row, including the primary key column
- It is activated automatically when using the UPDATE statement for a Primary key or Unique key.
- For rows currently being locked
  - INSERT: allow
  - UPDATE: block
  - DELETE: block
- You can see that this lock behaves like FOR NO KEY UPDATE but is stronger regarding rows with a foreign key linking to the locked rows
  - INSERT: not allowed to INSERT a column containing a foreign key pointing to the locked row, while inserting the id of unlocked columns is still allowed.
  - UPDATE: not allowed to UPDATE the foreign key column to the value of the locked primary key, while other values are still allowed.
  - DELETE: allow
- Similar to FOR NO KEY UPDATE, while a row is being locked, you cannot apply any other Row-level lock to any other rows.

Matrix

This is the matrix showing the relationship between Row-level locks

| Current Lock (Tx A)  | Update Non-Key (Parent)? | Update Key (Parent)?  | FK Link (Child)?  | Allowed Locks (Tx B)            | Blocked Locks (Tx B)                    |
| :------------------- | :----------------------: | :------------------:  | :--------------:  | :------------------------------ | :-------------------------------------- |
| 1. FOR KEY SHARE     |      ✅ YES              |      ❌ Blocked        |      ✅ YES       | KEY SHARE, SHARE, NO KEY UPDATE | UPDATE                                  |
| 2. FOR SHARE         |    ❌ Blocked            |      ❌ Blocked        |      ✅ YES       | KEY SHARE, SHARE                | NO KEY UPDATE, UPDATE                   |
| 3. FOR NO KEY UPDATE |    ❌ Blocked            |      ❌ Blocked        |      ✅ YES       | KEY SHARE                       | SHARE, NO KEY UPDATE, UPDATE            |
| 4. FOR UPDATE        |    ❌ Blocked            |      ❌ Blocked        |    ❌ Blocked     | None (Exclusive)                | KEY SHARE, SHARE, NO KEY UPDATE, UPDATE |

Detail

First, let us create the tables as follows

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT
);

CREATE TABLE product_variants (
  id BIGSERIAL PRIMARY KEY,
  product_id BIGINT REFERENCES products(id),
  sku VARCHAR(100) UNIQUE NOT NULL,
  variation_name VARCHAR(100) NOT NULL,
  price DECIMAL(12, 2) NOT NULL,
  stock INT NOT NULL CHECK (stock >= 0)
);

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  product_variant_id BIGINT REFERENCES product_variants(id),
  status VARCHAR(20) NOT NULL,
  total_price DECIMAL(12, 2) NOT NULL,
  shipping_address TEXT NOT NULL,
);

FOR KEY SHARE

-- Transaction 1
BEGIN;
SELECT * FROM product_variants WHERE id = 1 FOR KEY SHARE;

-- Transaction 2
BEGIN;
-- Statement 1
SELECT * FROM product_variants WHERE id = 1 FOR KEY SHARE;
SELECT * FROM product_variants WHERE id = 1 FOR SHARE;
SELECT * FROM product_variants WHERE id = 1 FOR NO KEY UPDATE;
SELECT * FROM product_variants WHERE id = 1 FOR UPDATE;

-- Statement 2
INSERT INTO product_variants(product_id, sku, variation_name, price, stock) VALUES (1, 'sku-1', 'variation_name', 1000, 100);
UPDATE product_variants SET id = 100 WHERE id = 1;
UPDATE product_variants SET variation_name = 'variation_name' WHERE id = 1;
DELETE FROM product_variants WHERE id = 1;

Statement 1: You will see that you can select with FOR KEY SHARE/FOR SHARE/FOR NO KEY UPDATE locks but cannot select with the FOR UPDATE lock.
Statement 2: The results are as follows

Can INSERT at will
Can UPDATE regular columns like variation_name but cannot update Key columns like id (primary key)
Cannot DELETE the row being blocked

FOR SHARE

-- Transaction 1
BEGIN;
SELECT * FROM product_variants WHERE id = 1 FOR SHARE;

-- Transaction 2
BEGIN;
-- Statement 1
SELECT * FROM product_variants WHERE id = 1 FOR KEY SHARE;
SELECT * FROM product_variants WHERE id = 1 FOR SHARE;
SELECT * FROM product_variants WHERE id = 1 FOR NO KEY UPDATE;
SELECT * FROM product_variants WHERE id = 1 FOR UPDATE;

-- Statement 2
INSERT INTO product_variants(product_id, sku, variation_name, price, stock) VALUES (1, 'sku-1', 'variation_name', 1000, 100);
UPDATE product_variants SET id = 100 WHERE id = 1;
UPDATE product_variants SET variation_name = 'variation_name' WHERE id = 1;
DELETE FROM product_variants WHERE id = 1;

Statement 1: Can select with FOR KEY SHARE/FOR SHARE locks but cannot select with FOR NO KEY UPDATE/FOR UPDATE locks.
Statement 2: The results are as follows

Can INSERT at will
This is the difference from FOR KEY SHARE because it does not allow UPDATE on any columns at all
Cannot DELETE the row being blocked

FOR NO KEY UPDATE

-- Transaction 1
BEGIN;
SELECT * FROM product_variants WHERE id = 1 FOR NO KEY UPDATE;

-- Transaction 2
BEGIN;
-- Statement 1
SELECT * FROM product_variants WHERE id = 1 FOR KEY SHARE;
SELECT * FROM product_variants WHERE id = 1 FOR SHARE;
SELECT * FROM product_variants WHERE id = 1 FOR NO KEY UPDATE;
SELECT * FROM product_variants WHERE id = 1 FOR UPDATE;

-- Statement 2
INSERT INTO product_variants(product_id, sku, variation_name, price, stock) VALUES (1, 'sku-1', 'variation_name', 1000, 100);
UPDATE product_variants SET variation_name = 'variation_name' WHERE id = 1;
DELETE FROM product_variants WHERE id = 1;

-- Statement 3
INSERT INTO orders(product_variant_id, status, total_price, shipping_address) VALUES (1, 'Pending', 100000, 'shipping_address');
INSERT INTO orders(product_variant_id, status, total_price, shipping_address) VALUES (2, 'Pending', 100000, 'shipping_address');

UPDATE orders SET id = 100 WHERE id = 1;
UPDATE orders SET shipping_address = 'shipping_address' WHERE id = 1;
UPDATE orders SET product_variant_id = 2 WHERE id = 1;
UPDATE orders SET product_variant_id = 1 WHERE id = 7;

DELETE FROM orders WHERE id = 1;

Statement 1: You can only select for FOR KEY SHARE, cannot select for FOR SHARE, FOR NO KEY UPDATE, FOR UPDATE.
Statement 2: When you execute in transaction 2, you will see that you can INSERT but cannot UPDATE/DELETE any columns of the row being locked.
Statement 3: You can INSERT/UPDATE/DELETE normally on the table containing a foreign key pointing to the table with locked rows, please pay attention to this part to compare the difference when using FOR UPDATE.

FOR UPDATE

-- Transaction 1
BEGIN;
SELECT * FROM product_variants WHERE id = 1 FOR UPDATE;

-- Transaction 2
BEGIN;
-- Statement 1
SELECT * FROM product_variants WHERE id = 1 FOR KEY SHARE;
SELECT * FROM product_variants WHERE id = 1 FOR SHARE;
SELECT * FROM product_variants WHERE id = 1 FOR NO KEY UPDATE;
SELECT * FROM product_variants WHERE id = 1 FOR UPDATE;

-- Statement 2
INSERT INTO product_variants(product_id, sku, variation_name, price, stock) VALUES (1, 'sku-1', 'variation_name', 1000, 100);
UPDATE product_variants SET variation_name = 'variation_name' WHERE id = 1;
DELETE FROM product_variants WHERE id = 1;

-- Statement 3
INSERT INTO orders(product_variant_id, status, total_price, shipping_address) VALUES (1, 'Pending', 100000, 'shipping_address');
INSERT INTO orders(product_variant_id, status, total_price, shipping_address) VALUES (2, 'Pending', 100000, 'shipping_address');

UPDATE orders SET id = 100 WHERE id = 1;
UPDATE orders SET shipping_address = 'shipping_address' WHERE id = 1;
UPDATE orders SET product_variant_id = 2 WHERE id = 1;
UPDATE orders SET product_variant_id = 1 WHERE id = 7;

DELETE FROM orders WHERE id = 1;

Statement 1: This is the strongest type of row lock, so it does not allow any other row locks to run concurrently.
Statement 2: You can INSERT but cannot UPDATE/DELETE any columns of the row being locked.
Statement 3: You can DELETE normally, but if you INSERT/UPDATE the product_variant_id with the value that is currently locked, it is not allowed.