Pessimistic Locking

Introduction

When applying Pessimistic Locking, it means that the system is always defensive and assumes that "There will certainly be other people accessing to modify this row at the same time as me, so it is best to set up a barrier to lock this row right from the reading phase (SELECT) to reserve the spot."
All types of Row-level Locks such as FOR SHARE, FOR KEY SHARE, FOR NO KEY UPDATE, FOR UPDATE in PostgreSQL are pure tools of the Pessimistic Locking mindset.

Detail

In the previous article, we explored Row-level locks and their characteristics. Now, I will guide you through its specific use cases when applied in a system like E-commerce.

First, let us create the tables and seed data as follows. This is a simple schema to illustrate how to apply Pessimistic locking to solve problems, rather than being as comprehensive as a real-world production system.

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    phone VARCHAR(20),
    loyalty_points INT DEFAULT 0
);

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    stock_quantity INT,
    price DECIMAL(10, 2)
);

CREATE TABLE user_wallets (
    user_id INT PRIMARY KEY REFERENCES customers(id),
    balance DECIMAL(10, 2)
);

CREATE TABLE coupons (
    id INT PRIMARY KEY,
    code VARCHAR(50) UNIQUE,
    description TEXT NOT NULL,
    discount_amount DECIMAL(10, 2)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    coupon_id INT REFERENCES coupons(id),
    status TEXT NOT NULL,
    total_amount DECIMAL(10, 2)
);

CREATE TABLE shipments (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id),
    tracking_number VARCHAR(100),
    carrier VARCHAR(50),
    status VARCHAR(50)
);

INSERT INTO customers (id, name, phone, loyalty_points) VALUES 
(1, 'customers 1', '123456789', 100),
(2, 'customers 2', '987654321', 50);

INSERT INTO products (id, name, stock_quantity, price) VALUES 
(101, 'product 1 (Flash Sale)', 1, 1500.00),
(102, 'product 2', 100, 15.00);

INSERT INTO coupons (id, code, description, discount_amount) VALUES 
(99, 'SUPERGAME', 'description', 50.00);

INSERT INTO orders (customer_id, coupon_id, status, total_amount) VALUES 
(1, 99, 'PENDING', 500.00);

FOR UPDATE

This is a common example when multiple customers place an order for the same product. The core requirement is to deduct the stock correctly by the number of products, preventing overselling.

-- Transaction 1
BEGIN;
SELECT stock_quantity FROM products WHERE id = 101 FOR UPDATE;
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE id = 101;
INSERT INTO orders (customer_id, total_amount) VALUES (1, 1500.00);

-- Transaction 2
BEGIN;
SELECT stock_quantity FROM products WHERE id = 101 FOR UPDATE;
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE id = 101;
INSERT INTO orders (customer_id, total_amount) VALUES (2, 1500.00);

We will use FOR UPDATE to lock when a customer places an order, then deduct the stock and create the order.
While processing that order (Transaction 1), if another customer also places an order for that product, they will be blocked (Transaction 2) and must wait until the previous order finishes processing before continuing.
Handling it sequentially in this manner ensures that the product quantity is always calculated accurately.
Note that using FOR UPDATE can precisely solve this issue, but it is not the most efficient solution. If there are high performance demands where the number of users ordering simultaneously is massive, you need to transition to other solutions like Advisory Locks or utilize Redis.

FOR NO KEY UPDATE

This is the use case for updating order status and handling shipment processing:

-- Transaction 1
BEGIN;
SELECT id, status FROM orders WHERE id = 9001 FOR NO KEY UPDATE;
UPDATE orders SET status = 'PROCESSING' WHERE id = 9001;

-- Transaction 2
BEGIN;
INSERT INTO shipments (order_id, tracking_number, carrier, status)
VALUES (2, 'ABC-123456-XYZ', 'carrier', 'PICKING');

When placing an order: You use FOR NO KEY UPDATE to lock that specific order row and then change its status accordingly.
If shipment processing is handled by another service: They will find a suitable vehicle for the goods and call a webhook so our system can continue processing.
At this point: We save the shipment information into the shipments table, which is a child table of the orders table. If you were to use FOR UPDATE instead, inserting data into the child table like this would be blocked.

FOR SHARE

This is used during the validation phase to verify the customer's order information. The main requirement is that when a customer views product details, those details (such as title, description, price and product image) must remain identical when placing the order. No one, including admins, should be allowed to alter the information during this window, as it preserves transparency in the transaction.

-- Transaction 1
BEGIN;
SELECT id, price FROM products WHERE id = 102 FOR SHARE;

-- Transaction 2
BEGIN;
SELECT price FROM products WHERE id = 102; 
UPDATE products SET price = 25.00 WHERE id = 102;

Use FOR SHARE to lock that product information, then apply business logic requirements to check order details such as phone number and address.
We do not use stronger locks like FOR UPDATE or FOR NO KEY UPDATE because this action can happen concurrently for many orders. Using FOR SHARE will not lock other transactions from reading the same content, but it will lock modifications to the product details that could impact the checkout process.

FOR KEY SHARE

This is utilized in cases where discount coupons are applied. During the coupon application process, if an admin identifies an error in the coupon details and wishes to edit it, the modification is permitted as long as the row is not deleted. Meanwhile, the customer can still use the coupon normally.

-- Transaction 1
BEGIN;
SELECT id, discount_amount FROM coupons WHERE id = 99 FOR KEY SHARE;

-- Transaction 2
BEGIN;
UPDATE coupons SET description = 'Description updated' WHERE id = 99;

We use FOR KEY SHARE, which is the weakest lock level. It still allows admins to modify coupon details but prevents them from deleting that row.
If we switched to stronger locks like FOR SHARE, no modifications to any coupon information would be allowed while it is being used.