PostgreSQL中的锁：2.行级锁

object-level locks 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 row version 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 remember, 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 AS
SELECT '(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_lock
FROM 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