Home PostgreSQL Locks in PostgreSQL: 1.Relationship locks

Locks in PostgreSQL: 1.Relationship locks

by admin

The previous two series of articles were on Isolation and multiversionality. and journaling
In this series we will talk about interlocks (locks).I will stick to this term, but there may be another in the literature: lock
The cycle will consist of four parts :

  1. Relationship locks (this article);
  2. RowLocks ;
  3. Blocking other objects and predicate locks;
  4. Locks in RAM

The material in all articles is based on training courses on administration, which Pavel and I do pluzanov but does not repeat them verbatim and is intended for thoughtful reading and independent experimentation.

Read the other series as well.
Indexes :

  1. Indexingmechanism ;
  2. Access method interface , classes, and operator families;
  3. Hash ;
  4. B-tree ;
  5. GiST ;
  6. SP-GiST ;
  7. GIN ;
  8. RUM ;
  9. BRIN ;
  10. Bloom

Isolation and multiversionality :

  1. Isolation as understood by the standard and PostgreSQL;
  2. Layers, files, pages – What’s going on at the physical level;
  3. String versions, virtual and nested transactions ;
  4. Data snapshots and line version visibility, event horizon ;
  5. In-page cleanup and HOT updates ;
  6. Routine cleaning (vacuum);
  7. Automatic cleaning (autovacuum);
  8. Transaction counter overflow and freeze

Journaling :

  1. Buffer Cache ;
  2. Pre-recorded log – How it is arranged and how it is used in recovery;
  3. Checkpoint and background recording-what they are for and how they are set up;
  4. Setting up the log – levels and tasks to be solved, reliability, and performance.

Locks in PostgreSQL: 1.Relationship locks

General information about interlocks

In PostgreSQL, there are many different mechanisms that serve to lock something (or at least that’s what they’re called). That’s why I’ll start with the most general words about why we need locks in the first place, what they are, and how they differ from each other. Then we’ll look at what this variety is in PostgreSQL, and only after that we’ll start to look at the different kinds of locks in detail.
Lockdowns are used to order competitive access to shared resources.
Competitive access refers to simultaneous access of several processes. The processes themselves can run either in parallel (if the hardware allows) or sequentially in time-sharing mode – this does not matter.
If there is no competition, there is no need for locks either (e.g., a shared buffer cache requires locks, but a local one does not).
Before accessing a resource, the process must capture (acquire) the lock associated with this resource. That is, we are talking about a certain discipline: everything works as long as all processes follow the established rules for accessing the shared resource. If the DBMS manages the locks, it keeps it in order; if the application sets the locks, this is its responsibility.
At a low level, a lock is represented by a section of shared memory in which some way is noted whether the lock is free or trapped (and maybe some additional information is written: process number, time of trapping, etc.).

It can be seen that such an area of shared memory is itself a resource that can be accessed competitively. If we go down a level, we see that special synchronization primitives (such as semaphores or mutexes) provided by the OS are used to order access. They allow the code accessing a shared resource to be executed in only one process at a time. At the lowest level, these primitives are implemented based on atomic processor instructions (such as test-and-set or compare-and-swap).

After the resource is no longer needed by the process, it releases (release) lock so that the resource can be used by others.
Of course, lockjacking is not always possible: the resource might already be occupied by someone else. In that case the process either waits in a queue (if the locking mechanism allows it) or retries to grab the lock after a certain amount of time. Either way this causes the process to have to idle while waiting for the resource to be released.

Sometimes it is possible to apply other, non-blocking, strategies. For example, multiversion mechanism. allows multiple processes in some cases to work simultaneously on different versions of the data without blocking each other.

