and in particular relation-level locks. In this article, we will see how row-level locks are organized in PostgreSQL and how they are used together with object-level locks. We will also talk of wait queues and of those who jumps the queue. ，尤其是关系级锁。 在本文中，我们将了解PostgreSQL中行级锁的组织方式以及它们如何与对象级锁一起使用。 我们还将讨论等待队列和跳过队列的人。

行级锁 (Row-level locks)

组织 (Organization)

Let's recall a few weighty conclusions of the previous article.

让我们回想一下上一篇文章的一些重要结论。

• A lock must be available somewhere in the shared memory of the server.
  服务器共享内存中的某个位置必须有锁。
• The higher granularity of locks, the lower the contention among concurrent processes.
  锁的粒度越高，并发进程之间的争用就越少。
• On the other hand, the higher the granularity, the more of the memory is occupied by locks.
  另一方面，粒度越高，锁占用的内存越多。

There is no doubt that we want a change of one row not block other rows of the same table. But we cannot afford to have its own lock for each row either.

毫无疑问，我们希望更改一行而不阻塞同一表的其他行。 但是我们也不能为每一行都拥有自己的锁。

There are different approaches to solving this problem. Some database management systems apply escalation of locks: if the number of row-level locks gets too high, they are replaced with one, more general lock (for example: a page-level or an entire table-level).

解决此问题有不同的方法。 某些数据库管理系统会应用锁升级：如果行级锁的数量过多，则将它们替换为一个更通用的锁(例如：页面级或整个表级)。

As we will see later, PostgreSQL also applies this technique, but only for predicate locks. The situation with row-level locks is different.

稍后我们将看到，PostgreSQL也应用了该技术，但仅用于谓词锁。 行级锁的情况有所不同。

PostgreSQL stores information that a row is locked only and exclusively in the inside the data page (and not in RAM). It means that it is not a lock in a usual sense, but just some indicator. Actually, the ID of the xmax transaction, along with additional information bits, serves as the indicator; we will look at how this is organized in detail a little later.

PostgreSQL仅在数据页内的 (而不是在RAM中)存储有关行被锁定的信息。 这意味着它不是通常意义上的锁，而只是一些指示器。 实际上， xmax事务的ID以及附加的信息位用作指示符。 稍后我们将详细研究它的组织方式。

A pro is that we can lock as many rows as we want without consuming any resources.

一个优点是，我们可以锁定任意数量的行，而不会消耗任何资源。

But there is also a con: since the information on the lock is not available in RAM, other processes cannot be queued. And monitoring is impossible either (to count the locks, all the table needs to be read).

但是也有一个缺点 ：由于关于锁的信息在RAM中不可用，因此其他进程无法排队。 而且监视也是不可能的(要计算锁，需要读取所有表)。

Let alone monitoring, but something needs to be done for the queue. To this end, «normal» locks have yet to be used. If we need to wait for a row to be released, we actually need to wait until completion of the locking transaction: all locks are released at the transaction commit or roll back. And to this end, we can request a lock on the ID of the locking transaction (to remind you, the transaction itself holds this lock in an exclusive mode). So, the number of locks used is proportional to the number of simultaneously running processes rather than to the number of rows being updated.

更不用说监视了，但是需要对队列做一些事情。 为此，尚未使用“普通”锁。 如果我们需要等待一行被释放，那么实际上我们需要等待直到锁定事务完成：在事务提交或回滚时释放所有锁。 为此，我们可以请求对锁定事务的ID进行锁定(提醒您，事务本身以独占模式持有此锁定)。 因此，使用的锁数与同时运行的进程数成正比，而不与要更新的行数成正比。

独家模式 (Exclusive modes)

There are 4 modes that allow locking a row. Two of them are exclusive locks, which only one transaction can hold at a time.

有4种模式可以锁定一行。 其中两个是互斥锁，一次只能保留一个事务。

• FOR UPDATE mode assumes a total change (or delete) of a row.
  FOR UPDATE模式假定一行的总更改(或删除)。
• FOR NO KEY UPDATE mode assumes a change only to the fields that are not involved in unique indexes (in other words, this change does not affect foreign keys).
  FOR NO KEY UPDATE模式仅假定对唯一索引中不涉及的字段进行了更改(换句话说，此更改不影响外键)。

