postgresql 锁_PostgreSQL中的锁：4.内存中的锁

postgresql 锁

relation-level locks, 关系级锁 ， row-level locks, 行级锁 ， locks on other objects (including predicate locks) and interrelationships of different types of locks. 其他对象上的锁 (包括谓词锁)以及不同类型锁的相互关系。

The following discussion of locks in RAM finishes this series of articles. We will consider spinlocks, lightweight locks and buffer pins, as well as events monitoring tools and sampling.

以下有关RAM中锁的讨论结束了本系列文章。 我们将考虑自旋锁，轻型锁和缓冲销以及事件监视工具和采样。

自旋锁 (Spinlocks)

Unlike normal, «heavy-weight», locks, to protect structures in the shared memory, more lightweight and less expensive (in overhead costs) locks are used.

与普通的“超重”锁不同，它用于保护共享内存中的结构，而使用的是重量更轻，成本更低(在开销成本方面)的锁。

The simplest of them are spinlocks. They are meant to be acquired for very short time intervals (a few processor instructions), and they protect separate memory areas from simultaneous changes.

其中最简单的是自旋锁 。 可以在很短的时间间隔(一些处理器指令)中获取它们，并且它们可以保护单独的存储区免于同时更改。

Spinlocks are implemented based on atomic processor instructions, such as compare-and-swap. They support the only, exclusive, mode. If a lock is acquired, a waiting process performs busy waiting — the command is repeated («spins» in a loop, hence the name) until it is a success. This makes sense since spinlocks are used in the cases where the probability of a conflict is estimated as very low.

自旋锁是根据原子处理器指令(例如比较和交换)实现的。 它们支持唯一的独占模式。 如果获得了锁，则等待过程将执行繁忙的等待-重复该命令(循环中称为“旋转”，因此称为名称)，直到成功为止。 这很有意义，因为在估计冲突可能性非常低的情况下使用了自旋锁。

Spinlocks do not enable detection of deadlocks (PostgreSQL developers take care of this) and provide no monitoring tools. Essentially, the only thing we can do with spinlocks is to be aware of their existence.

自旋锁不能启用死锁检测(PostgreSQL开发人员会注意这一点)，并且不提供监视工具。 从本质上讲，我们对自旋锁唯一能做的就是知道它们的存在。

轻巧的锁 (Lightweight locks)

So-called lightweight locks (lwlocks) come next.

接下来是所谓的轻量级锁 (lwlocks)。

They get acquired for a short time that is needed to work with the data structure (such as a hash table or a list of pointers). As a rule, a lightweight lock is held briefly, but sometimes lightweight locks protect input/output operations, so in general, the time might also be considerable.

它们会在很短的时间内被获取，以使用数据结构(例如哈希表或指针列表)。 通常，轻量级锁是短暂持有的，但有时轻量级锁可以保护输入/输出操作，因此，通常情况下，时间可能也很长。

Two modes are supported: exclusive (for data modifications) and shared (only for reading). There is actually no wait queue: if a few processes wait for release of a lock, one of them will get the access in a more or less random fashion. In high-concurrency and large-load systems, this can be troublesome (for example, see this discussion).

支持两种模式：独占(用于数据修改)和共享(仅用于读取)。 实际上没有等待队列：如果有几个进程等待释放锁，那么其中一个进程或多或少会获得随机访问。 在高并发和大负载的系统中，这可能很麻烦(例如，请参阅此讨论 )。

There are no techniques to check for deadlocks, so this is left to the responsibility of developers of the core. However, lightweight locks have monitoring tools, so, unlike spinlocks, we can «see» them (I will show a bit later how to do this).

没有检查死锁的技术，因此，这由核心开发人员负责。 但是，轻量级锁具有监视工具，因此，与自旋锁不同，我们可以“查看”它们(稍后我将展示如何执行此操作)。

缓冲销 (Buffer pin)

Yet another type of locks, which we already touched upon in the article on the buffer cache, is a buffer pin.

我们已经在缓冲区高速缓存的文章中介绍过的另一种类型的锁是缓冲区pin 。

Different operations, including data modifications, can be performed with a pinned buffer, but under the condition that the changes are not visible to other processes due to multiversion concurrency control. That is, we can, for instance, add a new row to the page, but cannot replace a page in the buffer with another one.

可以使用固定缓冲区执行不同的操作(包括数据修改)，但条件是由于多版本并发控制，更改对其他进程不可见。 也就是说，例如，我们可以在页面上添加新行，但是不能用另一页面替换缓冲区中的页面。