A protected resource can basically be anything, as long as the resource can be uniquely identified and the blocking address mapped to it.
For example, a resource can be an object that the DBMS works with, such as a data page (identified by the file name and position within the file), a table (oid in the system directory), a table string (page and offset within the page). A resource can be a structure in memory such as a hash table, buffer, etc. (identified by a preassigned number). Sometimes it is even convenient to use abstract resources that have no physical meaning (identified simply by a unique number).
There are many factors that influence the effectiveness of interlocks, of which we will highlight two.

  • Granularity (degree of granularity) is important if resources form a hierarchy.
    For example, a table consists of pages that contain tabular rows. All of these objects can act as resources. If processes are usually only interested in a few rows, and locking is set at the table level, other processes will not be able to handle different rows at the same time. Therefore, the higher the granularity, the better for the possibility of parallelization.
    But this increases the number of locks (information about which must be stored in memory). In this case the following can be used level up (escalation) locks : when the number of low-level, high-granular locks exceeds a certain limit, they are replaced by a single higher-level lock.
  • The locks can be captured in different modes
    The names of modes can be absolutely arbitrary, only the matrix of their compatibility with each other is important. A mode incompatible with any mode (including itself) is called exclusive or monopoly (exclusive). If the modes are compatible, then the lock can be captured by more than one process at the same time; such modes are called shared (shared). In general, the more different modes that are compatible with each other, the more opportunities for concurrency are created.

Lockouts can be divided into long and short lockouts according to the time of use.

  • Long-term locks are captured for a potentially long time (usually until the end of a transaction), and most often refer to resources such as tables (relations) and strings. PostgreSQL usually manages such locks automatically, but the user nevertheless has some control over this process.
    Long-term locks are characterized by a large number of modes, so that as many simultaneous actions on the data as possible can be performed. Usually, such locks have a well-developed infrastructure (e.g., waiting queue support and interlock detection) and monitoring facilities, since the cost of maintaining all these amenities is still incomparably lower compared to the cost of operations on the data being protected.
  • Short-term locks are captured for a small amount of time (from a few CPU instructions to fractions of a second) and usually refer to data structures in shared memory. PostgreSQL manages such locks completely automatically – you just need to be aware of their existence.
    Short locks are characterized by a minimum of modes (exclusive and shared) and a simple infrastructure. In some cases, even monitoring facilities may not be available.

PostgreSQL uses different types of locks.
Object level locks are long-term, "heavyweight" ones. Relationships and other objects act as resources here. If the word lock is used unspecified, it means a "normal" lock.
Among the long-term blockages, the following stand out line-level locks Their implementation differs from other long locks because of their potentially huge number (imagine updating a million strings in a single transaction). Such locks will be discussed in the next article.
The third article in the series will focus on the remaining object-level locks, and predicate locks (because information about all these locks is stored in RAM in the same way).
Short interlocks include various locks of RAM structures We will look at them in the last article of the series.

Locking objects

So we start with objectlevel locks. By object we mean here in the first place relations (relations), that is, tables, indexes, sequences, materialized representations, but also some other entities. These locks usually protect objects from simultaneous changes or from being used while the object is being changed, but also for other needs.
Vague wording? It is, because the locks in this group are used for many different purposes. The only thing they have in common is the way they are set up.


Object locks are located in the server’s shared memory. Their number is limited by the product of the values of the two parameters : max_locks_per_transaction × max_connections
The lock pool is common to all transactions, which means that one transaction can capture more locks than max_locks_per_transaction : it is only important that the total number of locks in the system does not exceed the set limit. The pool is created at startup, so changing either of these two parameters requires restarting the server.
All locks can be viewed in the pg_locks view.
If a resource is already locked in an incompatible mode, a transaction attempting to grab that resource is queued and waiting for the lock to be released. Waiting transactions do not consume CPU resources: the corresponding serving processes "sleep" and are awakened by the operating system when the resource is released.
Possible situation interlocks or deadlock (deadlock), in which one transaction needs a resource occupied by the second transaction to continue its work, while the second transaction needs a resource occupied by the first (in general, more than two transactions may interlock). In such a case, the wait will continue indefinitely, so PostgreSQL automatically detects such situations and abruptly interrupts one of the transactions so that the others can continue. (We’ll talk more about interlocks in the next article.)