The UPDATE command itself selects the minimum appropriate locking mode; rows are usually locked in the FOR NO KEY UPDATE mode.

UPDATE命令本身会选择最小的适当锁定模式。 通常在FOR NO KEY UPDATE模式下锁定行。

As you , when a row is updated or deleted, the ID of the current transaction is written to the xmax field of the current up-to-date version. This ID shows that the transaction deleted the tuple. And this very number xmax is used to indicate a lock. Indeed, if xmax in a tuple matches an active (not yet completed) transaction and we want to update this very row, we need to wait until the transaction completes, and no additional indicator is needed.

如您所 ，当更新或删除一行时，当前事务的ID将被写入当前最新版本的xmax字段中。 此ID显示事务删除了元组。 这个非常大的数字xmax用于表示锁定。 确实，如果元组中的xmax与活动的(尚未完成)交易匹配，并且我们要更新这一行，则需要等到交易完成后才需要其他指示符。

Let's take a look. And let's create a table of accounts, same as in the last article.

让我们来看看。 让我们创建一个帐户表，与上一篇文章相同。

=> CREATE TABLE accounts(  acc_no integer PRIMARY KEY,  amount numeric);=> INSERT INTO accounts  VALUES (1, 100.00), (2, 200.00), (3, 300.00);

To look into pages, we need a pretty familiar pageinspect extension.

要浏览页面，我们需要一个非常熟悉的pageinspect扩展。

=> CREATE EXTENSION pageinspect;

For convenience, we will create a view that shows only the information of interest: xmax and some information bits.

为了方便起见，我们将创建一个仅显示感兴趣的信息的视图： xmax和一些信息位。

=> CREATE VIEW accounts_v ASSELECT '(0,'||lp||')' AS ctid,       t_xmax as xmax,       CASE WHEN (t_infomask & 128) > 0   THEN 't' END AS lock_only,       CASE WHEN (t_infomask & 4096) > 0  THEN 't' END AS is_multi,       CASE WHEN (t_infomask2 & 8192) > 0 THEN 't' END AS keys_upd,       CASE WHEN (t_infomask & 16) > 0 THEN 't' END AS keyshr_lock,       CASE WHEN (t_infomask & 16+64) = 16+64 THEN 't' END AS shr_lockFROM heap_page_items(get_raw_page('accounts',0))ORDER BY lp;

So, we start a transaction and update the amount in the first account (the key is unchanged) and the number of the second account (the key is changed):

因此，我们开始交易并更新第一个帐户中的金额(密钥不变)和第二个帐户的编号(密钥已更改)：

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

Let's look into the view:

让我们看一下视图：

=> SELECT * FROM accounts_v LIMIT 2;

ctid  |  xmax  | lock_only | is_multi | keys_upd | keyshr_lock | shr_lock -------+--------+-----------+----------+----------+-------------+---------- (0,1) | 530492 |           |          |          |             |  (0,2) | 530492 |           |          | t        |             | (2 rows)

The keys_updated information bit determines the lock mode.

keys_updated信息位确定锁定模式。

The same xmax field is involved in locking of a row by the SELECT FOR UPDATE command, but in this case, an additional information bit (xmax_lock_only) is set, which tells us that the tuple is only locked, but not deleted and is still live.

SELECT FOR UPDATE命令锁定行时涉及相同的xmax字段，但是在这种情况下，会设置一个附加信息位( xmax_lock_only )，这告诉我们该元组仅被锁定，但未被删除，并且仍然有效。

=> ROLLBACK;=> BEGIN;=> SELECT * FROM accounts WHERE acc_no = 1 FOR NO KEY UPDATE;=> SELECT * FROM accounts WHERE acc_no = 2 FOR UPDATE;

=> SELECT * FROM accounts_v LIMIT 2;

ctid  |  xmax  | lock_only | is_multi | keys_upd | keyshr_lock | shr_lock -------+--------+-----------+----------+----------+-------------+---------- (0,1) | 530493 | t         |          |          |             |  (0,2) | 530493 | t         |          | t        |             | (2 rows)

=> ROLLBACK;

共享模式 (Shared modes)

Two more modes represent shared locks, which several transactions can be holding.

还有两种模式表示共享锁，可以持有几个事务。

