Locks

Eugenia Kuzina

ADPG/PostgreSQL provides various lock modes to control concurrent access to data in tables. Most PostgreSQL commands use locks of appropriate modes to ensure that referenced tables are not dropped or modified in incompatible ways while the command executes. For example, TRUNCATE cannot safely be executed concurrently with other operations on the same table, it obtains an ACCESS EXCLUSIVE lock on the table. You can also implement application-controlled locking in situations where MVCC does not suit your requirements.

Based on the type of object being blocked, the following locks can be distinguished:

  • Table-level locks — lock a table.

  • Row-level locks — lock the current row.

  • Page-level locks — page-level share/exclusive locks are used to control read/write access to table pages in the shared buffer pool. These locks are released immediately after a row is fetched or updated. Application developers generally do not need to be concerned with page-level locks.

Locks can also be:

  • exclusive — can only be held by one transaction at a time;

  • shared — can be held by multiple transactions.

Table-level locks

The table below shows the available lock modes and the contexts in which ADPG/PostgreSQL uses them. You can also obtain any of these locks explicitly with the LOCK command as described below. All of these lock modes are table-level locks, even if the name contains the word "row", since the names of the lock modes are historical.

Table-level locks
Name Use of the lock

ACCESS SHARE (AccessShareLock)

The SELECT command acquires a lock of this mode on referenced tables. In general, any query that only reads a table and does not modify it will acquire this lock mode

ROW SHARE (RowShareLock)

The SELECT FOR UPDATE and SELECT FOR SHARE commands acquire a lock of this mode on the target tables (in addition to ACCESS SHARE locks on any other tables that are referenced but not selected FOR UPDATE/FOR SHARE)

ROW EXCLUSIVE (RowExclusiveLock)

The UPDATE, DELETE, and INSERT commands acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other referenced tables). In general, this lock mode will be acquired by any command that modifies data in a table

SHARE UPDATE EXCLUSIVE (ShareUpdateExclusiveLock)

This mode is acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, COMMENT ON, REINDEX CONCURRENTLY, and certain ALTER INDEX and ALTER TABLE variants (for more information, see the documentation of these commands)

SHARE (ShareLock)

Protects a table against concurrent data changes. It is acquired by CREATE INDEX (without CONCURRENTLY)

SHARE ROW EXCLUSIVE (ShareRowExclusiveLock)

Protects a table against concurrent data changes, and it is self-exclusive so that only one session can hold it at a time. It is acquired by CREATE TRIGGER and some forms of ALTER TABLE

EXCLUSIVE (ExclusiveLock)

Allows only concurrent ACCESS SHARE locks — only reads from the table can proceed in parallel with a transaction holding the EXCLUSIVE lock. It is acquired by REFRESH MATERIALIZED VIEW CONCURRENTLY

ACCESS EXCLUSIVE (AccessExclusiveLock)

Guarantees that only the transaction that has acquired this lock can access the table. Acquired by the DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, and REFRESH MATERIALIZED VIEW (without CONCURRENTLY) commands. Many forms of ALTER INDEX and ALTER TABLE also acquire a lock at this level. This is also the default lock mode for the LOCK TABLE statement. Only an ACCESS EXCLUSIVE lock blocks the SELECT (without FOR UPDATE/SHARE) statement

The main difference between one lock mode and another is the set of conflicting lock modes. See the Conflicting table-level locks table. Two transactions cannot hold locks of conflicting modes simultaneously on the same table. At the same time, different transactions can simultaneously own locks of non-conflicting modes. Note that some lock modes are self-conflicting. For example, an ACCESS EXCLUSIVE lock cannot be held by more than one transaction at a time. Other modes are not self-conflicting. For example, an ACCESS SHARE lock can be held by multiple transactions. In the table below, the intersection of a row and a column with conflicting transactions is marked with the X symbol.

Conflicting table-level locks

Requested lock mode

Existing lock mode

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

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

Once acquired, a lock is normally held until the end of the transaction. But if a lock is acquired after establishing a savepoint, the lock is released immediately if a rollback to the save point occurs.

Row-level Locks