Types of objects

Here is a list of lock types (or object types, if you will) that we will deal with in this and the next articles. The names are given according to the locktype column of the pg_locks view.

  • relation
    Relationship Lockdowns.
  • transactionid and virtualxid
    Transaction number locking (real or virtual). Each transaction itself holds an exclusive lock on its own number, so such locks are convenient to use when you need to wait for another transaction to finish.
  • tuple
    String version locking. Used in some cases to set priority among multiple transactions waiting to lock the same string.

We will talk about the other types of locks until the third article in the series. All of them are captured either in exclusive mode only, or in exclusive and shared mode.

  • extend
    Used when adding pages to a relation file.
  • object
    Locking objects that are not relations (databases, schemas, subscriptions, etc.).
  • page
    Page lock, used infrequently and only by some index types.
  • advisory
    Recommended blocking, set manually by the user.

Relationship locks

So as not to lose context, I will mark on this picture the types of blockages that will be discussed next.
Locks in PostgreSQL: 1.Relationship locks


If not the most important, then certainly the most "extensive" blocking is the relationship blocking. As many as 8 different modes are defined for it. This number is needed so that as many commands relating to one table as possible can be executed at the same time.
There is no point in learning these modes by heart or trying to grasp the meaning of their names; the important thing is to have them in front of you when you need them matrix which shows which interlocks are in conflict with each other. For convenience, it is reproduced here along with examples of commands that require the appropriate lock levels :

lock mode AS RS RE SUE S SRE E AE Example SQL Commands
Access Share X SELECT

Some comments :

  • The first 4 modes allow simultaneous change of data in the table, while the next 4 do not.
  • The first mode (Access Share) is the weakest, it is compatible with any other mode except the last one (Access Exclusive). This last mode is a monopoly mode, it is not compatible with any mode.
  • The ALTER TABLE command has many variants, different ones requiring different lock levels. That’s why this command appears on different lines in the matrix and is marked with an asterisk.

For example

Here is an example. What happens if you run the CREATE INDEX command?
We find in the documentation that this command sets a lock in Share mode. We determine from the matrix that the command is compatible with itself (that is, you can create multiple indexes at the same time) and with the reading commands. Thus SELECT commands will continue to work, but UPDATE, DELETE, INSERT commands will be blocked.
And vice versa – incomplete transactions that change data in the table will block the CREATE INDEX command. That is why there is a variant of command – CREATE INDEX CONCURRENTLY. It takes longer (and may even crash with an error), but it allows simultaneous data changes.
The above can also be seen in practice. For the experiments we will use the familiar from of the first cycle table of "bank" accounts, in which we will store the account number and amount.

=> CREATE TABLE accounts(acc_no integer PRIMARY KEY, amount numeric);=> INSERT INTO accountsVALUES (1, 1000.00), (2, 2000.00), (3, 3000.00);

In the second session we start the transaction. We need the number of the serving process.

| => SELECT pg_backend_pid();
| pg_backend_pid| ----------------| 4746| (1 row)

What locks does the transaction that just started hold? See pg_locks:

=> SELECT locktype, relation::REGCLASS, virtualxid AS virtxid, transactionid AS xid, mode, grantedFROM pg_locks WHERE pid = 4746;
locktype | relation | virtxid | xid | mode | granted------------+----------+---------+-----+---------------+---------virtualxid | | 5/15 | | ExclusiveLock | t(1 row)

As I said before, the transaction always holds an exclusive (ExclusiveLock) lock on its own number, and in this case a virtual number. This process has no other locks.
Now let’s update the table row. How will the situation change?

| => UPDATE accounts SET amount = amount + 100 WHERE acc_no = 1;

