# Miscellaneous Locks

LLMS index: [llms.txt](/en/llms.txt)

---

<span id="internal-chapter-14" class="pdf-anchor"></span><span id="internal-page-231" class="pdf-anchor"></span>

## 14.1 Non-Object Locks {#internal-section-14-1}

To lock a resource that is not considered a *relation*, PostgreSQL uses heavyweight locks of the `object` type.[^internal-231-1] You can lock almost anything that is stored in the system catalog: tablespaces, subscriptions, schemas, roles, policies, enumerated data types, and so on.

Let’s start a transaction that creates a table:

```psql
=> BEGIN;
=> CREATE TABLE example(n integer);
```

Now take a look at non-relation locks in the `pg_locks` table:

```psql
=> SELECT database,
  (
    SELECT datname FROM pg_database WHERE oid = database
  ) AS dbname,
  classid,
  (
    SELECT relname FROM pg_class WHERE oid = classid
  ) AS classname,
  objid,
  mode,
  granted
FROM pg_locks
WHERE locktype = 'object'
  AND pid = pg_backend_pid() \gx
-[ RECORD 1 ]--------------
database  | 16391
dbname    | internals
classid   | 2615
classname | pg_namespace
objid     | 2200
mode      | AccessShareLock
granted   | t
```

<span id="internal-page-232" class="pdf-anchor"></span>

The locked resource is defined here by three values:

**database** — the `oid` of the database that contains the object being locked (or zero if this object is common to the whole cluster)

**classid** — the `oid` listed in `pg_class` that corresponds to the name of the system catalog table defining the type of the resource

**objid** — the `oid` listed in the system catalog table referenced by `classid`

The `database` value points to the `internals` database; it is the database to which the current session is connected. The `classid` column points to the `pg_namespace` table, which lists schemas.

Now we can decipher the `objid`:

```psql
=> SELECT nspname FROM pg_namespace WHERE oid = 2200;
 nspname
---------
 public
(1 row)
```

Thus, PostgreSQL has locked the `public` schema to make sure that no one can delete it while the transaction is still running.

Similarly, object deletion requires exclusive locks on both the object itself and all the resources it depends on.[^internal-232-1]

```psql
=> ROLLBACK;
```

## 14.2 Relation Extension Locks {#internal-section-14-2}

As the number of tuples in a relation grows, PostgreSQL inserts new tuples into free space in the already available pages whenever possible. But it is clear that at some point it will have to add new pages, that is, to *extend the relation*. In terms of the physical layout, new pages get added to the end of the corresponding file (which, in turn, can lead to creation of a new file).

For new pages to be added by only one process at a time, this operation is protected by a special heavyweight lock of the `extend` type.[^internal-232-2] Such a lock is also used by index vacuuming to forbid adding new pages during an index scan.

<span id="internal-page-233" class="pdf-anchor"></span>

Relation extension locks behave a bit differently from what we have seen so far:

- They are released as soon as the extension is created, without waiting for the transaction to complete.

- They cannot cause a deadlock, so they are not included into the wait-for graph. (v. 9.6)

> [!NOTE]
> However, a deadlock check will still be performed if the procedure of extending a relation is taking longer than *deadlock\_timeout*. It is not a typical situation, but it can happen if a large number of processes perform multiple insertions concurrently. In this case, the check can be called multiple times, virtually paralyzing normal system operation.
>
> To minimize this risk, heap files are extended by several pages at once (in proportion to the number of processes awaiting the lock, but by not more than 512 pages per operation).[^internal-233-1] An exception to this rule is B-tree index files, which are extended by one page at a time.[^internal-233-2]

## 14.3 Page Locks {#internal-section-14-3}

A page-level heavyweight lock of the `page` type[^internal-233-3] is applied only by GIN indexes, and only in the following case.

GIN indexes can speed up search of elements in compound values, such as words in text documents. They can be roughly described as B-trees that store separate words rather than the whole documents. When a new document is added, the index has to be thoroughly updated to include each word that appears in this document.

To improve performance, GIN indexes allow deferred insertion, which is controlled by the *fastupdate* storage parameter. New words are first quickly added into an unordered *pending* *list*, and after a while all the accumulated entries are moved into the main index structure. Since different documents are likely to contain duplicate words, this approach proves to be quite cost-effective. (*default:* `on`)

To avoid concurrent transfer of words by several processes, the index metapage is locked in the exclusive mode until all the words are moved from the pending list to the main index. This lock does not interfere with regular index usage.

Just like relation extension locks, page locks are released immediately when the task is complete, without waiting for the end of the transaction, so they never cause deadlocks.

<span id="internal-page-234" class="pdf-anchor"></span>

