PostgreSQL中的锁：2.行级锁

飞象数据 2020-12-17

1892

上一次，我们讨论了对象级锁，尤其是关系级锁。在本文中，我们将了解PostgreSQL中行级锁的组织方式以及它们如何与对象级锁一起使用。我们还将讨论等待和跳过队列。

行级锁

组织

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

服务器共享内存中的某个位置必须有锁。
锁的粒度越高，并发进程之间的争用就越少。
另一方面，粒度越高，锁占用的内存越多。

毫无疑问，我们希望更改一行而不阻塞同一表的其他行。同样每一行都拥有自己的锁是我们所负担不起的消耗。

解决这个问题有不同的方法。一些数据库管理系统应用锁升级：如果行级锁的数量太多，它们将被一个更通用的锁替换（例如：页级或整个表级）。

PostgreSQL也应用了该技术，但仅用于谓词锁。行级锁的情况有所不同。

PostgreSQL仅在数据页内的行版本中（而不是在运行内存中）存储有关行被锁定的信息。这意味着它不是通常意义上的锁，而只是一些标志。实际上，xmax
交易ID 以及附加的信息位可作为标志。稍后我们将详细研究它的组织方式。

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

但是也有一个缺点：由于关于锁的信息在运行内存中不可用，因此其他进程无法排队。而监控也是不可能的（要计算锁，需要读取所有表）。

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

独占模式

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

FOR UPDATE模式假定行的全部更改（或删除）。
FOR NO KEY UPDATE模式假定只更改不涉及唯一索引的字段（换句话说，此更改不影响外键）。

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

当更新或删除一行时，当前事务ID将被写入xmax
（当前最新版本）字段中。此ID显示事务删除了元组。xmax
这个数字用来表示锁。如果xmax
在一个元组中匹配一个活动（尚未完成）的事务，并且我们想更新这一行，则需要等到事务完成为止，并且不需要其他这个标志。

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

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

要观察页面，我们需要pageinspect
扩展。

=> CREATE EXTENSION pageinspect;
复制

为了方便起见，我们将创建一个仅显示感兴趣信息的视图：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;
复制

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

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

让我们看一下视图：

 => 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)
复制

keys_updated
决定锁定模式。

包含相同的xmax
字段被SELECT FOR UPDATE命令锁定行时，设置了一个附加信息位（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;
复制

共享模式

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

当我们需要读取一行但不允许其他事务更改它时，使用FOR SHARE模式。
FOR KEY SHARE模式允许更改一行，但只能在其非关键字段中进行。PostgreSQL在检查外键时会自动使用此模式。

让我们来看看。

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

在元组中，我们看到：

 => 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)
复制

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

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

Mode	FOR KEY SHARE	FOR SHARE	FOR NO KEY UPDATE	FOR UPDATE
FOR KEY SHARE				Х
FOR SHARE			Х	Х
FOR NO KEY UPDATE		Х	Х	Х
FOR UPDATE	Х	Х	Х	Х

从矩阵中可以看出：

独占模式相互冲突。
共享模式彼此兼容。
共享模式 FOR KEY SHARE 与独占模式 FOR NO KEY UPDATE 兼容（也就是说，我们可以同时更新非密钥字段，并确保密钥不会更改）。

多重事务

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

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

除了现有的锁，让我们再添加一个由另一个事务持有的独占锁（我们可以这样做，因为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)
复制

在第一行中，我们看到事务ID被多事务ID取代了-is_multi
指示了这一点。

我们可以再使用一个扩展，它使我们能够以用户友好的格式获取所有类型的锁的所有信息。

=> CREATE EXTENSION pgrowlocks;
=> SELECT * FROM pgrowlocks('accounts') \gx
-[ RECORD 1 ]-----------------------------
locked_row | (0,1)
locker     | 61
multi      | t
xids       | {530494,530495}
modes      | {"Key Share","No Key Update"}
pids       | {5892,5928}
-[ RECORD 2 ]-----------------------------
locked_row | (0,2)
locker     | 530494
multi      | f
xids       | {530494}
modes      | {"For Share"}
pids       | {5892}
复制

=> COMMIT;
复制

|  => ROLLBACK;
复制

冻结操作