• FOR SHARE mode is used when we need to read a row, but no other transaction is permitted to change it.
  当我们需要读取一行但不允许其他事务更改它时，使用FOR SHARE模式。
• FOR KEY SHARE mode permits a change of a row, but only in its non-key fields. In particular, PostgreSQL automatically uses this mode when checking foreign keys.
  FOR KEY SHARE模式允许更改一行，但只能在其非关键字段中进行。 特别是，PostgreSQL在检查外键时会自动使用此模式。

Let's take a look.

让我们来看看。

=> BEGIN;=> SELECT * FROM accounts WHERE acc_no = 1 FOR KEY SHARE;=> SELECT * FROM accounts WHERE acc_no = 2 FOR SHARE;

In the tuples, we see:

在元组中，我们看到：

=> SELECT * FROM accounts_v LIMIT 2;

ctid  |  xmax  | lock_only | is_multi | keys_upd | keyshr_lock | shr_lock -------+--------+-----------+----------+----------+-------------+---------- (0,1) | 530494 | t         |          |          | t           |  (0,2) | 530494 | t         |          |          | t           | t(2 rows)

In both cases, the keyshr_lock bit is set, and the SHARE mode can be recognized by looking into one more information bit.

在两种情况下， keyshr_lock位置1，并且可以通过查看另外一个信息位来识别SHARE模式。

This is what the general matrix of modes compatibility looks like.

这就是模式兼容性的一般矩阵。

From the matrix, it is clear that:

从矩阵中可以明显看出：

• Exclusive modes conflict each other.
  互斥模式相互冲突。
• Shared modes are compatible with one another.
  共享模式彼此兼容。
