PostgreSQL笔记30：锁机制深度解析——表级锁、行级锁、轻量级锁与自旋锁

Wang's Blog

墨衍会员 · AI 创作全网分发码龄12年

关注

原创 2026-08-20 19:45:00 842 阅读 AI 写同款· GEO 优化 ›

【PostgreSQL】PostgreSQL查锁表详情 PostgreSQL查锁表详情阅读详情

纲要

表级锁（Table-Level Locks）
- 锁模式与冲突矩阵
  - ACCESS SHARE
  - ROW SHARE
  - ROW EXCLUSIVE
  - SHARE UPDATE EXCLUSIVE
  - SHARE
  - SHARE ROW EXCLUSIVE
  - EXCLUSIVE
  - ACCESS EXCLUSIVE
- 常见操作对应的锁模式
  - SELECT → ACCESS SHARE
  - INSERT / UPDATE / DELETE → ROW EXCLUSIVE
  - VACUUM（不带 FULL） → SHARE UPDATE EXCLUSIVE
  - CREATE INDEX（不带 CONCURRENTLY） → SHARE
  - CREATE INDEX CONCURRENTLY → SHARE UPDATE EXCLUSIVE
  - TRUNCATE / VACUUM FULL / CLUSTER / REINDEX → ACCESS EXCLUSIVE
- 锁的共享内存限制：max_locks_per_transaction × (max_connections + max_prepared_transactions)
- lock_timeout 与锁等待队列
- Fast-Path 锁机制
  - 每个后端进程 16 个 fast-path 槽位
  - 索引数量与 fast-path 失效的关系
行级锁（Row-Level Locks）
- 锁模式
  - FOR UPDATE
  - FOR NO KEY UPDATE
  - FOR SHARE
  - FOR KEY SHARE
- 存储位置：磁盘上的元组头（tuple header），非共享内存
- pg_rowlocks 插件
轻量级锁（Lightweight Locks / LWLock）
- 保护内存结构（如哈希表、缓冲区）
- BufMapping 与 BufferContent
- LWLock 等待事件
自旋锁（Spinlocks）
- 特点：忙等待（busy-wait），无等待队列
- 典型案例：old_snapshot 参数导致的性能问题
  - PostgreSQL 9.6 → 13 升级后 CPU 飙升
  - 根因：old_snapshot_threshold 开启后，页面修剪（page pruning）路径中频繁获取自旋锁
  - 修复版本：PostgreSQL 14 及以后

PostgreSQL 的锁机制是并发控制的基石。理解不同锁的层级、模式及其冲突关系，对于数据库性能调优和问题排查至关重要。本文将从表级锁、行级锁、轻量级锁和自旋锁四个维度，系统性地梳理 PostgreSQL 的锁体系。

表级锁

表级锁是 PostgreSQL 中最常接触的锁类型。PostgreSQL 官方文档将表级锁划分为八种模式。这些模式在源代码中分别对应一个整数值，因此社区中有时会以“几级锁”来指代某种锁模式。

锁模式与冲突矩阵

八种表级锁模式及其冲突关系如下：

各锁模式的典型获取场景与冲突范围：

锁模式	自动获取场景	冲突模式
`ACCESS SHARE`	`SELECT`	`ACCESS EXCLUSIVE`
`ROW SHARE`	`SELECT FOR UPDATE` / `SELECT FOR SHARE`	`EXCLUSIVE`, `ACCESS EXCLUSIVE`
`ROW EXCLUSIVE`	`INSERT`, `UPDATE`, `DELETE`, `MERGE`	`SHARE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE`
`SHARE UPDATE EXCLUSIVE`	`VACUUM`（不带 `FULL`）, `ANALYZE`, `CREATE INDEX CONCURRENTLY`, `REINDEX CONCURRENTLY`	`SHARE UPDATE EXCLUSIVE`, `SHARE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE`
`SHARE`	`CREATE INDEX`（不带 `CONCURRENTLY`）	`ROW EXCLUSIVE`, `SHARE UPDATE EXCLUSIVE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE`
`SHARE ROW EXCLUSIVE`	部分 `ALTER TABLE` 变体	`ROW EXCLUSIVE`, `SHARE UPDATE EXCLUSIVE`, `SHARE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE`
`EXCLUSIVE`	部分 `ALTER TABLE` / `REFRESH MATERIALIZED VIEW CONCURRENTLY`	`ROW SHARE`, `ROW EXCLUSIVE`, `SHARE UPDATE EXCLUSIVE`, `SHARE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE`
`ACCESS EXCLUSIVE`	`DROP TABLE`, `TRUNCATE`, `REINDEX`（不带 `CONCURRENTLY`）, `CLUSTER`, `VACUUM FULL`	所有锁模式（自冲突）

