Table-level Locks

Introduction

Lock Modes

• As mentioned in the previous article, the lock management system is divided into three main levels depending on the locked object: Table-level Locks, Row-level Locks and Page-level Locks.
• For each lock level, it is further divided into multiple lock modes, for instance, Table-level Locks has eight different lock modes.
• These lock modes are specific implementations derived from two primary lock types, which are Shared Lock and Exclusive Lock.
• When a statement executes, Postgres automatically assigns the appropriate lock type, so we rarely need to manually execute lock commands.
• Understanding these lock modes helps you know which SQL statements can run concurrently and which statements will force others to queue and wait.

Below are the details of the eight Table-level Lock Modes in Postgres, ordered from the lowest level (least conflict) to the highest level (blocking everything).

Access Share (AS)

• Activation command: SELECT (pure, without any locking clauses).
• Behavior: This mode conflicts exclusively with the Access Exclusive mode.
• Significance: Allows multiple users to read the table simultaneously without hindering each other.

Row Share (RS)

• Activation command: SELECT ... FOR SHARE, SELECT ... FOR KEY SHARE.
• Behavior: Conflicts with Exclusive and Access Exclusive.
• Significance: Commonly used when you want to read a few rows of data and ensure that no one can delete or modify the table structure of those rows while you are processing.

Row Exclusive (RE)

• Activation command: UPDATE, INSERT, DELETE.
• Behavior: Conflicts with modes from Share and above (Share, Share Row Exclusive, Exclusive, Access Exclusive).
• Significance: Allows multiple transactions to concurrently modify data on the same table, provided they do not modify the exact same row of data.

Share Update Exclusive (SUE)

• Activation command: VACUUM (regular), ANALYZE, CREATE INDEX CONCURRENTLY, ALTER TABLE VALIDATE CONSTRAINT.
• Behavior: Conflicts with itself and higher modes such as Share Update Exclusive, Share, Share Row Exclusive, Exclusive, Access Exclusive.
• Significance: This mode protects the table from concurrent schema changes but does not block regular data read or write operations (SELECT, INSERT, UPDATE, DELETE).

Share (S)

• Activation command: CREATE INDEX (regular, without the word CONCURRENTLY).
• Behavior: Conflicts with Row Exclusive, Share Update Exclusive, Share Row Exclusive, Exclusive, Access Exclusive.
• Significance: Allows multiple processes to read data concurrently (SELECT) but completely blocks all data modification commands (INSERT, UPDATE, DELETE).

Share Row Exclusive (SRE)

• Activation command: Rarely encountered in typical statements, Postgres automatically uses this mode for certain internal data integrity checks or specific variants of the LOCK TABLE command.
• Behavior: Conflicts with all modes except Access Share and Row Share.
• Significance: Allows only one process to hold this lock at a time to read the table and lock rows, blocking all other writing processes.

Exclusive (E)

• Activation command: REFRESH MATERIALIZED VIEW CONCURRENTLY.
• Behavior: Conflicts with most other modes except Access Share, still allowing pure SELECT statements to execute.
• Significance: Completely blocks all data write commands (INSERT, UPDATE, DELETE) as well as maintenance commands, leaving only data read permissions for the system.

Access Exclusive (AE)

• Activation command: ALTER TABLE, DROP TABLE, TRUNCATE, VACUUM FULL, REINDEX.
• Behavior: Conflicts with ALL other lock modes.
• Significance: This is the master lock. When a transaction holds this lock, no one else can perform any action on that table (even a simple SELECT command must queue and wait). This explains why running ALTER TABLE on large tables in production environments can easily hang your system due to bottlenecking.

Matrix conflict

You can observe that all eight lock modes follow these general rules:

• Read does not block Read: A standard SELECT command never blocks another SELECT command.
• Write does not block Read: The INSERT/UPDATE/DELETE commands only block each other if they operate on the exact same row (Row Lock), rather than blocking a regular SELECT statement. The exception is when you use table structure modification commands with Access Exclusive (such as ALTER TABLE or TRUNCATE), which locks the entire table completely.

Below is the conflict matrix between table lock types.

(The symbol X indicates that the two modes conflict or block each other, requiring them to queue and wait for one another)

| Lock Mode Requested    | Access Share | Row Share | Row Exclusive | Share Update Exclusive | Share | Share Row Exclusive | Exclusive | Access Exclusive |
| ---------------------- | ------------ | --------- | ------------- | ---------------------- | ----- | ------------------- | --------- | ---------------- |
| Access Share           |              |           |               |                        |       |                     |           | X                |
| Row Share              |              |           |               |                        |       |                     | X         | X                |
| Row Exclusive          |              |           |               |                        | X     | X                   | X         | X                |
| Share Update Exclusive |              |           |               | X                      | X     | X                   | X         | X                |
| Share                  |              |           | X             | X                      |       | X                   | X         | X                |
| Share Row Exclusive    |              |           | X             | X                      | X     | X                   | X         | X                |
| Exclusive              |              | X         | X             | X                      | X     | X                   | X         | X                |
| Access Exclusive       | X            | X         | X             | X                      | X     | X                   | X         | X                |

Detail

First, create the table as follows:

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    stock INT NOT NULL CHECK (stock >= 0)
);

You can use this query to view specific lock information for the examples below:

SELECT
    blocked.pid                     AS blocked_pid,
    blocked.query                   AS blocked_query,
    blocked_locks.mode              AS waiting_lock_mode,

    blocking.pid                    AS blocking_pid,
    blocking.query                  AS blocking_query,
    blocking_locks.mode             AS blocking_lock_mode,

    blocked_locks.locktype,
    blocked_locks.relation::regclass AS table_name
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked
    ON blocked.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
