Explicit Locking

[!NOTE] This module explores the core principles of the subject, deriving solutions from first principles and hardware constraints to build world-class, production-ready expertise.

1. The Hook: The Double-Booking Disaster

Imagine you are building a ticket booking system for a highly anticipated concert. Two users, Alice and Bob, simultaneously try to book the exact same VIP seat. They both query the database: SELECT status FROM seats WHERE id = 42. The database happily tells both of them the seat is “Available”. They both proceed to purchase, and your system issues an UPDATE for both.

Congratulations! You have just double-booked a seat, furious customers are calling, and your CEO is asking why the database didn’t prevent this.

While MVCC handles most concurrency needs automatically by allowing readers to never block writers, there are times when you must prevent concurrent access to maintain absolute correctness. This is where Explicit Locking comes in. PostgreSQL provides a rich set of lock modes for tables, rows, and even arbitrary application-defined resources.

[!IMPORTANT] The Golden Rule of Locking Two transactions cannot hold locks of conflicting modes on the same object at the same time. One will wait until the other releases the lock (commits or rolls back).

2. Table-Level Locks

Analogy: The Library Renovation. If you want to replace the carpeting in the entire library, you can’t just block off one aisle. You have to lock the front door so no patrons can enter while the work is being done.

Table locks block access to an entire table. They are acquired automatically by most commands (like ALTER TABLE or TRUNCATE), but can also be requested explicitly with LOCK TABLE.

Interactive Lock Matrix

Select two commands to see if they can run concurrently on the same table.

Compatibility Checker

Transaction A holds: ACCESS SHARE (SELECT) ROW SHARE (SELECT FOR UPDATE) ROW EXCLUSIVE (UPDATE, DELETE, INSERT) SHARE UPDATE EXCLUSIVE (VACUUM, CREATE INDEX) SHARE (CREATE INDEX) SHARE ROW EXCLUSIVE EXCLUSIVE ACCESS EXCLUSIVE (DROP TABLE, TRUNCATE)

Acquired by SELECT commands.

Transaction B requests: ACCESS SHARE (SELECT) ROW SHARE (SELECT FOR UPDATE) ROW EXCLUSIVE (UPDATE, DELETE, INSERT) SHARE UPDATE EXCLUSIVE (VACUUM, CREATE INDEX) SHARE (CREATE INDEX) SHARE ROW EXCLUSIVE EXCLUSIVE ACCESS EXCLUSIVE (DROP TABLE, TRUNCATE)

Acquired by SELECT commands.

Checking...

3. Row-Level Locks

Analogy: The Reference Book. You don’t need to lock the entire library to read a single reference book. You just check it out. However, if someone else wants to write notes in the margins (update the row), they must wait until you return it.

Postgres uses fine-grained row locks for high concurrency. These are stored directly on the tuple header (remember the hidden system columns like xmax?), not in an in-memory lock table. Because of this architectural decision, you can lock millions of rows without consuming any extra RAM!

Locking Hierarchy (Weakest to Strongest)

1. FOR KEY SHARE: (Weakest) Blocks DELETE and key updates. Acquired automatically when checking foreign key constraints.
2. FOR SHARE: Blocks UPDATE, DELETE, FOR NO KEY UPDATE. Used when you need to read a row and ensure it doesn’t change until your transaction completes.
3. FOR NO KEY UPDATE: Acquired by UPDATE (that doesn’t change Primary Key). Blocks FOR SHARE.
4. FOR UPDATE: (Strongest) Acquired by DELETE or UPDATE (on Primary Key). Blocks everything except pure reads.

The Fix for the Double-Booking Disaster

The solution to our ticket booking problem is the FOR UPDATE clause:

BEGIN;
-- 1. Lock the seat row. Other transactions must wait here.
SELECT * FROM seats WHERE id = 42 FOR UPDATE;

-- 2. Application logic: Check if still available.
-- 3. Perform work...
UPDATE seats SET status = 'booked', user_id = 123 WHERE id = 42;
COMMIT;

[!TIP] Why FOR UPDATE? Use this when you select a row, calculate a new value in your app, and then update it. It prevents “Lost Updates” (Race Conditions) by ensuring no one else changes the row while you are calculating.

4. Deadlocks: The Embrace of Death

A Deadlock occurs when two transactions wait for each other infinitely.

The Cycle Anatomy

1. TX A locks Row 1.
2. TX B locks Row 2.
3. TX A tries to lock Row 2 (waits for B to release).
4. TX B tries to lock Row 1 (waits for A to release).

