PostgreSQL 并发控制之显示锁定

PostgreSQL 提供了各种锁模式，用于控制对表中数据的并发访问。在 MVCC（多版本并发控制）不能提供所需行为的情况下，可以使用这些模式进行应用程序控制的锁定。

大多数 PostgreSQL 命令会自动获取适当模式的锁，以确保在执行命令时不会以不兼容的方式删除或修改引用的表。比如，truncate 无法安全地与同一表的其他操作并发执行，因此获取表的 access exclusive 锁来强制执行。

要查看数据库服务器中当前未完成锁的列表，使用 pg_locks 系统视图。

一、表级锁

以下列表显示了可用的锁模式及其在 PostgreSQL 中自动使用的上下文。也可以使用 lock 命令显示地获取这些锁。

注意：所有这些锁都是表级别的锁，即使名称中包含“行”，这些锁模式的名称是历史遗留问题。这些名称反映了每种锁模式的典型用法，但它们的语义都是相同的。不同锁模式之间的唯一区别是他们之间的冲突关系。两个事务不能再同一时间对同一表持有冲突模式的锁。（但是，一个事务永远不会与自身冲突。例如，它可能先获取 ACCESS EXCLUSIVE 锁，然后再获取同一表的 ACCESS SHARE 锁）。非冲突模锁可以被多个事务同时持有。注意，有些锁是自冲突的（例如，一个 ACCESS EXCLUSIVE 锁不能同时被多个事务持有），而另一些则不是自冲突的（例如，多个事务可以同时持有 ACCESS SHARE 锁）。

一旦获取锁，通常会保持到事务结束。但是，如果在建立保存点之后获取锁，那么如果回滚到该保存点，锁会立即释放。这与 ROLLBACK 取消保存点以来所有命令效果的原则一致。对于在 PL/pgSQL 异常块内获取的锁也是如此：从块内发生错误逃逸会释放在块内获取的锁。

下表展示了不同锁之间的冲突关系：

Requested Lock Mode	ACCESS SHARE	ROW SHARE	ROW EXCL.	SHARE UPDATE EXCL.	SHARE	SHARE ROW EXCL.	EXCL.	ACCESS EXCL.
ACCESS SHARE								X
ROW SHARE							X	X
ROW EXCL.					X	X	X	X
SHARE UPDATE EXCL.				X	X	X	X	X
SHARE			X	X		X	X	X
SHARE ROW EXCL.			X	X	X	X	X	X
EXCL.		X	X	X	X	X	X	X
ACCESS EXCL.	X	X	X	X	X	X	X	X

只与 ACCESS EXCLUSIVE 锁冲突。

select 命令在需要引用的表上获取此模式的锁。通常，任何读取表但是不修改表的查询都将获取这种模式的锁。

与 EXCLUSIVE 和 access exclusive 模式的锁冲突。

select 命令对所有指定了 for update、for no key update、for share 或 for key share 选项的表上获取此模式的锁。此外，还会对没有明确指定 for ... 锁选项的其他与你用表获取 ACCESS SHARE 锁。

3、ROW EXCLUSIVE（RowExclusiveLock）

与 SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 模式的锁冲突。

UPDATE、DELETE、INSERT 和 MERGE 命令在目标表上获取此模式的锁。此外还会对其他引用表获取 ACCESS SHARE 锁。一般来说，任何修改表中数据的命令都会获取这种模式的锁。

与 SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 模式的锁冲突。该模式保护表免受并发的架构更改和 VACUUM 操作的影响。

由 VACUUM（非 FULL 模式）、ANALYZE、CREATE INDEX CONCURRENTLY、CREATE STATISTICS、COMMENT ON、REINDEX CONCURRENTLY 以及某些 ALTER INDEX 和 ALTER TABLE 变体获取。

与 ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。该模式保护表免受并发数据更改的影响。

由 CREATE INDEX（非 CONCURRENTLY 模式）获取。

与 ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。

该模式保护表免受并发数据更改的影响，并且是自排斥的，因此一次只能有一个会话持有此锁。

由 CREATE TRIGGER 和某些形式的 ALTER TABLE 获取。

7、EXCLUSIVE（ExclusiveLock）

与 ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁模式冲突。

该模式只允许并发的 ACCESS SHARE 锁，即只有来自表的读取操作可以与持有此锁模式的事务并行进行。

由 REFRESH MATERIALIZED VIEW CONCURRENTLY 获取。

8、ACCESS EXCLUSIVE（AccessExclusiveLock）

与所有模式的锁 ACCESS SHARE、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 冲突。