If a buffer pin hinders a process, as a rule, the latter just skips this buffer and chooses a different one. But in some cases, where exactly this buffer is needed, the process queues and «falls asleep»; the system will wake it up when the buffer is unpinned.

通常，如果缓冲销阻碍了处理，则后者仅跳过该缓冲并选择其他缓冲。 但是在某些情况下，正是需要此缓冲区的地方，进程排队并“入睡”； 取消固定缓冲区时，系统将唤醒它。

Monitoring can access waits related to buffer pins.

监视可以访问与缓冲区引脚相关的等待。

示例：缓冲区缓存 (Example: buffer cache)

Now, in order to get some (although incomplete!) insight into how and where locks are used, let's consider the buffer cache as an example.

现在，为了对使用锁的方式和位置有一些了解(尽管不完整！)，让我们以缓冲区缓存为例。

To access a hash table that contains links to buffers, a process must acquire a lightweight buffer mapping lock in a shared mode, and if the table needs to be updated, in an exclusive mode. To reduce the granularity, this lock is structured as a tranche that consists of 128 separate locks, each protecting its own part of the hash table.

若要访问包含缓冲区链接的哈希表，进程必须在共享模式下获取轻量级缓冲区映射锁 ，并且如果需要更新表，则必须以独占模式获取。 为了减小粒度，该锁被构造为，它由128个单独的锁，每个保护其自身的哈希表的一部分的付款 。

The process gets access to the buffer header using a spinlock. Certain operations (such as a counter increment) can also be performed without explicit locking, by means of atomic processor instructions.

该过程使用自旋锁访问缓冲区标头。 某些操作(例如计数器增量)也可以通过原子处理器指令在没有显式锁定的情况下执行。

In order to read the contents of a buffer, a buffer content lock is needed. It is usually acquired only for the time needed to read pointers to tuples, and after that, a buffer pin provides sufficient protection. To change the contents of a buffer, this lock must be acquired in an exclusive mode.

为了读取缓冲区的内容 ，需要缓冲区内容锁定 。 通常仅在读取指向元组的指针所需的时间内获取它，然后，缓冲引脚提供足够的保护。 要更改缓冲区的内容，必须以独占模式获取此锁。

When a buffer is read from disk (or written to disk), an IO in progress lock is also acquired, which indicates to other processes that the page is being read (or written) — they can queue up if they need to do something with this page.

当从磁盘读取缓冲区(或将其写入磁盘)时，也会获取一个IO进行中锁定，这向其他进程表明该页面正在被读取(或写入)-如果需要执行某些操作，它们可以排队。这一页。

Pointers to free buffers and to the next victim are protected by one buffer strategy lock spinlock.

指向空闲缓冲区和下一个受害者的指针受到一个缓冲区策略锁自旋锁的保护。

示例：WAL缓冲区 (Example: WAL buffers)

WAL buffers provide another example.

WAL缓冲区提供了另一个示例。

The WAL cache also uses a hash table that contains mapping of pages to buffers. Unlike for the buffer cache, this hash table is protected by the only lightweight WALBufMappingLock lock since the size of the WAL cache is smaller (usually 1/32 of the buffer cache) and access to the buffers is more regular.

WAL缓存还使用一个哈希表，该哈希表包含页面到缓冲区的映射。 与缓冲区高速缓存不同，此哈希表受唯一的轻量级WALBufMappingLock锁保护，因为WAL高速缓存的大小较小(通常为缓冲区高速缓存的1/32)，并且对缓冲区的访问更为规则。

Writes of pages to disk are protected by the WALWriteLock lock, so that only one process can perform this operation at a time.

将页面WALWriteLock磁盘受WALWriteLock锁保护，因此一次只能有一个进程可以执行此操作。

To create a WAL record, the process must first allocate space in a WAL page. To do this, it acquires an insert position lock spinlock. When the space is allocated, the process copies the contents of its record to the space allocated. Copying can be performed by several processes simultaneously, therefore, the record is protected by a tranche of 8 lightweight wal insert lock locks (the process must acquire any of them).

要创建WAL记录，该过程必须首先在WAL页面中分配空间。 为此，它获取一个插入位置锁定自旋锁。 分配空间后，该过程会将其记录的内容复制到分配的空间。 复制可以由多个进程同时执行，因此，该记录受8个轻量沃尔玛插入锁的保护(该进程必须获取其中的任何一个)。

