PostgreSQL 锁机制浅析

锁是用来使对共享资源的并发访问有序化的。

准备工作

创建测试数据

CREATE TABLE users (id int, username text);
INSERT INTO users (id, username) VALUES (1, 'igor'), (2, 'bob'), (3, 'john'), (4, 'susan');
SELECT * FROM users;

为了便于观察，启动两个命令行数据库连接，并设置提示符分别为 bob 和 alice。

testdb=# \set PROMPT1 '(bob) # '
(bob) #

bob 用来创建数据库事务、锁。

testdb=# \set PROMPT1 '(alice) # '
(alice) #

alice 用来观察状态。

了解 pg_locks 视图

pg_locks 视图可以查看当前活跃的事务进程持有的锁的状态。

(alice) # \d pg_locks;
                   View "pg_catalog.pg_locks"
       Column       |   Type   | Collation | Nullable | Default
--------------------+----------+-----------+----------+---------
 locktype           | text     |           |          |
 database           | oid      |           |          |
 relation           | oid      |           |          |
 page               | integer  |           |          |
 tuple              | smallint |           |          |
 virtualxid         | text     |           |          |
 transactionid      | xid      |           |          |
 classid            | oid      |           |          |
 objid              | oid      |           |          |
 objsubid           | smallint |           |          |
 virtualtransaction | text     |           |          |
 pid                | integer  |           |          |
 mode               | text     |           |          |
 granted            | boolean  |           |          |
 fastpath           | boolean  |           |          |

其中 mode 属性标识锁的模式，granted 属性如果为 true 表示当前事务已经获取到了锁，如果是 false 表示还在等待锁。

查看数据库中处于开启状态的锁：

(alice) # SELECT locktype, relation, mode, granted, pid FROM pg_locks;
  locktype  | relation |      mode       | granted | pid
------------+----------+-----------------+---------+-----
 relation   |    11577 | AccessShareLock | t       |  70
 virtualxid |          | ExclusiveLock   | t       |  70
(2 rows)

用 ::regclass 将 relation 变成名称：

(alice) # SELECT locktype, relation::regclass, mode, granted, pid FROM pg_locks;
  locktype  | relation |      mode       | granted | pid
------------+----------+-----------------+---------+-----
 relation   | pg_locks | AccessShareLock | t       |  70
 virtualxid |          | ExclusiveLock   | t       |  70
(2 rows)

再排除掉当前用户 session 创建的锁：

(alice) # SELECT locktype, relation::regclass, mode, granted, pid FROM pg_locks WHERE pid != pg_backend_pid();
 locktype | relation | mode | granted | pid
----------+----------+------+---------+-----
(0 rows)

表级锁

加锁方式：SQL 命令一般会自动获取表级锁，或者由 LOCK TABLE tablename (IN ... MODE) 命令显示加锁；
不管名字里面有没有 row，下面列出的都是表级锁；
重点是锁之间的冲突，两个事务不能在同一张表上同时分别持有两个冲突的锁，没有冲突的锁可以被多个事务同时持有；

ACCESS SHARE

语句：

SELECT ，一般是只读数据而不修改的命令获取这种锁

冲突：

ACCESS EXCLUSIVE

示例：

Bob：

(bob) #
(bob) # begin;
BEGIN
(bob) # select * from users;
 id | username
----+----------
  1 | igor
  2 | bob
  3 | john
  4 | susan
(4 rows)

Alice：

(alice) # SELECT locktype, relation::regclass, mode, granted, pid FROM pg_locks WHERE pid != pg_backend_pid();
  locktype  | relation |      mode       | granted | pid
------------+----------+-----------------+---------+-----
 relation   | users    | AccessShareLock | t       |  52
 virtualxid |          | ExclusiveLock   | t       |  52
(2 rows)

ROW SHARE

语句：

SELECT FOR UPDATE
SELECT FOR SHARE

冲突：

EXCLUSIVE
ACCESS EXCLUSIVE

示例：

Bob：

(bob) # begin;
BEGIN
(bob) # select * from users where id = 1 for update;
 id | username
----+----------
  1 | igor
(1 row)

Alice：

(alice) # SELECT locktype, relation::regclass, mode, granted, pid FROM pg_locks WHERE pid != pg_backend_pid();
   locktype    | relation |     mode      | granted | pid
---------------+----------+---------------+---------+-----
 relation      | users    | RowShareLock  | t       |  52
 virtualxid    |          | ExclusiveLock | t       |  52
 transactionid |          | ExclusiveLock | t       |  52
(3 rows)

这时候如果 Alice 试图去更新 id = 1的数据，那么就只能等待 Bob 的事务提交。

(alice) # update users set username = 'igorr' where id = 1;
hang...
(bob) # commit;
COMMIT
(alice) # update users set username = 'igorr' where id = 1;
UPDATE 1

ROW EXCLUSIVE

语句：

UPDATE
DELETE
INSERT

一般修改表数据的命令会获取这种锁。

冲突:

SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE
ACCESS EXCLUSIVE