=> g
locktype | relation | virtxid | xid | mode | granted---------------+---------------+---------+--------+------------------+---------relation | accounts_pkey | | | RowExclusiveLock | trelation | accounts | | | RowExclusiveLock | tvirtualxid | | 5/15 | | ExclusiveLock | ttransactionid | | | 529404 | ExclusiveLock | t(4 rows)

Now there are locks for the table being changed and the index (created for the primary key) used by the UPDATE command. Both locks are taken in RowExclusiveLock mode. In addition, an exclusive lock has been added for the real transaction number (which appeared as soon as the transaction started to change data).
Now let’s try in another session to create an index on the table.

|| => SELECT pg_backend_pid();
|| pg_backend_pid|| ----------------|| 4782|| (1 row)
|| => CREATE INDEX ON accounts(acc_no);

The command "hangs" waiting for the resource to be released. What kind of lock is it trying to grab? Let’s check :

=> SELECT locktype, relation::REGCLASS, virtualxid AS virtxid, transactionid AS xid, mode, grantedFROM pg_locks WHERE pid = 4782;
locktype | relation | virtxid | xid | mode | granted------------+----------+---------+-----+---------------+---------virtualxid | | 6/15 | | ExclusiveLock | trelation | accounts | | | ShareLock | f(2 rows)

We see that the transaction is trying to get a table lock in ShareLock mode, but cannot (granted = f).
Finding the number of the blocking process, and in general several numbers, is easy with the feature which appeared in 9.6 (before that you had to look through all the contents of pg_locks to draw conclusions):

=> SELECT pg_blocking_pids(4782);
pg_blocking_pids------------------{4746}(1 row)

And then, to make sense of the situation, you can get information about the sessions to which the numbers found belong :

=> SELECT * FROM pg_stat_activityWHERE pid = ANY(pg_blocking_pids(4782)) gx
-[ RECORD 1 ]----+------------------------------------------------------------datid | 16386datname | testpid | 4746usesysid | 16384usename | studentapplication_name | psqlclient_addr |client_hostname |client_port | -1backend_start | 2019-08-07 15:02:53.811842+03xact_start | 2019-08-07 15:02:54.090672+03query_start | 2019-08-07 15:02:54.10621+03state_change | 2019-08-07 15:02:54.106965+03wait_event_type | Clientwait_event | ClientReadstate | idle in transactionbackend_xid | 529404backend_xmin |query | UPDATE accounts SET amount = amount + 100 WHERE acc_no = 1;backend_type | client backend

When the transaction is complete, the locks are released and the index is created.

| => COMMIT;


Get in line!

To get a better idea of what an incompatible lockout results in, let’s see what happens if you run the VACUUM FULL command while the system is running.
Let the SELECT command be executed on our table first. It gets the weakest Access Share lock. To control the lock release time, we execute this command inside the transaction – until the transaction is over, the lock will not be released. In reality, a table can be read (and modified) by several commands, and some of the queries can take quite a long time to execute.

=> BEGIN;=> SELECT * FROM accounts;
acc_no | amount--------+---------2 | 2000.003 | 3000.001 | 1100.00(3 rows)
=> SELECT locktype, mode, granted, pid, pg_blocking_pids(pid) AS wait_forFROM pg_locks WHERE relation = 'accounts'::regclass;
locktype | mode | granted | pid | wait_for----------+-----------------+---------+------+----------relation | AccessShareLock | t | 4710 | {}(1 row)

The administrator then runs the VACUUM FULL command, which requires an Access Exclusive level lock, which is incompatible with anything, even Access Share. (The same lock is required by the LOCK TABLE command.) The transaction is queued up.

| => BEGIN;| => LOCK TABLE accounts; -- similar to VACUUM FULL

=> SELECT locktype, mode, granted, pid, pg_blocking_pids(pid) AS wait_forFROM pg_locks WHERE relation = 'accounts'::regclass;
locktype | mode | granted | pid | wait_for----------+---------------------+---------+------+----------relation | AccessShareLock | t | 4710 | {}relation | AccessExclusiveLock | f | 4746 | {4710}(2 rows)