该模式确保持有者是唯一以任何方式访问该表的事务。

由 DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL 和 REFRESH MATERIALIZED VIEW（不带 CONCURRENTLY）命令获取。

许多形式的 ALTER INDEX 和 ALTER TABLE 也会在此级别获取锁。这也是未明确指定模式的 LOCK TABLE 语句的默认锁模式。

注意：只有 ACCESS EXCLUSIVE 锁会阻止 select 语句。

二、行级锁

除了表级锁之外，还有行级锁。一个事务可以在同一行上持有冲突锁，即使在不同的子事务中。但两个事务永远不能再同一行上持有冲突的锁。

行级锁不会影响数据查询，它们只会阻止对同一行进行写入和锁定操作。行级锁在事务结束或在保存点回滚期间释放，就像表级锁一样。

PostgreSQL 在内存中不会记录修改行的任何信息，因此一次锁定的行数没有限制。然而，锁定行可能会导致磁盘写入，例如 SELECT FOR UPDATE 会修改选定的行来标记它们为锁定状态，因此会导致磁盘写入操作。

下表为行级锁冲突关系：

Requested Lock Mode	FOR KEY SHARE	FOR SHARE	FOR NO KEY UPDATE	FOR UPDATE
FOR KEY SHARE				X
FOR SHARE			X	X
FOR NO KEY UPDATE		X	X	X
FOR UPDATE	X	X	X	X

1、FOR UPDATE

FOR UPDATE 使得被 SELECT 语句检索到的行被锁定，就像它们将被更新一样。

这防止了它们在当前事务结束之前被其他事务锁定、修改或删除。也就是说，其他试图对这些行执行 UPDATE、DELETE、SELECT FOR UPDATE、SELECT FOR NO KEY UPDATE、SELECT FOR SHARE 或 SELECT FOR KEY SHARE 的事务将被阻塞，直到当前事务结束。

反过来，SELECT FOR UPDATE 将等待在同一行上运行了任何这些命令的并发事务，然后锁定并返回更新后的行（如果行已被删除，则不返回任何行）。然而，在 REPEATABLE READ 或 SERIALIZABLE 事务中，如果要锁定的行自事务开始以来已更改，则会引发错误。

FOR UPDATE 锁还会被任何对行执行的 DELETE 所获取，修改特定列的 UPDATE 也会获取该锁。目前，适用于 UPDATE 情况的列是那些具有可在外键中使用的唯一索引的列（因此不考虑部分索引和表达式索引），但这在将来可能会发生变化。

2、FOR NO KEY UPDATE

该锁的行为类似于 FOR UPDATE，但获取的锁更弱。这种锁不会阻塞试图对相同行获取锁的 SELECT FOR KEY SHARE 命令。

此锁模式也可由任何未获取 FOR UPDATE 锁的 UPDATE 获取。

该锁的行为类似于 FOR NO KEY UPDATE，但它获取的是共享锁而不是排他锁。共享锁会阻止其他事务对这些行执行 UPDATE、DELETE、SELECT FOR UPDATE 或 SELECT FOR NO KEY UPDATE，但不会阻止它们执行 SELECT FOR SHARE 或 SELECT FOR KEY SHARE。

该锁的行为类似于 FOR SHARE，但是获取的锁级别较低：SELECT FOR UPDATE 被阻塞，但 SELECT FOR NO KEY UPDATE 不会被阻塞。该锁会阻止其他事务执行 DELETE 或任何修改键值的 UPDATE，但不阻止其他 UPDATE，也不阻止 SELECT FOR NO KEY UPDATE、SELECT FOR SHARE 或 SELECT FOR KEY SHARE。

三、页级锁

除了表级别和行级别锁定之外，页面级别的共享/排他锁用于控制对共享缓冲池中表页面的读写访问。这些锁在获取或更新行后立即释放。通常应用程序开发人员不需要关注页面级别的锁定。

四、死锁

显式锁的使用可能增加死锁的可能性，即两个（或多个）事务各自持有对方想要的锁的情况。

例如，如果事务1在表A上获取了排他锁，然后尝试在表B上获取排他锁，而事务2已经在表B上获取了排他锁，并且现在希望在表A上获取排他锁，那么两者都无法继续进行。

PostgreSQL 会自动检测死锁情况，并通过中止其中一个事务来解决死锁，从而允许其他事务完成。哪个事务将被中止很难预测，因此不应依赖于此。

在两个并发事务修改表时，死锁也可能由行级锁引起（因此，即使没有使用显式锁，也可能发生死锁）。

第一个事务如下，将在指定行上获取行级锁。