数据来源：PostgreSQL 官方文档

关键锁模式的实践解读

ACCESS SHARE：SELECT 查询在访问表时会获取此锁。它仅与 ACCESS EXCLUSIVE 冲突，因此普通的查询操作不会被大多数 DML 操作阻塞。

ROW EXCLUSIVE：INSERT、UPDATE、DELETE 等数据修改操作获取此锁。多个 DML 操作可以并发执行，因为它们之间不冲突。

SHARE UPDATE EXCLUSIVE：VACUUM（不带 FULL）获取此锁。该锁是自冲突的——同一张表上同时只能有一个 VACUUM 进程运行。这正是大表维护的痛点所在：如果单表数据量巨大，死元组（dead tuples）累积过多，VACUUM 的运行时间会非常长，而在此期间其他 VACUUM 操作无法并发执行。因此，对于大表，通常建议采用分区表策略，将数据分散到多个子表中，以便并行执行 VACUUM。

SHARE：CREATE INDEX（不带 CONCURRENTLY）获取此锁。从冲突矩阵可以看出，SHARE 与 ROW EXCLUSIVE 冲突——这意味着在创建索引期间，表上的 INSERT、UPDATE、DELETE 操作会被阻塞。在生产环境中，这通常是不可接受的。解决方案是使用 CREATE INDEX CONCURRENTLY，它仅需要 SHARE UPDATE EXCLUSIVE 锁，不会阻塞 DML 操作。但需要注意，CREATE INDEX CONCURRENTLY 会带来额外的复杂度：它需要执行两次表扫描，并且如果构建过程中出现问题，可能会留下无效（invalid）的索引，需要手动清理。

ACCESS EXCLUSIVE：这是 PostgreSQL 中最高级别的锁，与所有锁模式冲突。TRUNCATE、VACUUM FULL、CLUSTER、REINDEX（不带 CONCURRENTLY）等重量级操作会获取此锁。在这些操作执行期间，目标表上不能有任何并发的读写操作。

锁的共享内存限制

PostgreSQL 的表级锁存储在共享内存中。共享锁表的大小由以下公式决定：

max_locks_per_transaction * (max_connections + max_prepared_transactions)

默认情况下，max_locks_per_transaction = 64，max_connections = 100，因此共享锁表大约可以跟踪 64 × 100 = 6400 个对象的锁。当 pg_locks 视图中的锁记录总数超过此上限时，会报错：

ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction

需要注意的是，这里的限制是可锁对象的数量，而非行锁的数量——行锁存储在磁盘上，不受此限制。

锁等待与 lock_timeout

PostgreSQL 的锁等待队列采用先来先服务（FIFO）策略。当一个事务因锁冲突而等待时，后续到达的请求即使与当前持有者不冲突，也需要排队等待。这可能导致锁队列不断累积，进而引发更广泛的阻塞。

lock_timeout 参数用于设置锁获取的超时时间（单位为毫秒）。当设置为大于 0 的值时，任何锁获取操作如果等待超过指定时间，语句将被中止并返回错误：

SET lock_timeout = '5s';

-- 如果 5 秒内无法获取锁，将报错：
-- ERROR:  canceling statement due to lock timeout

在生产环境中，将 lock_timeout 设置为 60 秒或 120 秒是一种常见的最佳实践，可以有效防止锁队列无限增长导致的潜在雪崩效应。

查看锁阻塞情况

PostgreSQL 提供了多种方式查看锁阻塞信息。

方式一：使用 pg_blocking_pids() 函数

-- 查看指定 PID 被哪些进程阻塞
SELECT pg_blocking_pids(31591);

pg_blocking_pids() 返回一个整数数组，包含阻塞指定进程的所有进程 ID。如果返回空数组，表示该进程未被阻塞。

方式二：查询 pg_locks 视图

SELECT locktype, database, relation::regclass, pid, mode, granted
FROM pg_locks
WHERE pid IN (31591, 23835);

granted 字段为 false 表示该锁尚未获取，进程正在等待。

方式三：综合查询被阻塞的进程