The figure does not show all WAL-related locks, but this and previous examples must give an idea of how locks in RAM are used.

该图未显示所有与WAL相关的锁，但是此示例和前面的示例必须使您了解如何使用RAM中的锁。

等待事件监控 (Wait events monitoring)

Starting with PostgreSQL 9.6, the pg_stat_activity view has built-in events monitoring tools. When a process (system or backend) cannot do its job and waits for something, we can see this wait in the view: the wait_event_type column shows the wait type and the wait_event column shows the name of a specific wait.

从PostgreSQL 9.6开始， pg_stat_activity视图具有内置的事件监视工具。 当某个进程(系统或后端)无法执行其任务并等待某事时，我们可以在视图中看到此等待： wait_event_type列显示等待类型，而wait_event列显示特定等待的名称。

Note that the view shows only waits that are properly handled in the source code. If the view does not show a wait, in general, this does not mean with 100 percent probability that the process really waits for nothing.

请注意，该视图仅显示源代码中已正确处理的等待。 通常，如果视图未显示等待，则这并不意味着该进程真正没有任何等待的可能性为100％。

Unfortunately, the only available information on waits is the current information. No accumulated statistics are maintained. The only way to get the picture of waits in time is sampling the state of the view at a certain interval. No built-in tools are provided to this end, but we can use extensions, such as pg_wait_sampling.

不幸的是，有关等待的唯一可用信息是当前信息。 没有维护累积的统计信息。 及时获取等待图像的唯一方法是按一定间隔对视图状态进行采样 。 为此没有提供内置工具，但是我们可以使用扩展名，例如pg_wait_sampling 。

We need to take into account the probabilistic nature of sampling. To get a more or less credible picture, the number of measurements must be pretty large. Low-frequency sampling may fail to provide a credible picture, while use of higher frequencies will increase overhead costs. For the same reason, sampling is useless to analyze short-lived sessions.

我们需要考虑抽样的概率性质。 为了获得或多或少可信的图像，测量次数必须非常大。 低频采样可能无法提供可信的图像，而使用较高的频率会增加开销成本。 出于同样的原因，采样对于分析短期会话毫无用处。

All the waits can be divided into several types.

所有等待可以分为几种类型。

Waits for the locks discussed make up a large category:

等待讨论的锁组成一个大类：

• Waits for locks on objects (the value of Lock in the wait_event_type column).

  等待对象上的Lock ( wait_event_type列中Lock的值)。

• Waits for lightweight locks (LWLock).

  等待轻量级锁( LWLock )。

• Waits for a buffer pin (BufferPin).

  等待一个缓冲引脚( BufferPin )。

But processes can also await other events:

但是进程也可以等待其他事件：

• Waits for input/output (IO) occur when a process needs to read or write data.

  当进程需要读取或写入数据时，等待输入/输出( IO )。

• A process can wait for the data needed from a client (Client) or another process (IPC).

  一个进程可以等待来自客户端( Client )或另一个进程( IPC )的所需数据。

• Extensions can register their specific waits (Extension).

  扩展可以注册其特定的等待时间( Extension )。

Sometimes situations arise when a process just doesn't do any productive work. This category includes:

有时，当流程根本不执行任何生产性工作时，就会出现这种情况。 此类别包括：

• Waits of a background process in its main loop (Activity).

  在其主循环( Activity )中等待后台进程。

• Waits for a timer (Timeout).

  等待一个计时器( Timeout )。

Usually, waits like these are treated as «normal» and do not indicate any problems.

通常，将此类等待视为“正常”，并不表示任何问题。

The wait type is followed by the name of a specific wait. For the complete table, see the documentation.

等待类型后跟特定等待的名称。 有关完整表，请参阅文档 。

If the name of a wait is not defined, the process is not in the waiting state. We need to treat this point in time as unaccounted for since we are actually unaware of what exactly is happening at that moment.

如果未定义等待的名称，则该过程不处于等待状态。 我们需要将此时间点视为无法解释的时间，因为我们实际上并不知道那一刻到底发生了什么。

However, let's watch this for ourselves.

但是，让我们自己看看。

=> SELECT pid, backend_type, wait_event_type, wait_event
FROM pg_stat_activity;