• The shared mode FOR KEY SHARE is compatible with the exclusive mode FOR NO KEY UPDATE (that is, we can update non-key fields simultaneously and be sure that the key won't change).
  共享模式FOR KEY SHARE与FOR NO KEY独占模式兼容(也就是说，我们可以同时更新非密钥字段，并确保密钥不会更改)。

多重交易 (Multitransactions)

Up to here, we considered the ID of the locking transaction in the xmax field to represent a lock. But shared locks can be held by multiple transactions, while we cannot write several IDs to one xmax field. What shall we do?

到目前为止，我们认为xmax字段中的锁定事务的ID代表锁定。 但是共享锁可以由多个事务持有，而我们不能将多个ID写入一个xmax字段。 我们该怎么办？

So-called multitransactions (MultiXact) are used for shared locks. This is a group of transactions to which a separate ID is assigned. This ID has the same bit depth as a normal transaction ID, but the IDs are assigned independently (that is, a system can have equal IDs of a transaction and multitransaction). To differentiate them, another information bit is used (xmax_is_multi), and detailed information on the participants of this group and locking modes is stored in files of the $PGDATA/pg_multixact/ directory. Naturally, the last used data are stored in buffers of the shared memory of the server for faster access.

所谓的多重事务(MultiXact)用于共享锁。 这是一组分配了单独ID的交易。 该ID具有与普通交易ID相同的位深，但是这些ID是独立分配的(也就是说，系统可以具有相同的交易ID和多交易ID)。 为了区分它们，使用了另一个信息位( xmax_is_multi )，有关该组参与者和锁定模式的详细信息存储在$PGDATA/pg_multixact/目录的文件中。 自然地，最后使用的数据存储在服务器共享内存的缓冲区中，以加快访问速度。

Apart from existing locks, let's add one more exclusive lock, held by another transaction (we can do this since FOR KEY SHARE and FOR NO KEY UPDATE modes are compatible with each other):

除了现有的锁，让我们再添加一个由另一个事务持有的排他锁(我们可以这样做，因为FOR KEY SHARE和FOR NO KEY UPDATE模式彼此兼容)：

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

=> SELECT * FROM accounts_v LIMIT 2;

ctid  |  xmax  | lock_only | is_multi | keys_upd | keyshr_lock | shr_lock -------+--------+-----------+----------+----------+-------------+---------- (0,1) |     61 |           | t        |          |             |  (0,2) | 530494 | t         |          |          | t           | t(2 rows)

In the first line, we see that the normal ID is replaced with the multitransaction ID — the xmax_is_multi bit indicates this.

在第一行中，我们看到正常ID被多xmax_is_multi ID取代了xmax_is_multi位表明了这一点。

To avoid exploring the internals of the multitransaction implementation, we can use one more extension, which allows us to get all the information on all types of locks in a user-friendly format.

为了避免探索多事务实现的内部，我们可以再使用一个扩展，它使我们能够以用户友好的格式获取所有类型的锁的所有信息。

=> CREATE EXTENSION pgrowlocks;=> SELECT * FROM pgrowlocks('accounts') \gx

-[ RECORD 1 ]-----------------------------locked_row | (0,1)locker     | 61multi      | txids       | {530494,530495}modes      | {"Key Share","No Key Update"}pids       | {5892,5928}-[ RECORD 2 ]-----------------------------locked_row | (0,2)locker     | 530494multi      | fxids       | {530494}modes      | {"For Share"}pids       | {5892}

=> COMMIT;

|  => ROLLBACK;

冻结设置 (Setup of freezing)

Since separate IDs are assigned to multitransactions, which are written to the xmax field of tuples, and because of the limitation on the bit depth of the IDs, the same issue can arise for them as for normal IDs.

由于将单独的ID分配给多xmax ，并将其写入元组的xmax字段，并且由于对ID的位深度的限制，它们可能会出现与普通ID相同的问题。

Therefore, for multitransaction IDs, it is needed to perform something similar to freezing, that is, replace old IDs with new ones (or with a normal transaction ID if at the time of freezing, the lock is held by only one transaction).

因此，对于多事务ID，需要执行类似于冻结的操作，即用新的ID替换旧的ID(如果冻结时，锁仅由一个事务持有，则用普通的事务ID替换)。

Note that freezing of normal transaction IDs is performed only for the xmin field (since if a tuple has a non-empty xmax field, this is either a dead tuple, which will be vacuumed away, or the xmax transaction is rolled back and its ID is of no interest to us). But for multitransactions, this involves the xmax field of the live tuple, which can remain live, but be continuously locked by different transactions in the shared mode.

请注意，仅对xmin字段执行常规交易ID的冻结(因为如果元组具有非空的xmax字段，则这可能是无效的元组，将被清除，或者回退xmax交易及其ID对我们来说没有兴趣)。 但是对于多xmax ，这涉及活动元组的xmax字段，该字段可以保持活动状态，但是在共享模式下会被不同事务连续锁定。

The parameters that are responsible for freezing multitransactions are similar to those of normal transactions: vacuum_multixact_freeze_min_age, vacuum_multixact_freeze_table_age, autovacuum_multixact_freeze_max_age.

负责冻结多重交易的参数与常规事务的参数相似： vacuum_multixact_freeze_min_age ， vacuum_multixact_freeze_table_age ， autovacuum_multixact_freeze_max_age 。

队列的结尾在哪里？ (Where is the end of the queue?)

Gradually approaching the sweet. Let's see what picture of locks arises when several transactions are going to update the same row.

逐渐接近甜蜜。 让我们看看当几个事务将更新同一行时出现的锁图。

We'll start with creating a view over pg_locks. First, we'll make the output more compact, second, we'll consider only interesting locks (actually, we drop locks on the following: virtual transaction IDs, the index on the accounts table, pg_locks, and the view itself — in short, everything that is irrelevant and only distracts attention).

我们将从在pg_locks上创建一个视图pg_locks 。 首先，我们将使输出更紧凑，其次，我们将仅考虑有趣的锁(实际上，我们在以下各项上放下锁：虚拟事务ID， accounts表上的索引， pg_locks和视图本身) ，所有无关紧要的事物只会分散注意力)。

=> CREATE VIEW locks_v ASSELECT pid,       locktype,       CASE locktype         WHEN 'relation' THEN relation::regclass::text         WHEN 'transactionid' THEN transactionid::text         WHEN 'tuple' THEN relation::regclass::text||':'||tuple::text       END AS lockid,       mode,       grantedFROM pg_locksWHERE locktype in ('relation','transactionid','tuple')AND (locktype != 'relation' OR relation = 'accounts'::regclass);

Now let's start the first transaction and update a row.

现在让我们开始第一个事务并更新一行。

=> BEGIN;=> SELECT txid_current(), pg_backend_pid();

txid_current | pg_backend_pid --------------+----------------       530497 |           5892(1 row)

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

UPDATE 1