In addition to table-level locks, ADPG/PostgreSQL uses row-level locks. The types of locks and the cases in which ADPG/PostgreSQL uses them are listed in the table below.

Row-level locks
Name Use of the lock

FOR UPDATE

FOR UPDATE locks the rows retrieved by the SELECT statement as for update. This prevents them from being locked, modified, or deleted by other transactions until the current transaction finishes. Other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE, or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends. Within a Repeatable read or Serializable transaction, an error will be thrown if a row to be locked has changed since the transaction started.

The FOR UPDATE lock mode is also acquired by any DELETE command on a row, and also by UPDATE under the following condition: UPDATE modifies the values of certain columns with a unique index on them that can be used in a foreign key (partial indexes and expressional indexes are not considered)

FOR NO KEY UPDATE

Behaves similarly to FOR UPDATE, except that the lock acquired is weaker: this lock does not block SELECT FOR KEY SHARE commands that attempt to acquire a lock on the same rows. This lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock

FOR SHARE

Behaves similarly to FOR NO KEY UPDATE, but it acquires a shared lock rather than an exclusive lock on each retrieved row. It blocks other transactions from performing the UPDATE, DELETE, SELECT FOR UPDATE, or SELECT FOR NO KEY UPDATE commands, but it does not prevent them from executing SELECT FOR SHARE or SELECT FOR KEY SHARE

FOR KEY SHARE

Behaves similarly to FOR SHARE, except that the lock is weaker: SELECT FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE. The lock blocks other transactions from performing DELETE or any UPDATE that changes the key values, but it allows other UPDATE commands and SELECT FOR NO KEY UPDATE, SELECT FOR SHARE, SELECT FOR KEY SHARE

Note that a transaction can hold conflicting locks on the same row, even in different subtransactions, but two transactions can never obtain conflicting locks of the same row. Row-level locks do not affect data querying. They block only writers and other lockers to the same row. Like table-level locks, row-level locks are released at the transaction end or during a savepoint rollback. The table below shows row-level lock conflicts.

Conflicting row-level locks

Requested lock mode

Existing lock mode

FOR KEY SHARE

FOR SHARE

FOR NO KEY UPDATE

FOR UPDATE

FOR KEY SHARE

X

FOR SHARE

X

X

FOR NO KEY UPDATE

X

X

X

FOR UPDATE

X

X

X

X

PostgreSQL does not store information about modified rows in memory, so there is no limit on the number of rows that can be locked. However, locking a row might cause a disk write. For example, SELECT FOR UPDATE modifies selected rows to mark them locked, a write to disk occurs.

The LOCK command

The LOCK command sets a table-level lock. When acquiring locks for commands that reference tables, PostgreSQL always uses the least restrictive lock mode possible. You can use the LOCK command for cases when you need more restrictive locking. It allows you to set the desired lock mode. If no lock mode is specified in the command, ACCESS EXCLUSIVE, the most restrictive mode, is used.

For example, obtain a SHARE lock on a primary key table when going to perform an insert into a foreign key table:

BEGIN;
LOCK TABLE author IN SHARE MODE;
INSERT INTO book (author_id, title, public_year) VALUES
((SELECT id FROM author
WHERE name = 'Herbert Wells'),
'The Time Machine',
1895);
COMMIT;

LOCK waits if necessary for any conflicting locks to be released. You can change this behavior using the NOWAIT option. If NOWAIT is specified, LOCK does not wait to acquire the desired lock. If it cannot be acquired immediately, PostgreSQL aborts the command and throws an error. The following code shows how to use NOWAIT:

BEGIN;
LOCK TABLE book IN SHARE ROW EXCLUSIVE MODE NOWAIT;
DELETE FROM book WHERE author_id =1;
COMMIT;

Once acquired, the lock is held until the end of the current transaction. There is no UNLOCK command. Locks are always released at the transaction end.

When the LOCK command blocks a view, all relations referenced in the view definition query are also locked recursively with the same lock mode.

Note that there is no LOCK in the SQL standard, which instead uses SET TRANSACTION to specify concurrency levels on transactions.

View locks

To examine a list of the currently outstanding locks in a database server, use the pg_locks system view.