pid  |         backend_type         | wait_event_type |     wait_event      
-------+------------------------------+-----------------+---------------------
 28739 | logical replication launcher | Activity        | LogicalLauncherMain
 28736 | autovacuum launcher          | Activity        | AutoVacuumMain
 28963 | client backend               |                 | 
 28734 | background writer            | Activity        | BgWriterMain
 28733 | checkpointer                 | Activity        | CheckpointerMain
 28735 | walwriter                    | Activity        | WalWriterMain
(6 rows)

It's clear that all background backend processes are idle. Empty values of wait_event_type and wait_event tell us that the process is waiting for nothing; in our example, the backend process is busy executing the query.

很明显，所有后台后端进程都是空闲的。 wait_event_type和wait_event空值告诉我们该进程什么也没有等待。 在我们的示例中，后端进程正在忙于执行查询。

采样 (Sampling)

To get a more or less complete picture of waits by means of sampling, we will use the pg_wait_sampling extension. We need to build it from source codes, but I will omit this part. Then we add the library name to the shared_preload_libraries parameter and restart the server.

为了通过采样了解更多或更少的等待，我们将使用pg_wait_sampling扩展。 我们需要从源代码构建它，但是我将省略这一部分。 然后，将库名称添加到shared_preload_libraries参数，然后重新启动服务器。

=> ALTER SYSTEM SET shared_preload_libraries = 'pg_wait_sampling';

student$ sudo pg_ctlcluster 11 main restart

Now we install the extension in the database.

现在，我们将扩展安装在数据库中。

=> CREATE EXTENSION pg_wait_sampling;

The extension allows us to look through the history of waits, which is stored in a circular buffer. But what's mostly interesting to us is the waits profile, that is, the statistics accumulated since the server start.

该扩展使我们可以查看等待的历史记录，该历史记录存储在循环缓冲区中。 但是对我们而言，最有趣的是等待配置文件，即自服务器启动以来累积的统计信息。

This is roughly what we will see a few seconds later:

大概几秒钟后，我们将看到以下内容：

=> SELECT * FROM pg_wait_sampling_profile;

pid  | event_type |        event        | queryid | count 
-------+------------+---------------------+---------+-------
 29074 | Activity   | LogicalLauncherMain |       0 |   220
 29070 | Activity   | WalWriterMain       |       0 |   220
 29071 | Activity   | AutoVacuumMain      |       0 |   219
 29069 | Activity   | BgWriterMain        |       0 |   220
 29111 | Client     | ClientRead          |       0 |     3
 29068 | Activity   | CheckpointerMain    |       0 |   220
(6 rows)

Because nothing happened since the server start, most waits refer to the types Activity (backend processes wait until there is some work for them) and Client (psql waits until a user sends a request).

因为自从服务器启动以来什么都没有发生，所以大多数等待都引用Activity类型(后端进程等待，直到有一些工作psql )和Client ( psql等待用户发送请求)。

With the default settings (of the pg_wait_sampling.profile_period parameter), the sampling period equals 10 milliseconds, which means that values are saved 100 times a second. Therefore, to evaluate the duration of waits in seconds, we need to divide the value of count by 100.

使用默认设置( pg_wait_sampling.profile_period参数的设置)，采样周期等于10毫秒，这意味着每秒将值保存100次。 因此，要评估等待时间(以秒为单位)，我们需要将count的值除以100。

To figure out which process the waits pertain to, let's add the pg_stat_activity view to the query:

为了弄清楚等待与哪个进程有关，我们将pg_stat_activity视图添加到查询中：

=> SELECT p.pid, a.backend_type, a.application_name AS app,
          p.event_type, p.event, p.count
FROM pg_wait_sampling_profile p
  LEFT JOIN pg_stat_activity a ON p.pid = a.pid
ORDER BY p.pid, p.count DESC;

pid  |         backend_type         | app  | event_type |        event         | count 
-------+------------------------------+------+------------+----------------------+-------
 29068 | checkpointer                 |      | Activity   | CheckpointerMain     |   222
 29069 | background writer            |      | Activity   | BgWriterMain         |   222
 29070 | walwriter                    |      | Activity   | WalWriterMain        |   222
 29071 | autovacuum launcher          |      | Activity   | AutoVacuumMain       |   221
 29074 | logical replication launcher |      | Activity   | LogicalLauncherMain  |   222
 29111 | client backend               | psql | Client     | ClientRead           |     4
 29111 | client backend               | psql | IPC        | MessageQueueInternal |     1
(7 rows)

Let's produce some workload using pgbench and see how the picture changes.

让我们使用pgbench产生一些工作量，看看图片是如何变化的。