But the application keeps issuing queries, and now another SELECT command appears in the system. Purely theoretically it could "skip" while VACUUM FULL is waiting, but no – it honestly takes a place in the queue behind VACUUM FULL.

|| => SELECT * FROM accounts;

=> SELECT locktype, mode, granted, pid, pg_blocking_pids(pid) AS wait_forFROM pg_locks WHERE relation = 'accounts'::regclass;
locktype | mode | granted | pid | wait_for----------+---------------------+---------+------+----------relation | AccessShareLock | t | 4710 | {}relation | AccessExclusiveLock | f | 4746 | {4710}relation | AccessShareLock | f | 4782 | {4746}(3 rows)

After the first transaction with the SELECT command finishes and releases the lock, the VACUUM FULL command (which we simulated with the LOCK TABLE command) starts to execute.



=> SELECT locktype, mode, granted, pid, pg_blocking_pids(pid) AS wait_forFROM pg_locks WHERE relation = 'accounts'::regclass;
locktype | mode | granted | pid | wait_for----------+---------------------+---------+------+----------relation | AccessExclusiveLock | t | 4746 | {}relation | AccessShareLock | f | 4782 | {4746}(2 rows)

And only after VACUUM FULL finishes its work and releases the lock, all the commands accumulated in the queue (SELECT in our example) can grab the corresponding locks (Access Share) and be executed.

| => COMMIT;

|| acc_no | amount|| --------+---------|| 2 | 2000.00|| 3 | 3000.00|| 1 | 1100.00|| (3 rows)

Thus, a carelessly executed command can paralyze the system for a period of time much longer than the time it takes to execute the command itself.

Monitoring means

Of course, locks are necessary for proper operation, but they can lead to unwanted waits. Such expectations can be monitored, so that their cause can be understood and, if possible, eliminated (for example, by changing the application’s algorithm).
We have already met one way: when a long lock occurs, we can query the pg_locks view, look at the blocking and blocking transactions (function pg_blocking_pids) and decrypt them with pg_stat_activity.
Another way is to include the parameter log_lock_waits In this case, the server’s message log will contain information if the transaction waited longer than deadlock_timeout (even though the parameter for deadlocks is used, we are talking about normal waits).
Let’s try.

=> ALTER SYSTEM SET log_lock_waits = on;=> SELECT pg_reload_conf();

Parameter value deadlock_timeout defaults to one second :

=> SHOW deadlock_timeout;
deadlock_timeout------------------1s(1 row)

Reproduce the blocking.

=> BEGIN;=> UPDATE accounts SET amount = amount - 100.00 WHERE acc_no = 1;

| => BEGIN;| => UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;

The second UPDATE command is waiting for a lock. Let’s wait a second and finish the first transaction.

=> SELECT pg_sleep(1);=> COMMIT;

Now the second transaction can also complete.

| => COMMIT;

And all the important information went into the log :

postgres$ tail -n 7 /var/log/postgresql/postgresql-11-main.log
2019-08-07 15:26:30.827 MSK [5898] student@test LOG: process 5898 still waiting for ShareLock on transaction 529427 after 1000.186 ms2019-08-07 15:26:30.827 MSK [5898] student@test DETAIL: Process holding the lock: 5862. Wait queue: 5898.2019-08-07 15:26:30.827 MSK [5898] student@test CONTEXT: while updating tuple (0, 4) in relation "accounts"2019-08-07 15:26:30.827 MSK [5898] student@test STATEMENT: UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
2019-08-07 15:26:30.836 MSK [5898] student@test LOG: process 5898 acquired ShareLock on transaction 529427 after 1009.536 ms2019-08-07 15:26:30.836 MSK [5898] student@test CONTEXT: while updating tuple (0, 4) in relation "accounts"2019-08-07 15:26:30.836 MSK [5898] student@test STATEMENT: UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;


You may also like