What's going on with locks?

锁怎么了？

=> SELECT * FROM locks_v WHERE pid = 5892;

pid  |   locktype    |  lockid  |       mode       | granted ------+---------------+----------+------------------+--------- 5892 | relation      | accounts | RowExclusiveLock | t 5892 | transactionid | 530497   | ExclusiveLock    | t(2 rows)

The transaction holds the locks on its own ID and on the table. Everything is as expected so far.

事务在其自己的ID和表上持有锁。 到目前为止，一切都如预期。

Starting the second transaction and trying to update the same row.

启动第二个事务并尝试更新同一行。

|  => BEGIN;|  => SELECT txid_current(), pg_backend_pid();

|   txid_current | pg_backend_pid |  --------------+----------------|         530498 |           5928|  (1 row)

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

What's going on with locks held by the second transaction?

第二笔交易持有的锁怎么了？

=> SELECT * FROM locks_v WHERE pid = 5928;

pid  |   locktype    |   lockid   |       mode       | granted ------+---------------+------------+------------------+--------- 5928 | relation      | accounts   | RowExclusiveLock | t 5928 | transactionid | 530498     | ExclusiveLock    | t 5928 | transactionid | 530497     | ShareLock        | f 5928 | tuple         | accounts:1 | ExclusiveLock    | t(4 rows)

It appears more interesting here. In addition to locks on the transaction's own ID and the table, we can see two more locks. The second transaction discovered that the row was locked by the first one and «hanged» waiting for its ID (granted = f). But where does the tuple lock (locktype = tuple) come from and what for?

在这里看起来更有趣。 除了对事务自己的ID和表的锁定以外，我们还可以看到另外两个锁定。 第二个事务发现该行被第一个锁定并被“挂起”，等待其ID(被granted = f )。 但是，元组锁( locktype = tuple )来自何处？

tuple type, which is visible in

tuple类型的常规锁，在

pg_locks. But the second one is an indicator in the data page:

pg_locks可见。 但是第二个是数据页中的指示符：

xmax and information bits.

xmax和信息位。

When a transaction is going to change a row, it performs the following sequence of steps:

当事务要更改行时，它将执行以下步骤序列：

1. Acquires an exclusive lock on the tuple to be updated.
   在要更新的元组上获取排他锁。

2. If xmax and information bits show that the row is locked, requests a lock on the xmax transaction ID.

   如果xmax和信息位显示该行已被锁定，则请求对xmax事务ID进行锁定。

3. Writes its own xmax and sets the required information bits.

   写入自己的xmax并设置所需的信息位。

4. Releases the tuple lock.
   释放元组锁。

When the first transaction was updating the row, it also acquired a tuple lock (step 1), but immediately released it (step 4).

当第一个事务更新该行时，它还获取了一个元组锁(步骤1)，但立即释放了它(步骤4)。

When the second transaction arrived, it acquired a tuple lock (step 1), but had to request a lock on the ID of the first transaction (step 2) and hanged ding this.

当第二笔交易到达时，它获得了一个元组锁(步骤1)，但是必须请求对第一笔交易的ID进行锁定(步骤2)，并对此进行了挂起。

What will happen if the third similar transaction appears? It will try to acquire a tuple lock (step 1) and will hang as early as at this step. Let's check.

如果第三次类似的交易出现，将会发生什么？ 它将尝试获取元组锁(步骤1)，并将最早在此步骤挂起。 让我们检查。

||     => BEGIN;||     => SELECT txid_current(), pg_backend_pid();

||      txid_current | pg_backend_pid ||     --------------+----------------||            530499 |           5964||     (1 row)

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

=> SELECT * FROM locks_v WHERE pid = 5964;

pid  |   locktype    |   lockid   |       mode       | granted ------+---------------+------------+------------------+--------- 5964 | relation      | accounts   | RowExclusiveLock | t 5964 | tuple         | accounts:1 | ExclusiveLock    | f 5964 | transactionid | 530499     | ExclusiveLock    | t(3 rows)

Transactions four, five and others that try to update the same row will be no different from transaction three: all of them will be «hanging» on the same tuple lock.

事务四，事务五和其他尝试更新同一行的事务与事务三没有什么不同：它们都将“挂”在同一元组锁上。

To top it off, let's add one more transaction.