student$ pgbench -i test

We reset the accumulated profile to zero and run the test for 30 seconds in a separate process.

我们将累积的配置文件重置为零，并在一个单独的过程中运行测试30秒。

=> SELECT pg_wait_sampling_reset_profile();

student$ pgbench -T 30 test

We need to execute the query while the pgbench process is not finished yet:

我们需要在pgbench进程尚未完成时执行查询：

=> SELECT p.pid, a.backend_type, a.application_name AS app,
          p.event_type, p.event, p.count
FROM pg_wait_sampling_profile p
  LEFT JOIN pg_stat_activity a ON p.pid = a.pid
WHERE a.application_name = 'pgbench'
ORDER BY p.pid, p.count DESC;

pid  |  backend_type  |   app   | event_type |   event    | count 
-------+----------------+---------+------------+------------+-------
 29148 | client backend | pgbench | IO         | WALWrite   |     8
 29148 | client backend | pgbench | Client     | ClientRead |     1
(2 rows)

The waits of the pgbench process will certainly differ slightly depending on a particular system. In our situation, a wait for WAL writing (IO/WALWrite) is highly likely to be presented, however, most of the time the process was doing something presumably productive rather than being idle.

pgbench进程的等待时间肯定会有所不同，具体取决于特定的系统。 在我们的情况下，很可能会出现等待WAL写入( IO / WALWrite )的情况，但是，在大多数情况下，该过程正在做一些可能有成效的事情，而不是处于空闲状态。

轻巧的锁 (Lightweight locks)

We always need to keep in mind that if a wait is missing when sampling, this does not mean that there was really no wait. If the wait was shorter than the sampling period (a hundredth of a second in our example), it could just fail to get into the sample.

我们始终需要牢记，如果采样时缺少等待，这并不意味着实际上没有等待。 如果等待时间短于采样时间(在我们的示例中为百分之一秒)，则可能无法进入样本。

That's why lightweight locks did not occur in the profile, but they will if the data is collected for a long time. To be able to see them for sure, we can intentionally slow down the file system, for example, by using the slowfs project, built on top of the FUSE file system.

这就是为什么概要文件中没有轻量级锁定的原因，但是如果长时间收集数据，轻型锁定就会发生。 为了确保能够看到它们，我们可以有目的地放慢文件系统的速度，例如，使用在FUSE文件系统之上构建的slowfs项目。

This is what we can see on the same test if any input/output operation takes 1/10 of a second.

如果任何输入/输出操作花费1/10秒，这就是我们在同一测试上可以看到的。

=> SELECT pg_wait_sampling_reset_profile();

student$ pgbench -T 30 test

=> SELECT p.pid, a.backend_type, a.application_name AS app,
          p.event_type, p.event, p.count
FROM pg_wait_sampling_profile p
  LEFT JOIN pg_stat_activity a ON p.pid = a.pid
WHERE a.application_name = 'pgbench'
ORDER BY p.pid, p.count DESC;

pid  |  backend_type  |   app   | event_type |     event      | count 
-------+----------------+---------+------------+----------------+-------
 29240 | client backend | pgbench | IO         | WALWrite       |  1445
 29240 | client backend | pgbench | LWLock     | WALWriteLock   |   803
 29240 | client backend | pgbench | IO         | DataFileExtend |    20
(3 rows)

Now the major wait of the pgbench process relates to input/output, more exactly, to WAL writes, which synchronously occur for every commit. Because (as shown in one of the above examples) a WAL write is protected by a lightweight WALWriteLock lock, this lock is also present in the profile — and it's just what we wanted to look at.

现在， pgbench进程的主要等待与输入/输出有关，更确切地说，与WAL写入有关，WAL写入对于每次提交都同步发生。 因为(如以上示例之一所示)WAL写操作受到轻量级WALWriteLock锁的保护，所以此锁也存在于配置文件中-这正是我们要查看的内容。

缓冲销 (Buffer pin)

To see a buffer pin, let's make use of the fact that open cursors hold the pin to faster read the next row.

要查看缓冲引脚，让我们利用以下事实：打开的游标会抓住该引脚，以便更快地读取下一行。

Let's start a transaction, open a cursor and select one row.

让我们开始一个事务，打开一个游标并选择一行。

=> BEGIN;
=> DECLARE c CURSOR FOR SELECT * FROM pgbench_history;
=> FETCH c;