Create the accounts table for demonstration purposes:

-- First session

CREATE TABLE accounts(
  acc_number integer PRIMARY KEY,
  balance numeric
);

INSERT INTO accounts VALUES (1234,1000.00), (5432,2000.00), (2346,3000.00);

It is convenient to view locks for a specific session. To do this, obtain ID of the server process attached to the current session using the pg_backend_pid() function (see System information functions and operators).

-- First session
SELECT pg_backend_pid();

Result:

 pg_backend_pid
----------------
           6155

Start a transaction and update a row in the table:

-- First session
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE acc_number = 1234;

Query pg_locks using the session process ID (6155):

-- First session
SELECT locktype, relation::regclass, transactionid AS xid, mode, granted
    FROM pg_locks WHERE pid = 6155;

Result:

   locktype    |   relation    |   xid   |       mode       | granted
---------------+---------------+---------+------------------+---------
 relation      | pg_locks      |         | AccessShareLock  | t
 relation      | accounts_pkey |         | RowExclusiveLock | t
 relation      | accounts      |         | RowExclusiveLock | t
 virtualxid    |               |         | ExclusiveLock    | t
 transactionid |               | 2584964 | ExclusiveLock    | t

Where:

  • locktype — type of the lockable object, see Wait events of type lock;

  • relation — relation name obtained by casting OID from the relation field to the regclass type;

  • mode — lock mode, the names given in the Table-level locks table in parentheses are used;

  • xid — transaction ID;

  • granted — takes the value of t if the lock is held, otherwise f (the lock is awaited).

Start the second session and a new transaction:

-- Second session
BEGIN;
SELECT pg_backend_pid();

Result:

 pg_backend_pid
----------------
          26741

Try to create an index:

-- Second session
CREATE INDEX ON accounts(acc_number);

Query pg_locks using the process ID of the second session (26741) from the first session:

-- First session
SELECT locktype, relation::regclass, transactionid AS xid, mode, granted
    FROM pg_locks WHERE pid = 26741;

As a result, you can see that the second session transaction waits a SHARE lock:

  locktype  | relation | xid |     mode      | granted
------------+----------+-----+---------------+---------
 virtualxid |          |     | ExclusiveLock | t
 relation   | accounts |     | ShareLock     | f

Terminate the transaction from the first session and check the result:

-- First session
COMMIT;
SELECT locktype, relation::regclass, transactionid AS xid, mode, granted
    FROM pg_locks WHERE pid = 26741;

The second session transaction acquired a SHARE lock to create an index:

   locktype    | relation |   xid   |        mode         | granted
---------------+----------+---------+---------------------+---------
 virtualxid    |          |         | ExclusiveLock       | t
 relation      | 289625   |         | AccessExclusiveLock | t
 relation      | accounts |         | ShareLock           | t
 transactionid |          | 2586134 | ExclusiveLock       | t

Deadlocks

Deadlocks is a situation where two (or more) transactions hold locks in such a way that they mutually block each other. Frequent use of explicit locks can increase the likelihood of deadlocks.

For example, if transaction 1 acquires an exclusive lock on table A and then tries to acquire an exclusive lock on table B, while transaction 2 has already exclusive-locked table B and now tries to obtain an exclusive lock on table A. None of the transactions will be able to continue. ADPG/PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions. It allows the others to complete. Which transaction will be aborted is difficult to predict and should not be relied upon.

Note that deadlocks can also occur as the result of row-level locks. So, they can occur even if explicit locking is not used. Consider the case in which two concurrent transactions modify a table. The first transaction executes:

UPDATE accounts SET balance = balance + 1000.00 WHERE acc_number = 1234;

It acquires a row-level lock on the row with acc_number = 1234. Then, the second transaction executes:

UPDATE accounts SET balance = balance + 500.00 WHERE acc_number = 5432;
UPDATE accounts SET balance = balance - 500.00 WHERE acc_number = 1234;

The first UPDATE statement successfully acquires a row-level lock on the specified row (with acc_number = 5432) and updates that row. However, the second UPDATE statement finds that the row it is attempting to update has already been locked, so it waits for the transaction that acquired the lock to complete. The second transaction is now waiting for the first transaction to complete before it continues execution.