Result: Neither can proceed. The database grinds to a halt.

Detection & Resolution

Postgres does not let deadlocks hang forever. It runs a background “Deadlock Detector”. By default, after 1 second (deadlock_timeout parameter), it wakes up, traverses the wait-for graph, finds the cycle, and sacrifices one transaction by killing it with error code 40P01.

Prevention Strategies

1. Consistent Ordering: Always lock objects in the exact same alphabetical or numerical order across your entire application.
   • ❌ Bad: Transaction A locks (User, Account), Transaction B locks (Account, User).
   • ✅ Good: Both strictly lock (Account, User) or sort the arrays by primary key ID before updating.
2. Lock Tables Explicitly: If a bulk job updates 10,000 rows scattered across a table, taking thousands of row locks might risk a deadlock with a concurrent process. Sometimes LOCK TABLE accounts is safer (but stops all other writers).
3. FOR UPDATE SKIP LOCKED: The absolute best pattern for queue processing.

   -- Give me 10 jobs that no one else is currently working on
   SELECT * FROM jobs
   WHERE status = 'pending'
   LIMIT 10
   FOR UPDATE SKIP LOCKED;
   

5. Advisory Locks (Application Locks)

Sometimes you need to lock something abstract, like “Running End of Day Report” or “Processing User 123’s Webhook”, that doesn’t map cleanly to a single row in a table.

Postgres allows you to create locks on numbers. This transforms Postgres into a highly reliable Distributed Lock Manager (DLM), completely negating the need for external tools like Redis or ZooKeeper for simple mutexes.

-- Acquire an exclusive lock on the arbitrary number 12345
SELECT pg_advisory_lock(12345);

-- Critical section... only one session across your entire fleet can hold lock 12345.

-- Release the lock
SELECT pg_advisory_unlock(12345);

Implementing a Distributed Cron Lock

Imagine you have 5 app servers, and you want a daily email job to run at midnight. If triggered by cron on all 5 servers, customers might get 5 duplicate emails. We can use an advisory lock to elect a single “leader” to run the job.

import java.sql.*;

public class DistributedCron {
    // 12345 is our magic number for "DailyEmailJob"
    private static final long LOCK_ID = 12345;

    public void runJob(Connection conn) throws SQLException {
        // Try to acquire the lock. Returns true immediately if successful,
        // or false if another server already holds it. Does NOT wait.
        try (PreparedStatement stmt = conn.prepareStatement(
                "SELECT pg_try_advisory_lock(?)")) {
            stmt.setLong(1, LOCK_ID);
            ResultSet rs = stmt.executeQuery();

            if (rs.next() && rs.getBoolean(1)) {
                try {
                    System.out.println("Lock acquired. Sending daily emails...");
                    // Execute critical section here
                } finally {
                    // Always release in a finally block!
                    try (PreparedStatement unlockStmt = conn.prepareStatement(
                            "SELECT pg_advisory_unlock(?)")) {
                        unlockStmt.setLong(1, LOCK_ID);
                        unlockStmt.execute();
                    }
                }
            } else {
                System.out.println("Another server is running this job. Skipping.");
            }
        }
    }
}

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"

	_ "github.com/lib/pq"
)

func runJob(db *sql.DB) {
	// 12345 is our magic number for "DailyEmailJob"
	const lockID = 12345

	// pg_try_advisory_lock returns a boolean immediately
	var lockAcquired bool
	err := db.QueryRow("SELECT pg_try_advisory_lock($1)", lockID).Scan(&lockAcquired)
	if err != nil {
		log.Fatalf("Error acquiring lock: %v", err)
	}

	if lockAcquired {
		// Ensure we always release the lock when the function exits
		defer func() {
			_, err := db.Exec("SELECT pg_advisory_unlock($1)", lockID)
			if err != nil {
				log.Printf("Failed to release lock: %v", err)
			}
		}()

		fmt.Println("Lock acquired. Sending daily emails...")
		// Execute critical section here

	} else {
		fmt.Println("Another server is running this job. Skipping.")
	}
}

6. Summary

1. Table Locks: Mostly automatic. ACCESS EXCLUSIVE (acquired by DROP/ALTER) is the most dangerous as it blocks both reads and writes.
2. Row Locks: FOR UPDATE is your primary tool for preventing race conditions during read-modify-write cycles.
3. Deadlocks: A cyclical dependency of locks. Prevent them by ordering your updates consistently by primary key.
4. Advisory Locks: Leverage Postgres as a fast, reliable distributed lock manager to orchestrate application-level concurrency.