最重要的是，让我们再添加一个事务。

|||        => BEGIN;|||        => SELECT txid_current(), pg_backend_pid();

|||         txid_current | pg_backend_pid |||        --------------+----------------|||               530500 |           6000|||        (1 row)

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

=> SELECT * FROM locks_v WHERE pid = 6000;

pid  |   locktype    |   lockid   |       mode       | granted ------+---------------+------------+------------------+--------- 6000 | relation      | accounts   | RowExclusiveLock | t 6000 | transactionid | 530500     | ExclusiveLock    | t 6000 | tuple         | accounts:1 | ExclusiveLock    | f(3 rows)

We can see an integral picture of current waits in the pg_stat_activity view by adding information on the locking processes:

通过添加有关锁定过程的信息，我们可以在pg_stat_activity视图中看到当前等待的完整情况：

=> SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid) FROM pg_stat_activity WHERE backend_type = 'client backend';

pid  | wait_event_type |  wait_event   | pg_blocking_pids ------+-----------------+---------------+------------------ 5892 |                 |               | {} 5928 | Lock            | transactionid | {5892} 5964 | Lock            | tuple         | {5928} 6000 | Lock            | tuple         | {5928,5964}(4 rows)

Sort of a «queue» arises here, with the first one (who holds the tuple lock) and the rest, queued up behind the first one.

这里出现了一种“队列”，第一个队列(持有元组锁)，其余队列在第一个队列之后。

What is this sophisticated structure needed for? Assume we did not have the tuple lock. Then the second, third (and next) transactions would wait for the lock on the ID of the first transaction. At the time of completion of the first transaction, the locked resource disappears, and what happens next depends on which of the waiting processes will be first waked up by the OS and therefore, will manage to lock the row. The rest of the processes will also be waked up, but will have to wait in a queue again, but behind another process now.

这种复杂的结构需要做什么？ 假设我们没有元组锁。 然后，第二，第三(和下一个)事务将等待第一个事务的ID的锁定。 在第一个事务完成时，锁定的资源将消失，接下来发生的情况取决于操作系统将首先唤醒哪个等待的进程，因此将设法锁定该行。 其余的进程也将被唤醒，但必须再次在队列中等待，但现在要在另一个进程之后。

This is fraught with an issue: one of the transactions may wait its turn infinitely long if due to an unfortunate coincidence, other transactions persistently «overtake» it. This situation is called «lock starvation».

这充满了一个问题：如果由于不幸的巧合，其中一个事务可能无限期地等待轮次，而其他事务却持久地“超越”它。 这种情况称为“锁定饥饿”。

In our example, we confront something similar, but a little better though: the transaction that arrived second is guaranteed to be the next to get access to the resource. But what happens to the next transactions (third and so on)?

在我们的示例中，我们遇到了类似的事情，但是有一点更好：到达第二位的事务可以保证是下一个获得该资源的访问。 但是接下来的交易(第三次等等)会怎样？

If the first transaction completes with a roll back, everything will be fine: the transactions that arrived will go in the same order as they are queued.

如果第一个事务通过回滚完成，那么一切都会好起来的：到达的事务将按照排队的顺序进行。

But — bad luck — if the first transaction completes with a commit, not only the transaction ID disappears, but also the tuple! I mean that the tuple is still in place, but it is no longer live, and quite a different, latest, version (of the same row) will need to be updated. The resource which the transactions were queueing up for no longer exists, and they start a race for the new resource.

但是，不幸的是，如果第一个事务以提交完成，则不仅事务ID消失，元组也消失！ 我的意思是，该元组仍然存在，但不再可用，并且需要更新(同一行)完全不同的最新版本。 事务排队的资源已不存在，它们开始争夺新资源。

Let the first transaction complete with a commit.

通过提交使第一个事务完成。

=> COMMIT;

The second transaction will be waked up and will perform steps 3 and 4.

第二笔交易将被唤醒，并将执行步骤3和4。

|  UPDATE 1

=> SELECT * FROM locks_v WHERE pid = 5928;

pid  |   locktype    |  lockid  |       mode       | granted ------+---------------+----------+------------------+--------- 5928 | relation      | accounts | RowExclusiveLock | t 5928 | transactionid | 530498   | ExclusiveLock    | t(2 rows)