Then, the first transaction executes:

UPDATE accounts SET balance = balance - 1000.00 WHERE acc_number = 5432;

The first transaction attempts to acquire a lock on the specified row, but it cannot: the second transaction already holds such a lock. So it waits for the second transaction to complete. So, the first transaction blocks the second transaction, and the second transaction blocks the first transaction. A deadlock occurs. ADPG/PostgreSQL detects this situation and aborts one of the transactions.

Typically, the best way to prevent deadlocks is to ensure that all applications accessing the database request locks on multiple objects in a consistent order. In the example above, if both transactions had updated the rows in the same order, no deadlock would have occurred.

Also, locks should be ordered so that the first lock acquired on an object is the most restrictive mode. If such an order cannot be ensured in advance, then deadlocks can be handled on the fly by retrying transactions that abort due to deadlocks.

Advisory locks

ADPG/PostgreSQL provides a means for creating locks that have application-defined meanings. These are called advisory locks. It is up to the application to use them correctly. Advisory locks can be useful for locking strategies that do not follow the MVCC model.

There are two ways to acquire an advisory lock: at the session level and the transaction level. Once acquired at the session level, an advisory lock is held until explicitly released or the session terminates. Unlike standard lock requests, advisory locks at the session level violate transaction logic — a lock acquired in a transaction, even if the transaction is rolled back, will be retained in the session. Similarly, releasing a lock remains in effect even if the transaction in which it was performed is later aborted. A lock can be acquired multiple times by its owning process; for each completed lock request there must be a corresponding unlock request before the lock is actually released.

Transaction-level lock requests are automatically released at the end of the transaction and do not require explicit unlock operations. This behavior is often more convenient than the session-level behavior for short-term usage of an advisory lock. Session-level and transaction-level lock requests for the same advisory lock identifier will block each other in the expected way. If a session already holds a given advisory lock, additional requests by it will always succeed, even if other sessions are awaiting the lock. This statement is true regardless of at what level (session or transaction) new locks are requested.

For example, obtain an exclusive session-level advisory lock. Functions that manipulate advisory locks use numeric identifiers. As a possible solution, you can use the hash code from the name:

BEGIN;
SELECT pg_advisory_lock(hashtext('custom_name'));
....
COMMIT;

SELECT pg_advisory_unlock(hashtext('custom_name'));

The functions that manipulate advisory locks are described in the Advisory lock functions section.

For example, you can use advisory locks to create a function that prevents a multi-user execution:

CREATE FUNCTION single_call() RETURNS VOID AS $$
BEGIN
    PERFORM pg_advisory_lock(1234);

     EXECUTE 'UPDATE pgbench_accounts
        SET abalance = abalance + 1'; -- execute requred commands

    PERFORM pg_advisory_unlock(1234);
END;
$$  LANGUAGE plpgsql

When using advisory locks, especially in queries with explicit ORDER BY and LIMIT statements, it is important to consider that the resulting locks may depend on the order in which the SQL expressions are evaluated. In the following example, LIMIT will not necessarily be applied before calling the pg_advisory_lock function. The application may acquire locks that it did not expect and will not be able to release until the session ends:

SELECT pg_advisory_lock(id) FROM table1 WHERE id > 12345 LIMIT 100;

The following query solves this problem:

SELECT pg_advisory_lock(q.id) FROM
(
SELECT id FROM table1 WHERE id > 12345 LIMIT 100
) q;

Both advisory locks and regular locks are stored in a shared memory pool. Its size is defined by the configuration parameters max_locks_per_transaction and max_connections. You can set these parameters on the Clusters → ADPG cluster → Services → ADPG → Primary configuration tab in ADCM UI. Use the ADPG configurations section to set max_locks_per_transaction (see Configuration parameters). If this memory is insufficient, the server will not be able to issue any lock.

Like all locks in PostgreSQL, a complete list of advisory locks currently held by any session can be found in the pg_locks system view. The locktype field contains the value advisory for them.

Found a mistake? Seleсt text and press Ctrl+Enter to report it