## 14.4 Advisory Locks {#internal-section-14-4}

Unlike other heavyweight locks (such as relation locks), *advisory locks*[^internal-234-1] are never acquired automatically: they are controlled by the application developer. These locks are convenient to use if the application requires dedicated locking logic for some particular purpose.

Suppose we need to lock a resource that does not correspond to any database object (which we could lock using `SELECT FOR` or `LOCK TABLE` commands). In this case, the resource needs to be assigned a numeric ID. If the resource has a unique name, the easiest way to do it is to generate a hash code for this name:

```psql
=> SELECT hashtext('resource1');
 hashtext
-----------
 991601810
(1 row)
```

PostgreSQL provides a whole class of functions for managing advisory locks.[^internal-234-2] Their names begin with the `pg_advisory` prefix and can contain the following words that hint at the function purpose:

**lock** — acquire a lock

**try** — acquire a lock if it can be done without waits

**unlock** — release the lock

**share** — use a shared locking mode (by default, the exclusive mode is used)

**xact** — acquire and hold a lock till the end of the transaction (by default, the lock is held till the end of the session)

Let’s acquire an exclusive lock until the end of the session:

```psql
=> BEGIN;
=> SELECT pg_advisory_lock(hashtext('resource1'));
=> SELECT locktype, objid, mode, granted
FROM pg_locks WHERE locktype = 'advisory' AND pid = pg_backend_pid();
 locktype |   objid   |     mode      | granted
----------+-----------+---------------+---------
 advisory | 991601810 | ExclusiveLock | t
(1 row)
```

<span id="internal-page-235" class="pdf-anchor"></span>

For advisory locks to actually work, other processes must also observe the established order when accessing the resource; it must be guaranteed by the application.

The acquired lock will be held even after the transaction is complete:

```psql
=> COMMIT;
=> SELECT locktype, objid, mode, granted
FROM pg_locks WHERE locktype = 'advisory' AND pid = pg_backend_pid();
 locktype |   objid   |     mode      | granted
----------+-----------+---------------+---------
 advisory | 991601810 | ExclusiveLock | t
(1 row)
```

Once the operation on the resource is over, the lock has to be explicitly released:

```psql
=> SELECT pg_advisory_unlock(hashtext('resource1'));
```

## 14.5 Predicate Locks {#internal-section-14-5}