tid | bid |  aid  | delta |           mtime            | filler 
-----+-----+-------+-------+----------------------------+--------
   9 |   1 | 35092 |   477 | 2019-09-04 16:16:18.596564 | 
(1 row)

Let's check that the buffer is pinned (pinning_backends):

让我们检查一下缓冲区是否已固定( pinning_backends )：

=> SELECT * FROM pg_buffercache
WHERE relfilenode = pg_relation_filenode('pgbench_history')
AND relforknumber = 0 \gx

-[ RECORD 1 ]----+------
bufferid         | 190
relfilenode      | 47050
reltablespace    | 1663
reldatabase      | 16386
relforknumber    | 0
relblocknumber   | 0
isdirty          | t
usagecount       | 1
pinning_backends | 1     <-- buffer is pinned 1 time

Now let's vacuum the table:

现在让我们清理桌子：

|  => SELECT pg_backend_pid();

|   pg_backend_pid 
|  ----------------
|            29367
|  (1 row)

|  => VACUUM VERBOSE pgbench_history;

|  INFO:  vacuuming "public.pgbench_history"
|  INFO:  "pgbench_history": found 0 removable, 0 nonremovable row versions in 1 out of 1 pages
|  DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 732651
|  There were 0 unused item pointers.

|  Skipped 1 page due to buffer pins, 0 frozen pages.

|  0 pages are entirely empty.
|  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
|  VACUUM

As we can see, the page was skipped (Skipped 1 page due to buffer pins). Indeed, VACUUM cannot process it because physical deletion of tuples from a page in a pinned buffer is forbidden. But vacuuming will not wait either, and the page will be processed next time.

如我们所见，页面被跳过了( Skipped 1 page due to buffer pins )。 实际上，VACUUM无法处理它，因为禁止从固定缓冲区中的页面物理删除元组。 但是清除操作也不会等待，下一次将处理该页面。

And now let's perform vacuuming with freezing:

现在让我们在冻结的情况下执行清理 ：

|  => VACUUM FREEZE VERBOSE pgbench_history;

If freezing is explicitly requested, none of the pages tracked in the all-frozen bit can be skipped; otherwise, it is impossible to reduce the maximum age of non-frozen transactions in pg_class.relfrozenxid. So, vacuuming hangs until the cursor is closed.

如果明确请求冻结，则不能跳过全冻结位中跟踪的页面； 否则，不可能在pg_class.relfrozenxid减少非冻结交易的最长pg_class.relfrozenxid 。 因此，吸尘将一直挂起，直到关闭游标。

=> SELECT age(relfrozenxid) FROM pg_class WHERE oid = 'pgbench_history'::regclass;

age 
-----
  27
(1 row)

=> COMMIT; -- cursor closes automatically

|  INFO:  aggressively vacuuming "public.pgbench_history"
|  INFO:  "pgbench_history": found 0 removable, 26 nonremovable row versions in 1 out of 1 pages
|  DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 732651
|  There were 0 unused item pointers.

|  Skipped 0 pages due to buffer pins, 0 frozen pages.

|  0 pages are entirely empty.
|  CPU: user: 0.00 s, system: 0.00 s, elapsed: 3.01 s.
|  VACUUM

=> SELECT age(relfrozenxid) FROM pg_class WHERE oid = 'pgbench_history'::regclass;

age 
-----
   0
(1 row)

And let's look into the waits profile of the second psql session, where the VACUUM commands were executed:

让我们看一下第二个psql会话的waits配置文件，在该配置文件中执行了VACUUM命令：

=> SELECT p.pid, a.backend_type, a.application_name AS app,
          p.event_type, p.event, p.count
FROM pg_wait_sampling_profile p
  LEFT JOIN pg_stat_activity a ON p.pid = a.pid
WHERE p.pid = 29367
ORDER BY p.pid, p.count DESC;

pid  |  backend_type  | app  | event_type |   event    | count 
-------+----------------+------+------------+------------+-------
 29367 | client backend | psql | BufferPin  | BufferPin  |   294
 29367 | client backend | psql | Client     | ClientRead |    10
(2 rows)

The BufferPin wait tells us that VACUUM was waiting for the buffer to get free.

BufferPin等待告诉我们VACUUM正在等待缓冲区释放。

And this is where we will consider discussing locks as finished. Thank you all for attentive reading and comments!

这是我们将讨论完成锁的地方。 谢谢大家的细心阅读和评论！

翻译自: https://habr.com/en/company/postgrespro/blog/507036/

postgresql 锁