SELECT
    pid,
    usename,
    application_name,
    state,
    wait_event_type,
    wait_event,
    pg_blocking_pids(pid) AS blocked_by,
    query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

Fast-Path 锁机制

Fast-Path 是 PostgreSQL 中一项重要的锁优化机制。其核心思想是：对于弱锁（如 ACCESS SHARE、ROW SHARE、ROW EXCLUSIVE 等互不冲突的锁），如果某个事务在表上只申请了弱锁，且能够确认该表上不存在其他事务持有的强锁，那么就不必将该锁记录在共享内存的 pg_locks 表中，从而避免频繁访问共享锁表，提升性能。

Fast-Path 机制存在一个关键限制：每个后端进程（backend）只有 16 个 fast-path 槽位。这意味着：

如果一个会话在同一个表上持有超过 16 个锁（例如，查询涉及超过 16 个索引），fast-path 将失效，后续锁操作将回退到常规锁管理器。
表上索引数量过多时，一次索引扫描需要对所有索引加锁，很容易耗尽 fast-path 槽位。

因此，在生产环境中应避免创建过多冗余索引，以减少 fast-path 失效带来的性能损耗。

行级锁

行级锁用于控制对表中特定行的并发访问。与表级锁不同，行级锁的信息存储在磁盘上的元组头（tuple header）中，而非共享内存。因此，行级锁在理论上没有数量上限。

行级锁模式

PostgreSQL 支持四种行级锁模式：

锁模式	对应 SQL 语法	说明
`FOR UPDATE`	`SELECT ... FOR UPDATE`	最严格的模式，阻止其他事务对行进行任何修改或加锁
`FOR NO KEY UPDATE`	`SELECT ... FOR NO KEY UPDATE`	与 `FOR UPDATE` 类似，但不阻塞 `FOR KEY SHARE` 锁
`FOR SHARE`	`SELECT ... FOR SHARE`	共享锁，阻止其他事务执行 `UPDATE`、`DELETE` 或 `FOR UPDATE`，但允许 `FOR SHARE`
`FOR KEY SHARE`	`SELECT ... FOR KEY SHARE`	最弱的模式，仅阻止其他事务修改行的键值

查看行级锁

PostgreSQL 提供了一个扩展插件 pg_rowlocks，用于查看表上的行级锁信息：

-- 启用扩展（如未安装）
CREATE EXTENSION IF NOT EXISTS pg_rowlocks;

-- 查看表上的行锁
SELECT * FROM pg_rowlocks('t1');

该插件提供的 pg_rowlocks() 函数可以显示每个行锁的类型（如 NoKeyUpdate、KeyShare 等），帮助 DBA 理解当前的并发冲突状况。

轻量级锁

轻量级锁（LWLock）是 PostgreSQL 中用于保护共享内存结构的锁机制。与表级锁和行级锁不同，轻量级锁不直接面向 SQL 层面，而是服务于内核层面的数据结构保护。

常见的轻量级锁

BufMapping ：当进程需要访问某个数据块时，首先需要通过哈希表定位该数据块在共享缓冲区中的位置。访问这个哈希表时需要获取 BufMapping 锁。在高并发场景下，如果 BufMapping 成为瓶颈，通常意味着需要增大 shared_buffers，或通过 fillfactor 减少热块冲突。

BufferContent ：在访问共享缓冲区中具体的数据页内容时，需要获取 BufferContent 锁。读取时获取共享锁，写入时获取独占锁。

WALWrite ：在写入 WAL 日志时获取，用于保护 WAL 缓冲区的并发写入。

监控轻量级锁等待

通过 pg_stat_activity 视图可以监控轻量级锁等待事件：

SELECT pid, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE wait_event_type = 'LWLock';

自旋锁

自旋锁（Spinlock）是 PostgreSQL 中最底层的锁机制，用于保护极短临界区的并发访问。自旋锁的特点包括：

忙等待（busy-wait） ：获取不到锁时不会进入睡眠，而是持续循环检测，消耗 CPU 时间。
无等待队列：没有 FIFO 队列，所有等待进程平等竞争。
事务结束时自动释放：无需显式解锁。

由于自旋锁的忙等待特性，在 CPU 资源紧张时，自旋锁竞争会显著加剧 CPU 负载。

经典案例：old_snapshot 参数引发的自旋锁风暴

这是一个在 PostgreSQL 社区中广为流传的性能问题案例。