JOIN pg_catalog.pg_stat_activity blocking
    ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted
  AND blocking_locks.granted;

Please carefully distinguish between:

• blocking is the session creating the block (holding the lock)
• blocked is the session being blocked, which must wait for the blocking side to finish execution (release the lock) before it can resume execution

Column explanations:

• blocked_pid: PID of the session waiting for the lock.
• blocked_query: Query that cannot proceed because it has not acquired the lock.
• waiting_lock_mode: The lock mode that this query is attempting to acquire.
• blocking_pid: PID of the session currently holding the lock.
• blocking_query: Query currently holding the lock.
• blocking_lock_mode: The lock mode held by the blocking session.
• locktype: The target object the lock applies to, where relation represents a table, tuple represents a row, etc.
• table_name: The table affected by the lock.
• Note that when executing the examples below, you are required to run them on two distinct Sessions (meaning two different tabs if you use a Database Client like pgAdmin).

• If you use multiple BEGIN commands within the same Session, Postgres will only raise WARNING: there is already a transaction in progress, but it actually still executes within a single Session.
• You should open a third session to execute the lock information query to get the most accurate results.

The following examples demonstrate how these lock modes operate:

Access Share (AS)

-- Session 1: Access Share
BEGIN;
SELECT * FROM products;

-- Session 2: Access Exclusive
BEGIN;
DROP TABLE products;

• When you execute a SELECT, an Access Share lock is created, indicating your intent to read data, allowing others to read or update data as well, but prohibiting table structure changes.
• Access Share only conflicts with Access Exclusive, so while session 1 has not performed a COMMIT or ROLLBACK, the statement in session 2 will remain blocked.

Row Share (RS)

-- Session 1: Row Share
BEGIN;
SELECT * FROM products FOR SHARE;

-- Session 2: Exclusive
BEGIN;
LOCK TABLE products IN EXCLUSIVE MODE;

• Similar to Access Share in that it is used when you want to read data, but it differs in Intent as you plan to modify that data shortly.
• It also enforces a constraint that prevents others from deleting or modifying the table structure.

Row Exclusive (RE)

CREATE OR REPLACE FUNCTION dummy_trigger_func() 
RETURNS TRIGGER AS $$
BEGIN
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

-- Session 1: Row Exclusive
BEGIN;
UPDATE products SET product_name = 'product name updated' WHERE product_id = 1;

-- Session 2: Share Row Exclusive
BEGIN;
CREATE TRIGGER trg_check_products
BEFORE INSERT ON products
FOR EACH ROW
EXECUTE FUNCTION dummy_trigger_func();

• This example demonstrates a conflict between Row Exclusive in session 1 and Share Row Exclusive in session 2.
• You need to create the Function dummy_trigger_func first before utilizing it inside the Trigger.

Share Update Exclusive (SUE)

-- Session 1: Share Update Exclusive
BEGIN;
ALTER TABLE products VALIDATE CONSTRAINT products_stock_check;

-- Session 2: Share Update Exclusive
BEGIN;
ANALYZE products;

• This illustrates that Share Update Exclusive conflicts even with itself.
• products_stock_check is the constraint previously established in the products table.

Share (S)

This demonstrates a conflict between a Share lock and a Row Exclusive lock:

-- Session 1: Share
BEGIN;
CREATE INDEX idx_products_name ON products(product_name);

-- Session 2: Row Exclusive
BEGIN;
UPDATE products SET product_name = 'product name updated' WHERE product_id = 1;

Share Row Exclusive (SRE)

This demonstrates a conflict between Share Row Exclusive and Share Update Exclusive:

-- Session 1: Share Row Exclusive
BEGIN;
CREATE TRIGGER trg_check_products
BEFORE INSERT ON products
FOR EACH ROW
EXECUTE FUNCTION dummy_trigger_func();

-- Session 2: Share Update Exclusive
BEGIN;
ANALYZE products;

Exclusive (E)

CREATE MATERIALIZED VIEW mv_products AS SELECT * FROM products
CREATE UNIQUE INDEX idx_mv_product_id ON mv_products (product_id);

-- Session 1: Exclusive
BEGIN;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_products;

-- Session 2: Row Exclusive
BEGIN;
UPDATE mv_products SET product_name = 'product name updated' WHERE product_id = 1;

• This demonstrates a conflict between Exclusive and Row Exclusive.
• To execute this properly, you must first create the Materialized View and the Index.

Access Exclusive (AE)

-- Session 1: Access Exclusive
BEGIN;
ALTER TABLE products ADD COLUMN col1 TEXT;

-- Session 2: Access Exclusive
BEGIN;
ALTER TABLE products ADD COLUMN col2 INT;

• Access Exclusive is the strongest lock mode and blocks any other operations, including a basic SELECT.
• You can review the very first example concerning Access Share.
• In this scenario, it conflicts with itself as well.

Lock table statement

If you want to actively initiate Explicit Locking for each specific mode, Postgres provides support to do so as follows:

-- Row Share
LOCK TABLE products IN ROW SHARE MODE;

-- Row Exclusive
LOCK TABLE products IN ROW EXCLUSIVE MODE;

-- Share Update Exclusive
LOCK TABLE products IN SHARE UPDATE EXCLUSIVE MODE;

-- Share
LOCK TABLE products IN SHARE MODE;

-- Share Row Exclusive
LOCK TABLE products IN SHARE ROW EXCLUSIVE MODE;

-- Exclusive
LOCK TABLE products IN EXCLUSIVE MODE;

-- Access Exclusive
LOCK TABLE products IN ACCESS EXCLUSIVE MODE;

Note that there is no manual lock command for Access Share because it is already the default behavior for the SELECT command.

Happy coding!

See more articles here.