由于单独的id被分配给多个事务，这些事务被写入元组的xmax
字段，并且由于ID的位深度的限制，它们可能会出现与普通id相同的xid wraparound问题。

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

注意，普通事务ID的冻结只针对xmin
字段执行（因为如果一个元组有一个非空的xmax
字段，那么这要么是一个死元组，它将被清空，要么xmax
事务被回滚，而它的ID对我们来说不感兴趣）。但是对于多事务，这涉及到活动元组的xmax
字段，它可以保持活动状态，但在共享模式下被不同的事务连续锁定。

负责冻结多重事务的参数与普通事务的参数相似：vacuum_multixact_freeze_min_age
，vacuum_multixact_freeze_table_age
，autovacuum_multixact_freeze_max_age
。

队列的尽头在哪里？

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

我们将从pg_locks
上创建视图。首先，我们将使输出更紧凑，其次，我们将仅考虑有趣的锁（实际上，我们在以下各项上放下锁：虚拟事务ID，accounts
表上的索引，pg_locks
以及视图本身）-简而言之，所有无关紧要的内容只会分散注意力）。

=> CREATE VIEW locks_v AS
SELECT 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,
       granted
FROM pg_locks
WHERE locktype in ('relation','transactionid','tuple')
AND (locktype != 'relation' OR relation = 'accounts'::regclass);
复制

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

=> 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
复制

锁怎么了？

=> SELECT * FROM locks_v WHERE pid = 5892;
 pid  |   locktype    |  lockid  |       mode       | granted 
------+---------------+----------+------------------+---------
 5892 | relation      | accounts | RowExclusiveLock | t
 5892 | transactionid | 530497   | ExclusiveLock    | t
(2 rows)
复制

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

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

|  => 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;
复制

第二个事务持有的锁怎么了？

 => 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)
复制

除了事务本身的ID和表上的锁之外，我们还可以看到另外两个锁。第二个事务发现该行被第一个锁定并被“挂起”，等待其ID（granted = f
）。但是元组锁（locktype = tuple
）从何而来？

避免将元组锁与行锁混淆。第一个是该元组类型的常规锁，在pg_locks
中可见。但是第二个是数据页中的指示符：xmax
和信息位。

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

1、在要更新的元组上获取独占锁。

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

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

4、释放元组锁。

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

当第二个事务到达时，它获得了一个元组锁（步骤1），但是必须请求对第一个事务的ID进行锁定（步骤2），所以挂了起来。

如果第三次类似的交易出现，将会发生什么？它将尝试获取元组锁（步骤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)
复制

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

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

|||        => 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)
复制

通过添加有关锁定过程的信息，我们可以在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)
复制

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

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

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

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

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

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

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

=> COMMIT;
复制

第二个事务将被唤醒，并将执行步骤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)
复制

第三个事务会怎样？它跳过步骤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)
复制

第四个事务也是如此：

=> 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)
复制

也就是说，第三和第四个事务正在等待第二个事务的完成。

完成所有已开始的事务。

|  => COMMIT;
||     UPDATE 1
||     => COMMIT;

|||        UPDATE 1
|||        => COMMIT;
复制

您可以在README.tuplock中找到锁定表行的更多详细信息。

轮到你了

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

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

=> 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)
复制

第二个事务尝试更新同一行，但不能：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;
复制

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

=> 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)
复制

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

||     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)
复制

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

=> SELECT * FROM pgrowlocks('accounts') \gx
-[ RECORD 1 ]---------------
locked_row | (0,10)
locker     | 62
multi      | t
xids       | {530501,530503}
modes      | {Share,Share}
pids       | {5892,5964}
复制

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

=> 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)
复制

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

||     => COMMIT;

|  UPDATE 1
|  => ROLLBACK;
复制

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

在多个并发进程中同时更新同一表行并不是最好的主意。
如果您选择在应用程序中使用SHARE锁，请谨慎操作。
外键的检查几乎不会受到阻碍，因为外键通常不会更改，并且KEY SHARE和NO KEY UPDATE模式兼容。

请不要排队

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

例如:

=> 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"
复制

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

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

避免等待的另一种选择是将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)
复制

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

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

=> ROLLBACK;
|  => ROLLBACK;
复制

postgresql

文章转载自飞象数据，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。