背景：某团队将 PostgreSQL 从 9.6 升级到 13 后，发现 CPU 使用率从 16% 飙升至 100%，且业务负载和数据库参数均无明显变化。

排查过程：通过 perf 工具抓取 CPU 热点，发现大量 CPU 时间消耗在 s_lock 相关函数（自旋锁实现）上。进一步分析堆栈，定位到 TransactionIdLimitedForOldSnapshots 函数。

根因：在 PostgreSQL 13 及更早版本中，当 old_snapshot_threshold 参数被启用（设置为大于 0 的值）时，每个后端进程在执行页面修剪（page pruning）时都会调用 TransactionIdLimitedForOldSnapshots，该函数内部会频繁获取自旋锁。在高并发场景下，这导致大量 CPU 时间消耗在自旋锁竞争上。

验证与解决：在相同并发（如 10 个或 30 个会话）和相同 SQL 负载下，关闭 old_snapshot_threshold 参数后，CPU 使用率降低约 50%。该问题在 PostgreSQL 14 中得到了显著优化。

经验教训：在 PostgreSQL 14 之前的版本中，除非有明确需求，否则不建议启用 old_snapshot_threshold 参数。

API 速览

`pg_blocking_pids()`

属性	说明
所属库	PostgreSQL 系统函数
方法签名	`pg_blocking_pids(pid integer) → integer[]`
参数	`pid`：目标服务器进程的进程 ID
返回值	阻塞目标进程的所有进程 ID 数组；若未被阻塞或进程不存在则返回空数组

-- 示例：查询 PID 12345 被哪些进程阻塞
SELECT pg_blocking_pids(12345);

-- 示例：查询所有被阻塞的进程及其阻塞者
SELECT
    pid,
    pg_blocking_pids(pid) AS blocked_by,
    query
FROM pg_stat_activity
WHERE pg_blocking_pids(pid) != '{}';

`pg_locks` 系统视图

属性	说明
所属库	PostgreSQL 系统目录
关键字段	`locktype`（锁类型）、`database`、`relation`、`pid`、`mode`（锁模式）、`granted`（是否已获取）
说明	行级锁通常不在此视图中显示，因其存储在磁盘上

-- 查询当前所有未获取的锁等待
SELECT
    pid,
    mode,
    granted,
    relation::regclass AS relation_name,
    locktype
FROM pg_locks
WHERE granted = false;

`pg_rowlocks()` 函数

属性	说明
所属库	`pg_rowlocks` 扩展
方法签名	`pg_rowlocks(relation regclass) → setof record`
参数	`relation`：目标表的名称或 OID
返回值	表中每行的锁信息，包括锁类型、持有者 PID 等

-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_rowlocks;

-- 查看表 t1 上的行级锁
SELECT * FROM pg_rowlocks('t1');

`lock_timeout` 配置参数

属性	说明
所属库	PostgreSQL 运行时配置
类型	`integer`（毫秒）
默认值	`0`（禁用）
作用域	会话级别（可动态设置）

-- 会话级设置
SET lock_timeout = '5s';

-- 在事务中临时设置
BEGIN;
SET LOCAL lock_timeout = '3s';
-- 执行可能加锁的操作
COMMIT;

`old_snapshot_threshold` 配置参数

属性	说明
所属库	PostgreSQL 运行时配置
类型	`integer`（分钟）
默认值	`-1`（禁用）
适用版本	PostgreSQL 9.6+（14 版本后性能问题已优化）

-- 查看当前设置
SHOW old_snapshot_threshold;

-- 禁用（推荐，尤其适用于 PG 14 以下版本）
SET old_snapshot_threshold = -1;

Demo 简单示例

以下 Demo 演示了 PostgreSQL 表级锁的阻塞与排查流程。

环境准备

-- 创建测试表
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INT PRIMARY KEY, name TEXT);
INSERT INTO t1 VALUES (1, 'Alice'), (2, 'Bob');

场景一：CREATE INDEX 被 INSERT 阻塞

会话 A（执行 INSERT）

BEGIN;
INSERT INTO t1 VALUES (3, 'Charlie');
-- 不提交，持有 ROW EXCLUSIVE 锁

会话 B（执行 CREATE INDEX）

-- 将被阻塞，因为 SHARE 锁与 ROW EXCLUSIVE 冲突
CREATE INDEX idx_t1_name ON t1 (name);

会话 C（排查阻塞）