示例：

Bob：

(bob) # begin;
BEGIN
(bob) # update users set username = 'igorr' where id = 1;
UPDATE 1

Alice：

(alice) # SELECT locktype, relation::regclass, mode, granted, pid FROM pg_locks WHERE pid != pg_backend_pid();
   locktype    | relation |       mode       | granted | pid
---------------+----------+------------------+---------+-----
 relation      | users    | RowExclusiveLock | t       |  52
 virtualxid    |          | ExclusiveLock    | t       |  52
 transactionid |          | ExclusiveLock    | t       |  52
(3 rows)

SHARE UPDATE EXCLUSIVE

语句：

VACUUM (without FULL)
ANALYZE
CREATE INDEX CONCURRENTLY
ALTER TABLE VALIDATE
部分ALTER TABLE。

冲突：

SHARE UPDATE EXCLUSIVE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE
ACCESS EXCLUSIVE

SHARE

语句：

CREATE INDEX (without CONCURRENTLY)

冲突：

ROW EXCLUSIVE
SHARE UPDATE EXCLUSIVE
SHARE ROW EXCLUSIVE
EXCLUSIVE
ACCESS EXCLUSIVE

SHARE ROW EXCLUSIVE

语句：

CREATE COLLATION
CREATE TRIGGER
部分ALTER TABLE

冲突：

与除了ACCESS SHARE 和 ROW SHARE 以外的所有其他锁模式冲突。

EXCLUSIVE

语句：

REFRESH MATERIALIZED VIEW CONCURRENTLY

冲突：

与除了ACCESS SHARE 以外的所有其他锁模式冲突，包括自身

ACCESS EXCLUSIVE

语句：

DROP TABLE
TRUNCATE
REINDEX
CLUSTER
VACUUM FULL
REFRESH MATERIALIZED VIEW (without CONCURRENTLY)
LOCK TABLE 命令如果没有显示的指定锁模式，那么默认的也是这种锁。

冲突：

所有其他锁模式冲突，包括自身。

冲突关系表：

行级锁

FOR UPDATE

SELECT ... FOR UPDATE 会让 SELECT 语句表现的像 UPDATE 语句一样，SELECT 选择的行会被锁住直到当前事务提交。

FOR NO KEY UPDATE

与 FOR UPDATE 类似，只是锁的等级更弱，对于 SELECT FOR KEY SHARE 不会冲突。

FOR SHARE

与 SELECT FOR KEY SHARE 类似，只是获取的是共享锁，不是排他锁，与 SELECT FOR SHARE 和 SELECT FOR KEY SHARE 不冲突。

FOR KEY SHARE

与 FOR SHARE 类似，只是锁的等级更弱，在 FOR SHARE 的基础上与 FOR NO KEY UPDATE 也不冲突。

避免行锁的方法

NOWAIT

当发现选取的行有不能获取锁的，返回错误，而不是等待。

SKIP LOCKED

跳过不能上锁的行。

其他锁

Transaction Lock

That usually indicates that the transaction waits for a row lock that is held by the transaction it is waiting for.

Row locks are not stored in the shared memory locking table permanently, but on the table row itself in the xmax system column. The value stored there is the transaction number of the blocking transaction (usually).

Once a transaction has found out who holds the lock on that row, it starts waiting for that transaction to finish, which will release the exclusive lock it holds on its own transaction ID.

Page-level Locks

控制对 shared buffer pool 中对表页的读写，用户感知不到，官方文档提到只是为了表述地完整性。

Advisory Locks

PostgreSQL提供了一种创建具有应用程序定义含义的锁的方法，称为 Advisory Locks。可以用于实现悲观锁，在表中存储标志可以实现的，但 Advisory Locks 更快，避免表膨胀，并在会话结束时由服务器自动清理。

有两种方法可以获得建议锁：在会话级别或在事务级别。

在会话级别获得 Advisory Locks 后，将一直持有，直到显式释放或会话结束。与标准的锁请求不同，会话级别的 Advisory Locks 请求不遵守事务语义：在事务中获得的锁在回滚之后仍然会被持有，同样，即使调用事务在稍后失败，解锁也是有效的。一个锁可以被它所拥有的进程多次获取；对于每个已完成的锁请求，在实际释放锁之前必须有一个相应的解锁请求。

事务级锁请求的行为更像常规锁请求：它们在事务结束时自动释放，并且不存在显式的解锁操作。对于短期使用咨询锁，这种行为通常比会话级行为更方便。

对于相同的 Advisory Locks 标识符的会话级和事务级锁请求将以预期的方式相互阻塞。如果一个会话已经拥有一个给定的 Advisory Locks，它的其他请求总是会成功，即使其他会话正在等待锁；无论现有的锁持有和新请求是在会话级别还是事务级别，此语句都为真。

案例1：

Bob：

(bob) # begin;
BEGIN
(bob) # SELECT pg_advisory_xact_lock(1);
 pg_advisory_xact_lock
-----------------------

(1 row)

Alice：