1	UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;

第二个事务如下，第一条 update 将在指定行上获取行级锁，成功地更新了该行。然而，第二条 update 语句发现它尝试更新的行已经被锁定，因此它会等待获取锁的事务完成。第二个事务正在等待第一个事务完成后才能继续执行。

1 2	UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222; UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;

之后，再在事务一中执行：

1	UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;

事务一视图在指定的行上获取行级锁，但是无法成功，因为事务二已经获取了 22222 这一行的锁。因此，事务一等待事务二完成。

此时，事务一被事务二阻塞，而事务二也被事务一阻塞，形成死锁。PostgreSQL 会检测到这种情况并中止其中一个事务。

最好的防御死锁的方法通常是通过确保所有使用数据库的应用程序以一致的顺序获取多个对象上的锁来避免。

在上面的例子中，如果两个事务更新行的顺序相同，就不会发生死锁。此外，应确保事务中对对象获取的第一个锁是该对象所需的最严格模式。如果无法提前验证这一点，那么可以通过在事务由于死锁而中止时重试来动态处理死锁。

只要没有检测到死锁情况，寻求表级或行级锁的事务将无限期地等待冲突锁被释放。因此，对于应用程序来说，长时间保持事务打开是一个不好的做法（例如，在等待用户输入时）。

五、咨询锁

PostgreSQL 提供了一种创建具有应用程序定义含义的锁的方式。这种锁被称为咨询锁，系统不会强制使用它们，正确使用它们又应用程序决定。咨询锁对于不适用 MVCC 模型的锁定策略非常有用。例如，咨询锁的一个常见用途是模拟所谓的“平面文件”数据管理系统中典型的悲观锁定策略。虽然可以使用存储在表中的标志达到相同的目的，但咨询锁更快，避免了表膨胀，并且在会话结束时由服务器自动清理。

在 PostgreSQL 中有两种获取咨询锁的途径：会话级和事务级。

在回话级别获取后，将保持咨询锁定，直到明确释放或会话结束。与标准所请求不同，会话级咨询锁请求不遵循事务语义：在事务中获得的锁即使在事务回滚后仍然保持，同样即使调用事务之后失败，解锁也是有效的。

一个进程可以多次获取同一个锁；对与每个完整的锁请求，在实际释放锁之前必须有相应的解锁请求。另一方面，事务级锁请求更像常规锁请求：它们会在事务结束时自动释放，并且没有显示的解锁操作。对于短期使用的咨询锁，这种行为通常比会话级行为更为方便。

对同一咨询锁标识符的会话级和事务级锁请求将以预期的方式相互阻塞。会话级和事务级对同一咨询锁标识符的锁请求会以预期的方式互相阻塞。如果一个会话已经持有给定的咨询锁，它的其他请求总是会成功，即使其他会话正在等待该锁；无论现有持有的或者新请求的锁是会话级还是事务级，这一声明都成立。

与 PostgreSQL 中的所有锁一样，可以在 pg_locks 系统视图中找到任何会话持有的咨询锁的完整列表。

咨询锁和常规锁都存储在共享内存池中，其大小由配置变量 max_locks_per_transaction 和 max_connections 定义。必须注意不要耗尽这部分内存，否则服务器将无法授予任何锁。这对服务器可以授予的咨询锁数量设定了一个上限，通常在数万到数十万之间，具体取决于服务器的配置。

在某些情况下，使用咨询锁定方法时，特别是在涉及显式排序和LIMIT子句的查询中，必须注意控制所获得的锁，因为 SQL 表达式的评估顺序会影响锁的获取。例如：

SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345; -- ok
SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; -- danger!
SELECT pg_advisory_lock(q.id) FROM
(
  SELECT id FROM foo WHERE id > 12345 LIMIT 100
) q; -- ok

在上述查询中，第二个查询是危险的，因为不能保证在锁定函数执行之前应用 LIMIT。这可能导致应用程序预期之外的一些锁被获取，从而无法释放这些锁（直到会话结束）。从应用程序的角度来看，这些锁将是不确定的，尽管它们仍然可以在 pg_locks 中查看。

OB links

OB tags

#PostgreSQL

一、表级锁

1、ACCESS SHARE（AccessShareLock）

2、ROW SHARE（RowShareLock）

3、ROW EXCLUSIVE（RowExclusiveLock）

4、SHARE UPDATE EXCLUSIVE（ShareUpdateExclusiveLock）

5、SHARE（ShareLock）

6、SHARE ROW EXCLUSIVE（ShareRowExclusiveLock）