-- 获取会话 A 和 B 的 PID
SELECT pid, backend_start, query FROM pg_stat_activity WHERE query LIKE '%t1%';

-- 假设会话 B 的 PID 为 31591
SELECT pg_blocking_pids(31591);

-- 查看锁详情
SELECT
    locktype,
    relation::regclass,
    pid,
    mode,
    granted
FROM pg_locks
WHERE pid IN (31591, 23835)  -- 替换为实际 PID
ORDER BY pid;

场景二：使用 CREATE INDEX CONCURRENTLY 避免阻塞

-- 不会阻塞 DML 操作（仅需 SHARE UPDATE EXCLUSIVE 锁）
CREATE INDEX CONCURRENTLY idx_t1_name ON t1 (name);

场景三：设置 lock_timeout 防止无限等待

-- 设置 5 秒超时
SET lock_timeout = '5s';

-- 如果 5 秒内无法获取锁，将报错退出
CREATE INDEX idx_t1_name ON t1 (name);
-- ERROR:  canceling statement due to lock timeout

运行说明

在 PostgreSQL 实例中创建测试数据库。
按顺序在多个会话（psql 连接）中执行上述 SQL。
使用排查语句观察锁阻塞情况。

技术点总结

INSERT 获取 ROW EXCLUSIVE 表级锁。
CREATE INDEX（不带 CONCURRENTLY）获取 SHARE 表级锁，与 ROW EXCLUSIVE 冲突。
CREATE INDEX CONCURRENTLY 仅获取 SHARE UPDATE EXCLUSIVE，不与 DML 冲突。
pg_blocking_pids() 和 pg_locks 是锁排查的核心工具。
lock_timeout 可防止锁等待无限期持续。

多语言示例

以下分别使用 Node.js、Go、Python 和 Java 演示 PostgreSQL 锁机制相关的操作，包括：

设置 lock_timeout
执行 SELECT ... FOR UPDATE 行级锁
查询锁等待情况（pg_locks 和 pg_blocking_pids）

所有示例均基于 PostgreSQL 14+，使用各语言主流驱动或 ORM。

Node.js（使用 `pg` 驱动）

// 依赖: npm install pg
const { Client } = require('pg');

const config = {
  host: 'localhost',
  port: 5432,
  database: 'testdb',
  user: 'testuser',
  password: 'testpass',
};

async function run() {
  const client = new Client(config);
  await client.connect();

  try {
    // 1. 设置锁超时
    await client.query('SET lock_timeout = \'5s\'');

    // 2. 开启事务并获取行锁
    await client.query('BEGIN');
    const res = await client.query(
      'SELECT * FROM t1 WHERE id = 1 FOR UPDATE'
    );
    console.log('锁定行:', res.rows[0]);

    // 3. 模拟业务处理（保持事务未提交）
    await new Promise(resolve => setTimeout(resolve, 10000)); // 10秒

    await client.query('COMMIT');
  } catch (err) {
    console.error('错误:', err.message);
    await client.query('ROLLBACK');
  } finally {
    await client.end();
  }
}

run();

// 配套查询锁等待（可另开连接执行）
async function checkLocks() {
  const client = new Client(config);
  await client.connect();
  const res = await client.query(`
    SELECT pid, mode, granted, relation::regclass, query
    FROM pg_locks
    LEFT JOIN pg_stat_activity USING (pid)
    WHERE locktype = 'relation'
  `);
  console.table(res.rows);
  await client.end();
}

Go（使用 `pgx` 驱动）

// 依赖: go get github.com/jackc/pgx/v5
package main

import (
	"context"
	"fmt"
	"log"
	"time"

	"github.com/jackc/pgx/v5"
)

func main() {
	ctx := context.Background()
	conn, err := pgx.Connect(ctx, "postgres://testuser:testpass@localhost:5432/testdb")
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close(ctx)

	// 1. 设置锁超时
	_, err = conn.Exec(ctx, "SET lock_timeout = '5s'")
	if err != nil {
		log.Fatal(err)
	}

	// 2. 开启事务并获取行锁
	tx, err := conn.Begin(ctx)
	if err != nil {
		log.Fatal(err)
	}
	defer tx.Rollback(ctx)

	var id int
	var name string
	err = tx.QueryRow(ctx, "SELECT id, name FROM t1 WHERE id = 1 FOR UPDATE").Scan(&id, &name)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("锁定行: id=%d, name=%s\n", id, name)

	// 模拟业务处理
	time.Sleep(10 * time.Second)

	err = tx.Commit(ctx)
	if err != nil {
		log.Fatal(err)
	}
}