And what happens to the third transaction? It skips step 1 (since the resource is no longer available) and gets stuck on step 2:

第三笔交易会怎样？ 它跳过步骤1(因为资源不再可用)，并停留在步骤2：

=> SELECT * FROM locks_v WHERE pid = 5964;

pid  |   locktype    |  lockid  |       mode       | granted ------+---------------+----------+------------------+--------- 5964 | relation      | accounts | RowExclusiveLock | t 5964 | transactionid | 530498   | ShareLock        | f 5964 | transactionid | 530499   | ExclusiveLock    | t(3 rows)

The same happens to the fourth transaction:

第四笔交易也是如此：

=> SELECT * FROM locks_v WHERE pid = 6000;

pid  |   locktype    |  lockid  |       mode       | granted ------+---------------+----------+------------------+--------- 6000 | relation      | accounts | RowExclusiveLock | t 6000 | transactionid | 530498   | ShareLock        | f 6000 | transactionid | 530500   | ExclusiveLock    | t(3 rows)

That is, the third and fourth transactions are waiting for completion of the second one. The queue turned into a

pumpkin

crowd.

也就是说，第三和第四笔交易正在等待第二笔交易的完成。 队列变成了

南瓜

人群。

Completing all the started transactions.

完成所有已开始的事务。

|  => COMMIT;

||     UPDATE 1

||     => COMMIT;

|||        UPDATE 1

|||        => COMMIT;

. 找到锁定表行的更多详细信息。