The term *predicate lock* appeared as early as the first attempts to implement full isolation based on locks.[^internal-235-1] The problem confronted at that time was that locking all the rows to be read and updated still could not guarantee full isolation. Indeed, if *new* rows that satisfy the filter condition get inserted into the table, they will become *phantoms*. (*p.* [42](/en/02-isolation/#internal-page-42))

For this reason, it was suggested to lock conditions (predicates) rather than rows. If you run a query with the *a* &gt; 10 predicate, locking this predicate will not allow adding new rows into the table if they satisfy this condition, so phantoms will be avoided. The trouble is that if a query with a different predicate appears, such as *a* &lt; 20, you have to find out whether these predicates overlap. In theory, this problem is algorithmically unsolvable; in practice, it can be solved only for a very simple class of predicates (like in this example).

In PostgreSQL, the `Serializable` isolation level is implemented in a different way: it uses the Serializable Snapshot Isolation (SSI) protocol.[^internal-235-2] The term *predicate lock* still remains, but its sense has radically changed. In fact, such “locks” do not lock anything: they are used to track data dependencies between different transactions.

It is proved that snapshot isolation at the `Repeatable Read` level allows no anomalies except for the *write skew* and the *read-only transaction anomaly*. These two anomalies result in (*p.* [55](/en/02-isolation/#internal-page-55))

<span id="internal-page-236" class="pdf-anchor"></span>

certain patterns in the data dependence graph that can be discovered at a relatively low cost.

The problem is that we must differentiate between two types of dependencies:

- The first transaction reads a row that is later updated by the second transaction (RW dependency).

- The first transaction modifies a row that is later read by the second transaction (WR dependency).

WR dependencies can be detected using regular locks, but RW dependencies have to be tracked via predicate locks. Such tracking is turned on automatically at the `Serializable` isolation level, and that’s exactly why it is important to use this level for *all* transactions (or at least all the interconnected ones). If any transaction is running at a different level, it will not set (or check) predicate locks, so the `Serializable` level will be downgraded to `Repeatable Read`.

I would like to stress once again that despite their name, predicate locks do not lock anything. Instead, a transaction is checked for “dangerous” dependencies when it is about to be committed, and if PostgreSQL suspects an anomaly, this transaction will be aborted.

Let’s create a table with an index that will span several pages (it can be achieved by using a low *fillfactor* value):

```psql
=> CREATE TABLE pred(n numeric, s text);
=> INSERT INTO pred(n) SELECT n FROM generate_series(1,10000) n;
=> CREATE INDEX ON pred(n) WITH (fillfactor = 10);
=> ANALYZE pred;
```

If the query performs a sequential scan, a predicate lock is acquired on the whole table (even if some of the rows do not satisfy the provided filter conditions).

<span id="internal-page-237" class="pdf-anchor"></span>

```psql
=> SELECT pg_backend_pid();
 pg_backend_pid
----------------
          34753
(1 row)
=> BEGIN ISOLATION LEVEL SERIALIZABLE;
=> EXPLAIN (analyze, costs off, timing off, summary off)
  SELECT * FROM pred WHERE n > 100;
                 QUERY PLAN
---------------------------------------------
 Seq Scan on pred (actual rows=9900 loops=1)
   Filter: (n > '100'::numeric)
   Rows Removed by Filter: 100
(3 rows)
```

Although predicate locks have their own infrastructure, the `pg_locks` view displays them together with heavyweight locks. All predicate locks are always acquired in the `SIRead` mode, which stands for Serializable Isolation Read:

```psql
=> SELECT relation::regclass, locktype, page, tuple
FROM pg_locks WHERE mode = 'SIReadLock' AND pid = 34753
ORDER BY 1, 2, 3, 4;
 relation | locktype | page | tuple
----------+----------+------+-------
 pred     | relation |      |
(1 row)
```

```psql
=> ROLLBACK;
```

Note that predicate locks may be held longer than the transaction duration, as they are used to track dependencies *between* transactions. But anyway, they are managed automatically.

If the query performs an index scan, the situation improves. For a B-tree index, it is enough to set a predicate lock on the read heap tuples and on the scanned leaf pages of the index. It will “lock” the whole range that has been read, not only the exact values.

```psql
=> BEGIN ISOLATION LEVEL SERIALIZABLE;
=> EXPLAIN (analyze, costs off, timing off, summary off)
  SELECT * FROM pred WHERE n BETWEEN 1000 AND 1001;
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using pred_n_idx on pred (actual rows=2 loops=1)
   Index Cond: ((n >= '1000'::numeric) AND (n <= '1001'::numeric))
(2 rows)
```

```psql
=> SELECT relation::regclass, locktype, page, tuple
FROM pg_locks WHERE mode = 'SIReadLock' AND pid = 34753
ORDER BY 1, 2, 3, 4;
  relation  | locktype | page | tuple
------------+----------+------+-------
 pred       | tuple    |    4 |    96
 pred       | tuple    |    4 |    97
 pred_n_idx | page     |   28 |
(3 rows)
```

<span id="internal-page-238" class="pdf-anchor"></span>

The number of leaf pages corresponding to the already scanned tuples can change: for example, an index page can be split when new rows get inserted into the table. However, PostgreSQL takes it into account and locks newly appeared pages too:

```psql
=> INSERT INTO pred
  SELECT 1000+(n/1000.0) FROM generate_series(1,999) n;
=> SELECT relation::regclass, locktype, page, tuple
FROM pg_locks WHERE mode = 'SIReadLock' AND pid = 34753
ORDER BY 1, 2, 3, 4;
  relation  | locktype | page | tuple
------------+----------+------+-------
 pred       | tuple    |    4 |    96
 pred       | tuple    |    4 |    97
 pred_n_idx | page     |   28 |
 pred_n_idx | page     |  266 |
 pred_n_idx | page     |  267 |
 pred_n_idx | page     |  268 |
 pred_n_idx | page     |  269 |
(7 rows)
```

Each read tuple is locked separately, and there may be quite a few of such tuples. Predicate locks use their own pool allocated at the server start. The total number of predicate locks is limited by the value of *max\_pred\_locks\_per\_transaction* parameter multiplied by *max\_connections* (despite the parameter names, predicate locks are not being counted per separate transactions). (*default:* `64`) (*default:* `100`)

Here we get the same problem as with row-level locks, but it is solved in a different way: *lock escalation* is applied.[^internal-238-1]

As soon as the number of tuple locks related to one page exceeds the value of the *max\_pred\_locks\_per\_page* parameter, they are replaced by a single page-level lock. (v. 10) (*default:* `2`)

```psql
=> EXPLAIN (analyze, costs off, timing off, summary off)
  SELECT * FROM pred WHERE n BETWEEN 1000 AND 1002;
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using pred_n_idx on pred (actual rows=3 loops=1)
   Index Cond: ((n >= '1000'::numeric) AND (n <= '1002'::numeric))
(2 rows)
```

Instead of three locks of the `tuple` type we now have one lock of the `page` type:

<span id="internal-page-239" class="pdf-anchor"></span>

```psql
=> SELECT relation::regclass, locktype, page, tuple
FROM pg_locks WHERE mode = 'SIReadLock' AND pid = 34753
ORDER BY 1, 2, 3, 4;
  relation  | locktype | page | tuple
------------+----------+------+-------
 pred       | page     |    4 |
 pred_n_idx | page     |   28 |
 pred_n_idx | page     |  266 |
 pred_n_idx | page     |  267 |
 pred_n_idx | page     |  268 |
 pred_n_idx | page     |  269 |
(6 rows)
```

```psql
=> ROLLBACK;
```

Escalation of page-level locks follows the same principle. If the number of such locks for a particular relation exceeds the *max\_pred\_locks\_per\_relation* value, they get replaced by a single relation-level lock. (If this parameter is set to a negative value, the threshold is calculated as *max\_pred\_locks\_per\_transaction* divided by the absolute value of *max\_pred\_locks\_per\_relation*; thus, the default threshold is 32). (v. 10) (*default:* `-2`) (*default:* `64`)

Lock escalation is sure to lead to multiple false-positive serialization errors, which negatively affects system throughput. So you have to find an appropriate balance between performance and spending the available RAM on locks.

Predicate locks support the following index types:

- B-trees

- hash indexes, GiST, and GIN (v. 11)

If an index scan is performed, but the index does not support predicate locks, the whole index will be locked. It is only to be expected that the number of transactions aborted for no good reason will also increase in this case.

For more efficient operation at the `Serializable` level, it makes sense to explicitly declare read-only transactions as such using the `READ ONLY` clause. If the lock manager sees that a read-only transaction will not conflict with other transactions,[^internal-239-1] it can release the already set predicate locks and refrain from acquiring new ones. And if such a transaction is also declared `DEFERRABLE`, the read-only transaction anomaly will be avoided too. (*p.* [59](/en/02-isolation/#internal-page-59))

[^internal-231-1]: [backend/storage/lmgr/lmgr.c](<https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/storage/lmgr/lmgr.c;hb=REL_14_STABLE>), LockDatabaseObject &amp; LockSharedObject functions
[^internal-232-1]: [backend/catalog/dependency.c](<https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/catalog/dependency.c;hb=REL_14_STABLE>), performDeletion function
[^internal-232-2]: [backend/storage/lmgr/lmgr.c](<https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/storage/lmgr/lmgr.c;hb=REL_14_STABLE>), LockRelationForExtension function
[^internal-233-1]: [backend/access/heap/hio.c](<https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/hio.c;hb=REL_14_STABLE>), RelationAddExtraBlocks function
[^internal-233-2]: [backend/access/nbtree/nbtpage.c](<https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/nbtree/nbtpage.c;hb=REL_14_STABLE>), \_bt\_getbuf function
[^internal-233-3]: [backend/storage/lmgr/lmgr.c](<https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/storage/lmgr/lmgr.c;hb=REL_14_STABLE>), LockPage function
[^internal-234-1]: [postgresql.org/docs/14/explicit-locking.html#ADVISORY-LOCKS](<https://postgresql.org/docs/14/explicit-locking.html#ADVISORY-LOCKS>)
[^internal-234-2]: [postgresql.org/docs/14/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS](<https://postgresql.org/docs/14/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS>)
[^internal-235-1]: [K.](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [P.](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [Eswaran,](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [J.](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [N.](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [Gray,](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [R.](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [A.](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [Lorie,](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [I.](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [L.](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [Traiger](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>)[.](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [The](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [notions](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [of](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [consistency](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [and](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [predicate](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [locks](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [in](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [a](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [database](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>) [system](<https://jimgray.azurewebsites.net/papers/on%20the%20notions%20of%20consistency%20and%20predicate%20locks%20in%20a%20database%20system%20cacm.pdf>)
[^internal-235-2]: [backend/storage/lmgr/README-SSI](<https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/storage/lmgr/README-SSI;hb=REL_14_STABLE>) [backend/storage/lmgr/predicate.c](<https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/storage/lmgr/predicate.c;hb=REL_14_STABLE>)
[^internal-238-1]: [backend/storage/lmgr/predicate.c](<https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/storage/lmgr/predicate.c;hb=REL_14_STABLE>), PredicateLockAcquire function
[^internal-239-1]: [backend/storage/lmgr/predicate.c](<https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/storage/lmgr/predicate.c;hb=REL_14_STABLE>), SxactIsROSafe macro