// 查询锁等待（可单独执行）
func checkLocks() {
	ctx := context.Background()
	conn, _ := pgx.Connect(ctx, "postgres://testuser:testpass@localhost:5432/testdb")
	defer conn.Close(ctx)

	rows, _ := conn.Query(ctx, `
		SELECT pid, mode, granted, relation::regclass
		FROM pg_locks
		WHERE locktype = 'relation'
	`)
	defer rows.Close()
	for rows.Next() {
		var pid int, mode string, granted bool, rel string
		rows.Scan(&pid, &mode, &granted, &rel)
		fmt.Printf("PID=%d mode=%s granted=%v rel=%s\n", pid, mode, granted, rel)
	}
}

Python（使用 `psycopg2`）

# 依赖: pip install psycopg2-binary
import psycopg2
import time

config = {
    'host': 'localhost',
    'port': 5432,
    'dbname': 'testdb',
    'user': 'testuser',
    'password': 'testpass'
}

def run():
    conn = psycopg2.connect(**config)
    conn.autocommit = False
    cur = conn.cursor()

    try:
        # 1. 设置锁超时
        cur.execute("SET lock_timeout = '5s'")

        # 2. 开启事务并获取行锁
        cur.execute("SELECT id, name FROM t1 WHERE id = 1 FOR UPDATE")
        row = cur.fetchone()
        print(f"锁定行: id={row[0]}, name={row[1]}")

        # 模拟业务处理
        time.sleep(10)

        conn.commit()
    except Exception as e:
        print("错误:", e)
        conn.rollback()
    finally:
        cur.close()
        conn.close()

if __name__ == "__main__":
    run()

# 查询锁等待（独立函数）
def check_locks():
    conn = psycopg2.connect(**config)
    cur = conn.cursor()
    cur.execute("""
        SELECT pid, mode, granted, relation::regclass
        FROM pg_locks
        WHERE locktype = 'relation'
    """)
    for row in cur.fetchall():
        print(f"PID={row[0]} mode={row[1]} granted={row[2]} rel={row[3]}")
    cur.close()
    conn.close()

Java（使用 `JDBC`）

// 依赖: org.postgresql:postgresql:42.7.3
import java.sql.*;
import java.util.Properties;

public class PgLockDemo {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:postgresql://localhost:5432/testdb";
        Properties props = new Properties();
        props.setProperty("user", "testuser");
        props.setProperty("password", "testpass");

        try (Connection conn = DriverManager.getConnection(url, props)) {
            conn.setAutoCommit(false);

            // 1. 设置锁超时
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("SET lock_timeout = '5s'");
            }

            // 2. 获取行锁
            String sql = "SELECT id, name FROM t1 WHERE id = 1 FOR UPDATE";
            try (PreparedStatement pstmt = conn.prepareStatement(sql);
                 ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    System.out.printf("锁定行: id=%d, name=%s%n",
                            rs.getInt("id"), rs.getString("name"));
                }
            }

            // 模拟业务处理
            Thread.sleep(10000);

            conn.commit();
        } catch (SQLException e) {
            System.err.println("错误: " + e.getMessage());
            // 回滚由 try-with-resources 自动处理，但需注意
        }
    }

    // 查询锁等待（独立方法）
    public static void checkLocks() throws Exception {
        String url = "jdbc:postgresql://localhost:5432/testdb";
        Properties props = new Properties();
        props.setProperty("user", "testuser");
        props.setProperty("password", "testpass");
        try (Connection conn = DriverManager.getConnection(url, props);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(
                     "SELECT pid, mode, granted, relation::regclass FROM pg_locks WHERE locktype = 'relation'")) {
            while (rs.next()) {
                System.out.printf("PID=%d mode=%s granted=%b rel=%s%n",
                        rs.getInt("pid"), rs.getString("mode"),
                        rs.getBoolean("granted"), rs.getString("relation"));
            }
        }
    }
}

多语言对比