轮到你了 (It's not your turn)

So, the idea behind two-level locking is to reduce the probability of lock starvation for an «unlucky» transaction. Nevertheless, as we've already seen, this situation is not unlikely. And if an application uses shared locks, everything can be even more sad.

因此，两级锁定背后的想法是减少“不幸”交易的锁定不足的可能性。 但是，正如我们已经看到的那样，这种情况并非不可能。 而且，如果应用程序使用共享锁，那么一切都会变得更加令人难过。

Let the first transaction lock a row in a shared mode.

让第一个事务在共享模式下锁定一行。

=> BEGIN;=> SELECT txid_current(), pg_backend_pid();

txid_current | pg_backend_pid --------------+----------------       530501 |           5892(1 row)

=> SELECT * FROM accounts WHERE acc_no = 1 FOR SHARE;

acc_no | amount --------+--------      1 | 100.00(1 row)

The second transaction tries to update the same row, but cannot: SHARE and NO KEY UPDATE modes are incompatible.

第二个事务尝试更新同一行，但不能：SHARE和NO KEY UPDATE模式不兼容。

|  => BEGIN;|  => SELECT txid_current(), pg_backend_pid();

|   txid_current | pg_backend_pid |  --------------+----------------|         530502 |           5928|  (1 row)

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

The second transaction waits for completion of the first one and holds the tuple lock — everything is the same as last time, so far.

第二个事务等待第一个事务完成并持有元组锁-到目前为止，所有操作都与上次相同。

=> SELECT * FROM locks_v WHERE pid = 5928;

pid  |   locktype    |   lockid    |       mode       | granted ------+---------------+-------------+------------------+--------- 5928 | relation      | accounts    | RowExclusiveLock | t 5928 | tuple         | accounts:10 | ExclusiveLock    | t 5928 | transactionid | 530501      | ShareLock        | f 5928 | transactionid | 530502      | ExclusiveLock    | t(4 rows)

And here the third transaction shows up, which wants a shared lock. The trouble is that it does not attempt to acquire a tuple lock (since it does not want to change the row), but simply jumps the queue since the needed lock is compatible with the lock held by the first transaction.

这里显示了第三个事务，它需要一个共享锁。 问题在于它不尝试获取元组锁(因为它不想更改行)，而只是跳过队列，因为所需的锁与第一个事务持有的锁兼容。

||     BEGIN||     => SELECT txid_current(), pg_backend_pid();

||      txid_current | pg_backend_pid ||     --------------+----------------||            530503 |           5964||     (1 row)

||     => SELECT * FROM accounts WHERE acc_no = 1 FOR SHARE;

||      acc_no | amount ||     --------+--------||           1 | 100.00||     (1 row)

And now two transactions lock the row:

现在，有两个事务锁定了该行：

=> SELECT * FROM pgrowlocks('accounts') \gx

-[ RECORD 1 ]---------------locked_row | (0,10)locker     | 62multi      | txids       | {530501,530503}modes      | {Share,Share}pids       | {5892,5964}

But what will happen now when the first transaction completes? The second transaction will be waked up, but will see that the row lock is still there and will «queue», this time, behind the third transaction:

但是，当第一笔交易完成后会发生什么呢？ 第二个事务将被唤醒，但是将看到行锁仍然存在，并且这次将“排队”在第三个事务之后：

=> COMMIT;=> SELECT * FROM locks_v WHERE pid = 5928;

pid  |   locktype    |   lockid    |       mode       | granted ------+---------------+-------------+------------------+--------- 5928 | relation      | accounts    | RowExclusiveLock | t 5928 | tuple         | accounts:10 | ExclusiveLock    | t 5928 | transactionid | 530503      | ShareLock        | f 5928 | transactionid | 530502      | ExclusiveLock    | t(4 rows)

And it's only when the third transaction completes (and if no other shared locks occur during that time), the second one will be able to perform the update.

而且只有当第三个事务完成时(并且如果在此期间没有其他共享锁发生)，第二个事务就可以执行更新。

||     => COMMIT;

|  UPDATE 1

|  => ROLLBACK;

It seems like it's time to make some practical conclusions.

似乎该做出一些实际结论了。

• Simultaneous updates of the same table row in multiple concurrent processes is hardly the best idea.
  在多个并发进程中同时更新同一表行并不是最好的主意。
• If you opt for using SHARE locks in an application, do this cautiously.
  如果您选择在应用程序中使用SHARE锁，请谨慎操作。
• A check of foreign keys can hardly hinder since foreign keys do not usually change and KEY SHARE and NO KEY UPDATE modes are compatible.
  外键的检查几乎不会受到阻碍，因为外键通常不会更改，并且KEY SHARE和NO KEY UPDATE模式兼容。

恳请您不要排队 (You are kindly asked not to queue)

Usually SQL commands wait until resources they need get free. But sometimes we want to refrain from execution of the command if the lock cannot be acquired immediately. To this end, such commands as SELECT, LOCK and ALTER permit the NOWAIT clause.

通常，SQL命令会等到它们需要的资源释放出来。 但是有时候，如果无法立即获取锁，我们希望避免执行该命令。 为此，诸如SELECT，LOCK和ALTER之类的命令允许使用NOWAIT子句。

For example:

例如：

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

|  => SELECT * FROM accounts FOR UPDATE NOWAIT;

|  ERROR:  could not obtain lock on row in relation "accounts"

The command terminates with an error if the resource appears to be in use. Application code can catch and handle an error like this.

如果资源似乎正在使用中，该命令将以错误终止。 应用程序代码可以捕获并处理这样的错误。

We cannot provide the NOWAIT clause for the UPDATE и DELETE commands, but we can first execute SELECT FOR UPDATE NOWAIT and then in the case of success, update or delete the row.

我们无法为UPDATE和DELETE命令提供NOWAIT子句，但是我们可以先执行SELECT FOR UPDATE NOWAIT，然后在成功的情况下更新或删除该行。

One more option to aviod waiting is to use the SELECT FOR command with the SKIP LOCKED clause. This command will skip locked rows, but process those that are free.

避免等待的另一种选择是将SELECT FOR命令与SKIP LOCKED子句一起使用。 该命令将跳过锁定的行，但处理那些空闲的行。

|  => BEGIN;|  => DECLARE c CURSOR FOR|       SELECT * FROM accounts ORDER BY acc_no FOR UPDATE SKIP LOCKED;|  => FETCH c;

|   acc_no | amount |  --------+--------|        2 | 200.00|  (1 row)

In the above example, the first, locked, row was skipped and we immediately accessed (and locked) the second one.

在上面的示例中，第一行(锁定)被跳过，我们立即访问(并锁定)第二行。

In practice, this allows us to set up multithreaded processing of queues. It makes no sense to think of other use cases for this command — if you want to use it, you most likely missed a simpler solution.

实际上，这允许我们设置队列的多线程处理。 考虑此命令的其他用例是没有意义的-如果要使用它，您很可能错过了一个更简单的解决方案。

=> ROLLBACK;

|  => ROLLBACK;

. 。

翻译自: