理解PostgreSQL锁对于构建可伸缩的应用程序和避免停机是很重要的。现代计算机和服务器有许多CPU核心，可以并行执行多个查询。数据库包含许多一致的结构，这些结构由并行运行的查询或后台进程所做的更改可能导致数据库崩溃，甚至损坏数据。因此，我们需要能够阻止来自并发进程的访问，同时更改共享内存结构或行。一个线程更新结构，而其他线程等待(独占锁)，或者多个线程读取结构，所有写入等待。等待的副作用是锁定争用和服务器资源浪费。因此，理解为什么会发生等待以及涉及哪些锁是很重要的。在本文中，我将回顾PostgreSQL行级锁。
行锁-概述
PostgreSQL在不同的抽象级别上有许多锁。应用程序中最重要的锁与MVCC实现行级锁相关。其次——在维护任务期间(在备份/数据库迁移模式更改期间)出现的锁——表级锁。在低级PostgreSQL锁上也有可能看到等待，但这种情况很少见。更常见的情况是，CPU使用率很高，有许多并发查询正在运行，但与正常的并行查询数量相比，整体服务器性能下降。
示例环境
接下来，你需要一个PostgreSQL服务器，它有一个单列表，包含几行：
postgres=# CREATE TABLE locktest (c INT);
CREATE TABLE
postgres=# INSERT INTO locktest VALUES (1), (2);
INSERT 0 2
行锁
场景：两个并发事务试图选择要更新的一行。
PostgreSQL在这种情况下使用行级锁。行级锁定与MVCC实现紧密集成，并使用隐藏的xmin和xmax字段。xmin和xmax存储事务id。所有需要行级锁的语句都会修改xmax字段(甚至包括SELECT FOR UPDATE)。修改发生在查询返回结果之后，所以为了看到xmax的变化，我们需要运行两次SELECT FOR UPDATE。通常，xmax字段用于将一行标记为过期(要么被某些事务完全删除，要么用于更新的行版本)，但它也用于行级锁定基础结构。
postgres=# BEGIN;
BEGIN
postgres=# SELECT xmin,xmax, txid_current(), c FROM locktest WHERE c=1 FOR UPDATE;
xmin | xmax | txid_current | c
----------+------+--------------+---
12594472 | 0 | 12594473 | 1
(1 row)
postgres=# SELECT xmin,xmax, txid_current(), c FROM locktest WHERE c=1 FOR UPDATE;
xmin | xmax | txid_current | c
----------+----------+--------------+---
12594472 | 12594473 | 12594473 | 1
(1 row)
如果语句试图修改同一行，则检查未完成事务列表。语句必须等待修改，直到id=xmax的事务完成。
没有用于等待特定行的基础设施，但是事务可以等待事务id。
-- second connection
SELECT xmin,xmax,txid_current() FROM locktest WHERE c=1 FOR UPDATE;
在第二个连接中运行的SELECT FOR UPDATE查询未完成，正在等待第一个事务完成。
pg_locks
这样的等待和锁可以通过查询pg_locks来查看:
postgres=# SELECT locktype,transactionid,virtualtransaction,pid,mode,granted,fastpath FROM pg_locks WHERE transactionid=12594473;
locktype | transactionid | virtualtransaction | pid | mode | granted | fastpath
---------------+---------------+--------------------+-------+---------------+---------+----------
transactionid | 12594473 | 5/48 | 33358 | ShareLock | f | f
transactionid | 12594473 | 4/13981 | 33325 | ExclusiveLock | t | f
(2 rows)
您可以看到locktype=transactionid == 12594473的写入器事务id。让我们获取持有锁的pid和后端id:
postgres=# SELECT id,pg_backend_pid() FROM pg_stat_get_backend_idset() AS t(id) WHERE pg_stat_get_backend_pid(id) = pg_backend_pid();
id | pg_backend_pid
----+----------------
5 | 33418
(1 row)
每个后端都有一个OS进程标识符(PID)和内部PostgreSQL标识符(backend id)。PostgreSQL可以处理许多事务，但是锁定只能发生在后端之间，并且每个后端执行一个事务。内部簿记只需要一个虚拟事务标识符：后端内部的一对后端id和一个序列号。
不管被锁的行数是多少，PostgreSQL在pg_locks表中只有一个相关的锁。查询可能修改数十亿行，但PostgreSQL不会为冗余锁结构浪费内存。
写入线程在其transactionid上设置ExclusiveLock。所有行级锁等待器都设置了ShareLock。一旦写入器释放锁，锁管理器将恢复之前锁定的所有后端锁。
transactionid的锁释放发生在提交或回滚时。
pg_stat_activity
另一个获取锁定相关细节的好方法是从pg_stat_activity表中选择：
postgres=# SELECT pid,backend_xid,wait_event_type,wait_event,state,query FROM pg_stat_activity WHERE pid IN (33358, 33325);
-[ RECORD 1 ]---+------------------------------------------------------------------------
pid | 33325
backend_xid | 12594473
wait_event_type |
wait_event |
state | idle in transaction
query | SELECT xmin,xmax, txid_current(), c FROM locktest WHERE c=1 FOR UPDATE;
-[ RECORD 2 ]---+------------------------------------------------------------------------
pid | 33358
backend_xid | 12594474
wait_event_type | Lock
wait_event | transactionid
state | active
query | SELECT xmin,xmax,txid_current() FROM locktest WHERE c=1 FOR UPDATE;
源代码级调查
让我们用gdb和pt-pmp工具检查服务员的堆栈跟踪：
# pt-pmp -p 33325
Sat Jul 28 10:10:25 UTC 2018
30 ../sysdeps/unix/sysv/linux/epoll_wait.c: No such file or directory.
1 epoll_wait,WaitEventSetWaitBlock,WaitEventSetWait,WaitLatchOrSocket,WaitLatch,ProcSleep,WaitOnLock,LockAcquireExtended,LockAcquire,XactLockTableWait,heap_lock_tuple,ExecLockRows,ExecProcNode,ExecutePlan,standard_ExecutorRun,PortalRunSelect,PortalRun,exec_simple_query,PostgresMain,BackendRun,BackendStartup,ServerLoop,PostmasterMain,main
WaitOnLock函数导致等待。函数位于lock.c文件(POSTGRES主锁机制)中。
锁表是一个共享内存哈希表。冲突进程在存储区/lmgr/proc.c中休眠。在大多数情况下，应该通过lmgr.c或其他锁管理模块调用此代码，而不是直接调用。
接下来，在pg_stat_activity中作为“Lock”列出的锁也称为重量级锁，并由锁管理器控制。hwlock也用于许多高级操作。
顺便说一下，完整的描述可以在这里找到:https://www.postgresql.org/docs/current/static/explicit-locking.html
总结
- 避免长时间运行的事务修改频繁更新的行或修改过多的行
- 其次，不要在MVCC数据库中使用热点(由多个应用程序客户端连接并行更新的单行或多行)。这种工作负载更适合于内存中的数据库，并且通常可以与主要业务逻辑分离。