特性	Node.js (pg)	Go (pgx)	Python (psycopg2)	Java (JDBC)
驱动/库	`pg`	`pgx/v5`	`psycopg2`	`postgresql` JDBC 驱动
连接方式	`Client` 对象	`pgx.Connect`	`psycopg2.connect`	`DriverManager.getConnection`
事务控制	`client.query('BEGIN/COMMIT')`	`conn.Begin()` / `tx.Commit()`	`conn.commit()` / `conn.rollback()`	`conn.setAutoCommit(false)` / `conn.commit()`
设置超时	`client.query("SET lock_timeout")`	`conn.Exec(ctx, "SET lock_timeout")`	`cur.execute("SET lock_timeout")`	`stmt.execute("SET lock_timeout")`
行锁语法	`SELECT ... FOR UPDATE`	同	同	同
异步支持	原生 async/await	原生 context	不支持（同步）	同步（可配合 CompletableFuture）
连接池	`pg.Pool`	`pgxpool`	`psycopg2.pool`	`HikariCP` 等
错误处理	try-catch	返回 error	try-except	try-catch
常见用途	Web 后端、Serverless	高性能微服务	数据科学、脚本	企业级应用

运行说明

确保本地 PostgreSQL 运行，创建测试表：

CREATE TABLE t1 (id INT PRIMARY KEY, name TEXT);
INSERT INTO t1 VALUES (1, 'Alice'), (2, 'Bob');

各语言示例均需安装对应依赖（见注释）。
分别运行，观察事务中 FOR UPDATE 加锁，并在另一个会话中尝试更新同一行（将被阻塞直至超时）。
使用提供的 checkLocks 函数查看锁信息。

技术点总结

所有语言均通过标准 SQL 实现锁控制，无特殊方言。
事务隔离级别默认 READ COMMITTED，FOR UPDATE 保证行锁。
lock_timeout 可在会话或事务级别设置，统一生效。
锁查询依赖 pg_locks 和 pg_stat_activity，跨语言一致。
各语言驱动均支持连接参数设置（如 connect_timeout 等）。

项目难点与解决方案

核心难点

PostgreSQL 锁机制层级复杂（表级、行级、轻量级、自旋锁），不同层级的锁相互独立又相互影响，故障排查时难以快速定位根因。
锁的共享内存有上限（max_locks_per_transaction），在高并发或复杂事务场景下可能耗尽。
Fast-Path 机制在索引过多的表上容易失效，导致性能回退。
自旋锁问题隐蔽性强，通常需要借助 perf 等工具才能定位。

解决方案

建立锁监控体系，定期查询 pg_locks 和 pg_stat_activity，及时发现锁等待和阻塞链。
对大表实施分区策略，分散 VACUUM 压力。
使用 CREATE INDEX CONCURRENTLY 替代普通 CREATE INDEX，避免阻塞生产流量。
合理设置 lock_timeout，防止锁队列无限堆积。
定期审查索引使用情况，移除冗余索引，减少 fast-path 失效风险。
升级到 PostgreSQL 14 及以上版本，规避 old_snapshot_threshold 的自旋锁性能问题。

广度

本文涵盖了 PostgreSQL 锁机制的完整金字塔——从面向 SQL 层的表级锁和行级锁，到内核层面的轻量级锁和自旋锁，并涉及了 Fast-Path 优化、共享内存限制、锁超时配置等横向知识点。

深度

深入分析了每种锁模式的冲突矩阵、典型应用场景和性能影响，并通过真实案例（old_snapshot_threshold 自旋锁风暴）展示了锁问题排查的完整思路——从现象观察到工具定位再到根因分析。

复杂度

锁机制本身是数据库内核中最复杂的子系统之一。本文在保持技术准确性的前提下，将复杂度分层拆解，使读者能够循序渐进地理解从最上层（表级锁）到最底层（自旋锁）的完整锁体系。

官方文档

参考链接

总结

PostgreSQL 的锁机制是一个自底向上的完整体系：自旋锁保护最底层的临界变量，采用忙等待策略；轻量级锁（LWLock）保护共享内存结构（如缓冲区、哈希表）；表级锁控制对表级对象的并发访问，共有八种模式，通过冲突矩阵定义互斥关系；行级锁存储在磁盘元组头中，用于控制行级别的并发操作。

Fast-Path 机制通过将弱锁排除在共享锁表之外来优化性能，但每个后端仅限 16 个槽位。锁的共享内存受 max_locks_per_transaction 限制，锁等待队列采用 FIFO 策略。在实践中，应善用 CREATE INDEX CONCURRENTLY 避免阻塞 DML，合理设置 lock_timeout 防止锁队列堆积，并在 PostgreSQL 14 之前慎用 old_snapshot_threshold 以避免自旋锁性能问题。