(alice) # SELECT locktype, relation::regclass, mode, granted, pid FROM pg_locks WHERE pid != pg_backend_pid();
  locktype  | relation |     mode      | granted | pid
------------+----------+---------------+---------+-----
 virtualxid |          | ExclusiveLock | t       |  52
 advisory   |          | ExclusiveLock | t       |  52
(2 rows)

阻塞形式：

-- Transaction 2
BEGIN;

SELECT pg_advisory_xact_lock(1);
-- Some work here

非阻塞形式：

-- Transaction 2
BEGIN;

SELECT pg_try_advisory_xact_lock(1) INTO vLockAcquired;
IF vLockAcquired THEN
-- Some work
ELSE
-- Lock not acquired
END;

案例2：

Bob：

(bob) # select pg_advisory_xact_lock(id) from users where id <= 2;
 pg_advisory_xact_lock
-----------------------


(2 rows)

Alice：

(alice) # select * from users where pg_try_advisory_xact_lock(id);
 id | username
----+----------
  3 | john
  4 | susan
(2 rows)

死锁

死锁产生的条件：

互斥条件（Mutual exclusion）：资源不能被共享，只能由一个进程使用。
请求与保持条件（Hold and wait）：已经得到资源的进程可以再次申请新的资源。
不可剥夺条件（No pre-emption）：已经分配的资源不能从相应的进程中被强制地剥夺。
环路/循环等待条件（Circular wait）：系统中若干进程组成环路，该环路中每个进程都在等待相邻进程正占用的资源。

两个线程各自持有一个无法共享(互斥条件)的资源，并且他们都需要获取（请求与保持条件）对方现在持有的资源才能进行下一步，但是他们又必须等对方释放了才能去获取(不可剥夺条件)，于是A等待B，B也在等待A（环路等待条件）。如此这般，死锁就产生了。

情况一

假设有 A、B 两张表

事务 T1 先在 A 表上获取到了排他锁，现在去申请 B 表的排他锁；

事务 T2 先在 B 表上获取到了排他锁，现在去申请 A 表的排他锁；

死锁产生。

情况二

两个事务更新同一张表

事务T1：

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

获取了 acctnum = 11111 的行级锁，执行成功，但事务还没结束；

事务T2：

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

获取了 acctnum = 22222 的行级锁，执行成功，尝试获取 acctnum = 11111 行级锁，但是已经被 T1 占据了，所以等待 T1 事务结束；

事务T1：

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

请求 acctnum = 22222 的行级锁，但是已经被 T2 占据。

死锁产生。

预防

对于情况二，PG 是会在一个事务超过deadlock_timeout (integer)设置的时间（默认1s）后会自动探测是否存在死锁，如果存在会 abrot 其中一个，具体哪个是不可预期的。

始终以一致的顺序获取锁资源。
一次性申请所有需要的锁资源，只要有一种资源不满足要求，哪怕其它需要的资源都足够，也不做任何分配。

解决

找出

select pg_blocking_pids(pid) as blocked_by, * from pg_stat_activity where state = 'active' order by query_start;

取消查询

pg_cancel_backend(pid int);
pg_terminate_backend(pid int)

前者取消当前的查询，后者终止整个查询进程包括数据库连接。

MVCC 与锁的关系

首先来看下多线程编程中处理读写的并发问题有三种策略：

互斥锁

一个数据对象上面只有一把锁，任何时候只要一个线程拿到该锁，其他线程就会阻塞，这意味着

写和写互斥
写和读互斥
读和读互斥

读写锁

一个数据对象上面有一把锁，但有俩个视图，读和写可以做到：

写和写互斥
写和读互斥
读和读可以并发

CopyOnWrite

写的时候，把该数据对象拷贝一份，等写完之后，再把数据对象的指针（引用）一次性赋值回去，读的时候取原始数据。这意味着

读和读可以并发
读和写可以并发
写和写理论上也可以并发

MVCC 采用的就是类似 CopyOnWrite 的策略，每个事务修改记录之前，都会先把该记录拷贝一份出来，在拷贝上做需改，再用修改完的记录替代原来的记录。原来的记录并没有被删除，只是变成了旧的版本。

正因为有了 MVCC 这种特性，通常的select语句都是不加锁的或者级别，读取的全部是数据的历史版本，从而支撑高并发的查询。这种读，专业术语叫做“快照读”，与之相对应的是“当前写”。

MVCC 解决了快照读和写之间的并发问题，但对于写和写之间、当前读和写之间的并发，MVCC无能为力，这时需要用到锁。

参考

[1] explicit-locking

[2] sql-select

[3] view-pg-locks

[4] have-an-eye-on-locks-of-postgresql

[5] Locks in PostgreSQL: 1. Relation-level locks

[6] Locks in PostgreSQL: 2. Row-level locks

[7] UNCTIONS-ADVISORY-LOCKS

Protream's Notes

准备工作

表级锁

行级锁

其他锁

死锁

MVCC 与锁的关系

参考

跳跃表（skiplist）及其 Go 实现