PostgreSQL 显式锁定完全指南

概述

PostgreSQL 提供了多种锁定机制来控制并发访问，确保数据的一致性和完整性。当 MVCC（多版本并发控制）无法满足特定业务需求时，显式锁定就成为了重要的并发控制手段。

INFO

核心概念显式锁定是数据库管理系统中用于控制并发访问的重要机制，它通过在事务执行期间锁定特定资源来防止数据竞争和确保事务的隔离性。

锁定机制架构

表级锁详解

锁定模式层次结构

表级锁按照限制性从低到高可以分为以下八个级别：

定义：最低限制的表级锁，只与 ACCESS EXCLUSIVE 锁冲突。

使用场景：

普通的 SELECT 查询
只读操作

业务示例：电商系统中查看商品信息

sql

-- 业务场景：用户浏览商品列表
SELECT product_id, name, price, stock_quantity
FROM products
WHERE category = '电子产品';

-- 自动获取 ACCESS SHARE 锁
-- 允许其他用户同时查看和修改数据

TIP

实际应用在高并发的电商系统中，商品浏览是最频繁的操作。ACCESS SHARE 锁允许大量用户同时查看商品信息，而不会阻塞其他操作。

定义：与 EXCLUSIVE 和 ACCESS EXCLUSIVE 锁冲突。

使用场景：

SELECT FOR UPDATE/SHARE 查询
需要后续修改的查询

业务示例：库存检查和预留

sql

-- 业务场景：用户下单前检查并预留库存
BEGIN;

-- 获取 ROW SHARE 锁，准备后续更新
SELECT product_id, stock_quantity
FROM products
WHERE product_id = 1001
FOR UPDATE;

-- 检查库存是否充足
-- 如果充足，后续执行扣减操作
UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_id = 1001 AND stock_quantity >= 2;

COMMIT;

3. ROW EXCLUSIVE 锁

定义：与 SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE 和 ACCESS EXCLUSIVE 锁冲突。

使用场景：

UPDATE、DELETE、INSERT、MERGE 操作
所有修改数据的操作

业务示例：订单创建和库存扣减

sql

-- 业务场景：用户下单，需要扣减库存
BEGIN;

-- 插入订单记录（获取 ROW EXCLUSIVE 锁）
INSERT INTO orders (user_id, product_id, quantity, order_time)
VALUES (123, 1001, 2, NOW());

-- 更新库存（获取 ROW EXCLUSIVE 锁）
UPDATE products
SET stock_quantity = stock_quantity - 2,
    last_updated = NOW()
WHERE product_id = 1001;

COMMIT;

WARNING

注意事项 ROW EXCLUSIVE 锁会阻塞其他事务的修改操作，但不会阻塞普通的 SELECT 查询。在高并发场景下需要考虑锁等待时间。

定义：保护表免受并发模式更改和 VACUUM 运行的影响。

使用场景：

VACUUM（非 FULL 模式）
ANALYZE
CREATE INDEX CONCURRENTLY
表结构维护操作

业务示例：数据库维护期间的索引创建

sql

-- 业务场景：在生产环境中在线创建索引
-- 不阻塞正常的查询和 DML 操作

CREATE INDEX CONCURRENTLY idx_products_category
ON products(category);

-- 此操作获取 SHARE UPDATE EXCLUSIVE 锁
-- 允许正常的读写操作继续进行
-- 但阻塞其他 DDL 操作

定义：保护表免受并发数据更改，但允许读取操作。

使用场景：

CREATE INDEX（非 CONCURRENTLY）
需要一致性快照的批量操作

业务示例：创建报表索引

sql

-- 业务场景：为报表查询创建索引
-- 确保创建过程中数据不被修改

CREATE INDEX idx_orders_date_range
ON orders(order_date, status);

-- 获取 SHARE 锁，阻塞所有写操作
-- 但允许其他用户查询数据

定义：自排他锁，一次只能有一个会话持有。

使用场景：

CREATE TRIGGER
某些 ALTER TABLE 操作

业务示例：添加业务规则触发器

sql

-- 业务场景：为订单表添加审计触发器
CREATE OR REPLACE FUNCTION audit_order_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO order_audit_log (
        order_id, operation, old_status, new_status, changed_at
    ) VALUES (
        NEW.order_id, TG_OP, OLD.status, NEW.status, NOW()
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器时获取 SHARE ROW EXCLUSIVE 锁
CREATE TRIGGER tr_order_audit
    AFTER UPDATE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION audit_order_changes();

7. EXCLUSIVE 锁

定义：只允许并发的 ACCESS SHARE 锁，即只允许读取操作。

使用场景：

REFRESH MATERIALIZED VIEW CONCURRENTLY
需要独占写入但允许读取的操作

业务示例：刷新销售报表物化视图

sql

-- 业务场景：每日刷新销售汇总报表
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT
    DATE(order_date) as sale_date,
    COUNT(*) as order_count,
    SUM(total_amount) as total_sales
FROM orders
WHERE status = 'completed'
GROUP BY DATE(order_date);

-- 并发刷新物化视图
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

-- 获取 EXCLUSIVE 锁，允许用户查看但不允许修改

8. ACCESS EXCLUSIVE 锁

定义：最严格的锁，与所有其他锁冲突。

使用场景：

DROP TABLE
TRUNCATE
VACUUM FULL
CLUSTER
大部分 ALTER TABLE 操作

业务示例：清空临时数据表

sql

-- 业务场景：清空过期的会话数据
-- 这是一个维护操作，需要完全独占访问

TRUNCATE TABLE user_sessions;

-- 获取 ACCESS EXCLUSIVE 锁
-- 阻塞所有其他操作直到完成

锁冲突矩阵

表级锁冲突关系

请求锁模式 ↓ / 现有锁模式 →	ACCESS SHARE	ROW SHARE	ROW EXCL.	SHARE UPDATE EXCL.	SHARE	SHARE ROW EXCL.	EXCL.	ACCESS EXCL.
ACCESS SHARE	✓	✓	✓	✓	✓	✓	✓	✗
ROW SHARE	✓	✓	✓	✓	✓	✓	✗	✗
ROW EXCLUSIVE	✓	✓	✓	✓	✗	✗	✗	✗
SHARE UPDATE EXCL.	✓	✓	✓	✗	✗	✗	✗	✗
SHARE	✓	✓	✗	✗	✓	✗	✗	✗
SHARE ROW EXCL.	✓	✓	✗	✗	✗	✗	✗	✗
EXCLUSIVE	✓	✗	✗	✗	✗	✗	✗	✗
ACCESS EXCLUSIVE	✗	✗	✗	✗	✗	✗	✗	✗

TIP

读取规则

✓ 表示锁可以并存
✗ 表示锁冲突，需要等待

行级锁详解

行级锁提供了更细粒度的并发控制，只锁定特定的数据行而不是整个表。

行级锁模式

1. FOR UPDATE 锁

定义：最强的行级锁，防止其他事务锁定、修改或删除这些行。

业务示例：银行账户转账

sql

-- 业务场景：银行转账操作，确保账户余额的一致性
BEGIN;

-- 锁定转出账户，防止其他事务修改余额
SELECT account_id, balance
FROM accounts
WHERE account_id = 'ACC001'
FOR UPDATE;

-- 检查余额是否充足
-- 假设当前余额为 1000.00，转账金额为 500.00

-- 锁定转入账户
SELECT account_id, balance
FROM accounts
WHERE account_id = 'ACC002'
FOR UPDATE;

-- 执行转账
UPDATE accounts
SET balance = balance - 500.00
WHERE account_id = 'ACC001';

UPDATE accounts
SET balance = balance + 500.00
WHERE account_id = 'ACC002';

-- 记录转账日志
INSERT INTO transfer_log (from_account, to_account, amount, transfer_time)
VALUES ('ACC001', 'ACC002', 500.00, NOW());

COMMIT;

分析过程：

问题陈述：确保银行转账操作的原子性和一致性
解决方案：使用 FOR UPDATE 锁定相关账户行
安全保障：防止并发转账导致的余额不一致
适用场景：金融交易、库存管理等需要强一致性的场景

2. FOR NO KEY UPDATE 锁

定义：类似 FOR UPDATE，但允许 FOR KEY SHARE 操作。

业务示例：用户信息更新

sql

-- 业务场景：更新用户个人信息，但不影响主键相关操作
BEGIN;

-- 锁定用户记录进行更新
SELECT user_id, name, email, phone
FROM users
WHERE user_id = 12345
FOR NO KEY UPDATE;

-- 更新非关键字段
UPDATE users
SET name = '张三',
    phone = '13800138000',
    last_updated = NOW()
WHERE user_id = 12345;

COMMIT;

适用场景：

更新非主键字段
需要允许某些并发读取操作
用户资料更新、商品信息修改等

定义：共享锁，阻止修改但允许其他共享读取。

业务示例：报表生成期间的数据一致性

sql

-- 业务场景：生成月度销售报表，确保数据在报表生成期间不被修改
BEGIN;

-- 共享锁定订单数据，确保报表生成期间数据一致
SELECT o.order_id, o.order_date, o.total_amount, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-01-01'
  AND o.order_date < '2024-02-01'
FOR SHARE;

-- 可以有多个报表同时生成（都使用 FOR SHARE）
-- 但阻止数据修改操作

-- 计算汇总数据
-- ... 报表生成逻辑 ...

COMMIT;

定义：最弱的行级锁，只阻止删除和主键修改。

业务示例：外键完整性检查

sql

-- 业务场景：订单详情表引用商品表，确保商品不被删除
BEGIN;

-- 在商品表上获取 KEY SHARE 锁
SELECT product_id, name, price
FROM products
WHERE product_id IN (1001, 1002, 1003)
FOR KEY SHARE;

-- 创建订单详情（外键引用商品）
INSERT INTO order_details (order_id, product_id, quantity, unit_price)
VALUES
    (5001, 1001, 2, 299.00),
    (5001, 1002, 1, 599.00),
    (5001, 1003, 3, 99.00);

COMMIT;

行级锁冲突矩阵

请求锁模式 ↓ / 当前锁模式 →	FOR KEY SHARE	FOR SHARE	FOR NO KEY UPDATE	FOR UPDATE
FOR KEY SHARE	✓	✓	✓	✗
FOR SHARE	✓	✓	✗	✗
FOR NO KEY UPDATE	✓	✗	✗	✗
FOR UPDATE	✗	✗	✗	✗

页级锁

页级锁是 PostgreSQL 内部使用的低级锁定机制，用于控制对缓冲池中数据页的访问。

特点

自动管理：由数据库系统自动获取和释放
短期持有：在读取或更新行后立即释放
透明操作：应用程序开发者通常无需关心

工作机制

死锁检测与处理

死锁的形成

死锁是多个事务相互等待对方持有的锁而形成的循环等待状态。

典型死锁场景

示例 1：表级死锁

sql

-- 会话 1
BEGIN;
LOCK TABLE products IN EXCLUSIVE MODE;
-- 尝试锁定 orders 表
LOCK TABLE orders IN EXCLUSIVE MODE;  -- 等待会话 2 释放

-- 会话 2（同时执行）
BEGIN;
LOCK TABLE orders IN EXCLUSIVE MODE;
-- 尝试锁定 products 表
LOCK TABLE products IN EXCLUSIVE MODE;  -- 等待会话 1 释放

-- 结果：死锁！PostgreSQL 会自动检测并中止其中一个事务

示例 2：行级死锁

sql

-- 业务场景：两个用户同时进行转账操作

-- 事务 1：A 向 B 转账
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
-- 等待锁定账户 B
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';

-- 事务 2：B 向 A 转账（同时执行）
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 'B';
-- 等待锁定账户 A
UPDATE accounts SET balance = balance + 50 WHERE account_id = 'A';

-- 死锁检测：PostgreSQL 检测到循环等待，中止其中一个事务

死锁预防策略

1. 一致的锁定顺序

sql

-- ❌ 错误做法：不同事务以不同顺序锁定资源
-- 事务 1
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';

-- 事务 2
UPDATE accounts SET balance = balance - 50 WHERE account_id = 'B';
UPDATE accounts SET balance = balance + 50 WHERE account_id = 'A';

-- ✅ 正确做法：按照固定顺序锁定资源
-- 事务 1
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';

-- 事务 2
UPDATE accounts SET balance = balance + 50 WHERE account_id = 'A';
UPDATE accounts SET balance = balance - 50 WHERE account_id = 'B';

2. 使用超时机制

sql

-- 设置语句超时
SET statement_timeout = '30s';

-- 设置锁等待超时
SET lock_timeout = '10s';

BEGIN;
-- 如果无法在 10 秒内获取锁，事务将被中止
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
COMMIT;

3. 减少事务持续时间

sql

-- ❌ 长事务增加死锁风险
BEGIN;
-- 复杂的业务逻辑
-- ... 大量操作 ...
-- 用户交互等待
-- ... 更多操作 ...
COMMIT;

-- ✅ 短事务减少死锁风险
-- 操作 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
COMMIT;

-- 操作 2
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';
COMMIT;

死锁处理最佳实践

重试机制：实现自动重试逻辑
错误处理：优雅处理死锁异常
监控告警：监控死锁发生频率
优化设计：重新设计避免死锁的应用架构

sql

-- 死锁重试示例（伪代码）
CREATE OR REPLACE FUNCTION safe_transfer(
    from_account TEXT,
    to_account TEXT,
    amount DECIMAL
) RETURNS BOOLEAN AS $$
DECLARE
    retry_count INTEGER := 0;
    max_retries INTEGER := 3;
BEGIN
    LOOP
        BEGIN
            -- 尝试执行转账
            UPDATE accounts
            SET balance = balance - amount
            WHERE account_id = from_account;

            UPDATE accounts
            SET balance = balance + amount
            WHERE account_id = to_account;

            RETURN TRUE; -- 成功

        EXCEPTION WHEN deadlock_detected THEN
            retry_count := retry_count + 1;
            IF retry_count >= max_retries THEN
                RETURN FALSE; -- 重试失败
            END IF;
            -- 等待随机时间后重试
            PERFORM pg_sleep(random() * 0.1);
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

咨询锁（Advisory Locks）

咨询锁是 PostgreSQL 提供的应用程序定义锁定机制，用于实现自定义的并发控制策略。

咨询锁的特点

应用程序控制：由应用程序决定锁的含义和使用方式
高性能：避免了表结构的膨胀
自动清理：会话结束时自动释放
灵活性：支持会话级和事务级两种模式

咨询锁类型

会话级咨询锁

使用场景：长期资源锁定、应用程序级别的互斥操作

业务示例：分布式任务调度

sql

-- 业务场景：分布式系统中确保定时任务只在一个节点执行

-- 定义任务锁 ID
-- 使用有意义的数字标识不同任务
-- 1001: 日报生成任务
-- 1002: 数据清理任务
-- 1003: 邮件发送任务

-- 尝试获取任务锁
SELECT pg_advisory_lock(1001);  -- 日报生成任务

-- 检查是否成功获取锁
SELECT pg_try_advisory_lock(1001);
-- 返回 true: 成功获取锁，可以执行任务
-- 返回 false: 锁已被其他会话持有，跳过任务

-- 执行任务逻辑
DO $$
BEGIN
    -- 检查锁状态
    IF pg_try_advisory_lock(1001) THEN
        -- 执行日报生成
        RAISE NOTICE '开始生成日报...';

        -- 模拟任务执行
        PERFORM pg_sleep(5);

        -- 任务完成，释放锁
        PERFORM pg_advisory_unlock(1001);
        RAISE NOTICE '日报生成完成';
    ELSE
        RAISE NOTICE '任务已在其他节点执行，跳过';
    END IF;
END $$;

实际应用案例：

sql

-- 创建任务调度表
CREATE TABLE scheduled_tasks (
    task_id INTEGER PRIMARY KEY,
    task_name VARCHAR(100),
    last_run TIMESTAMP,
    next_run TIMESTAMP,
    is_running BOOLEAN DEFAULT FALSE
);

-- 任务执行函数
CREATE OR REPLACE FUNCTION execute_scheduled_task(task_id INTEGER)
RETURNS BOOLEAN AS $$
DECLARE
    task_name VARCHAR(100);
    lock_acquired BOOLEAN;
BEGIN
    -- 尝试获取任务锁
    SELECT pg_try_advisory_lock(task_id) INTO lock_acquired;

    IF NOT lock_acquired THEN
        RAISE NOTICE '任务 % 正在其他会话中执行', task_id;
        RETURN FALSE;
    END IF;

    -- 获取任务信息
    SELECT t.task_name INTO task_name
    FROM scheduled_tasks t
    WHERE t.task_id = execute_scheduled_task.task_id;

    -- 标记任务为运行状态
    UPDATE scheduled_tasks
    SET is_running = TRUE, last_run = NOW()
    WHERE scheduled_tasks.task_id = execute_scheduled_task.task_id;

    RAISE NOTICE '开始执行任务: %', task_name;

    -- 根据任务类型执行不同逻辑
    CASE task_id
        WHEN 1001 THEN
            -- 日报生成逻辑
            PERFORM generate_daily_report();
        WHEN 1002 THEN
            -- 数据清理逻辑
            PERFORM cleanup_old_data();
        WHEN 1003 THEN
            -- 邮件发送逻辑
            PERFORM send_pending_emails();
    END CASE;

    -- 更新任务状态
    UPDATE scheduled_tasks
    SET is_running = FALSE,
        last_run = NOW(),
        next_run = NOW() + INTERVAL '1 day'
    WHERE scheduled_tasks.task_id = execute_scheduled_task.task_id;

    -- 释放锁
    PERFORM pg_advisory_unlock(task_id);

    RAISE NOTICE '任务 % 执行完成', task_name;
    RETURN TRUE;

EXCEPTION WHEN OTHERS THEN
    -- 异常处理：确保锁被释放
    PERFORM pg_advisory_unlock(task_id);
    UPDATE scheduled_tasks
    SET is_running = FALSE
    WHERE scheduled_tasks.task_id = execute_scheduled_task.task_id;

    RAISE NOTICE '任务 % 执行失败: %', task_name, SQLERRM;
    RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

事务级咨询锁

使用场景：短期资源锁定、事务内的资源同步

业务示例：序列号生成

sql

-- 业务场景：生成全局唯一的订单号
-- 确保在并发环境下不会产生重复的订单号

CREATE TABLE order_sequence (
    seq_name VARCHAR(50) PRIMARY KEY,
    current_value BIGINT NOT NULL DEFAULT 0,
    increment_by INTEGER NOT NULL DEFAULT 1
);

-- 初始化序列
INSERT INTO order_sequence (seq_name, current_value)
VALUES ('ORDER_SEQ', 100000);

-- 获取下一个订单号的函数
CREATE OR REPLACE FUNCTION get_next_order_number()
RETURNS BIGINT AS $$
DECLARE
    next_number BIGINT;
    lock_key BIGINT := 999999; -- 序列锁的标识
BEGIN
    -- 在事务内获取咨询锁
    PERFORM pg_advisory_xact_lock(lock_key);

    -- 获取并更新序列值
    UPDATE order_sequence
    SET current_value = current_value + increment_by
    WHERE seq_name = 'ORDER_SEQ'
    RETURNING current_value INTO next_number;

    RETURN next_number;

    -- 事务结束时锁自动释放
END;
$$ LANGUAGE plpgsql;

-- 使用示例
BEGIN;

-- 生成新订单
INSERT INTO orders (
    order_number,
    customer_id,
    order_date,
    total_amount
) VALUES (
    'ORD' || get_next_order_number(),  -- 生成唯一订单号
    12345,
    NOW(),
    299.99
);

COMMIT;

咨询锁函数详解

主要函数列表

函数名	锁类型	说明
`pg_advisory_lock(key)`	会话级	阻塞式获取锁
`pg_try_advisory_lock(key)`	会话级	非阻塞式尝试获取锁
`pg_advisory_unlock(key)`	会话级	释放锁
`pg_advisory_xact_lock(key)`	事务级	阻塞式获取事务锁
`pg_try_advisory_xact_lock(key)`	事务级	非阻塞式尝试获取事务锁

高级用法示例

多层锁定策略：

sql

-- 业务场景：分布式缓存更新
-- 使用多个咨询锁实现分层锁定

CREATE OR REPLACE FUNCTION update_product_cache(product_id INTEGER)
RETURNS BOOLEAN AS $$
DECLARE
    category_lock_key BIGINT;
    product_lock_key BIGINT;
    category_id INTEGER;
BEGIN
    -- 获取商品分类
    SELECT c.category_id INTO category_id
    FROM products p
    JOIN categories c ON p.category_id = c.category_id
    WHERE p.product_id = update_product_cache.product_id;

    -- 计算锁键值
    category_lock_key := 100000 + category_id;  -- 分类锁
    product_lock_key := 200000 + product_id;    -- 商品锁

    -- 按顺序获取锁，避免死锁
    IF pg_try_advisory_lock(category_lock_key) THEN
        IF pg_try_advisory_lock(product_lock_key) THEN
            -- 执行缓存更新
            RAISE NOTICE '更新商品 % 的缓存', product_id;

            -- 模拟缓存更新操作
            PERFORM pg_sleep(0.1);

            -- 释放锁
            PERFORM pg_advisory_unlock(product_lock_key);
            PERFORM pg_advisory_unlock(category_lock_key);

            RETURN TRUE;
        ELSE
            -- 释放已获取的分类锁
            PERFORM pg_advisory_unlock(category_lock_key);
            RETURN FALSE;
        END IF;
    ELSE
        RETURN FALSE;
    END IF;
END;
$$ LANGUAGE plpgsql;

咨询锁的限制和注意事项

1. 内存限制

sql

-- 查看当前锁配置
SHOW max_locks_per_transaction;
SHOW max_connections;

-- 计算最大锁数量
-- 最大锁数 = max_locks_per_transaction * max_connections

2. 锁键值管理

sql

-- 建议的锁键值分配策略
-- 1-10000:     系统级锁
-- 10001-20000: 业务模块 A
-- 20001-30000: 业务模块 B
-- 30001-40000: 业务模块 C

-- 使用枚举或常量管理锁键值
CREATE TABLE advisory_lock_keys (
    lock_name VARCHAR(50) PRIMARY KEY,
    lock_key BIGINT UNIQUE NOT NULL,
    description TEXT
);

INSERT INTO advisory_lock_keys VALUES
('DAILY_REPORT_TASK', 1001, '日报生成任务锁'),
('DATA_CLEANUP_TASK', 1002, '数据清理任务锁'),
('EMAIL_SEND_TASK', 1003, '邮件发送任务锁'),
('ORDER_SEQUENCE', 999999, '订单序列生成锁');

3. 危险用法避免

sql

-- ❌ 危险：LIMIT 子句可能导致意外锁定
SELECT pg_advisory_lock(id)
FROM products
WHERE category_id > 100
LIMIT 10;  -- 可能锁定超过 10 个产品

-- ✅ 安全：使用子查询明确控制锁定范围
SELECT pg_advisory_lock(p.id)
FROM (
    SELECT id
    FROM products
    WHERE category_id > 100
    LIMIT 10
) p;

锁监控和诊断

查看当前锁状态

sql

-- 查看所有当前持有的锁
SELECT
    locktype,
    database,
    relation::regclass,
    page,
    tuple,
    virtualxid,
    transactionid,
    classid,
    objid,
    objsubid,
    virtualtransaction,
    pid,
    mode,
    granted,
    fastpath
FROM pg_locks
ORDER BY pid, locktype;

-- 查看阻塞关系
SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
    ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
    ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

锁等待分析

sql

-- 创建锁监控视图
CREATE VIEW lock_monitor AS
SELECT
    l.locktype,
    l.mode,
    l.granted,
    l.pid,
    a.usename,
    a.query,
    a.query_start,
    a.state,
    CASE
        WHEN l.relation IS NOT NULL THEN l.relation::regclass::text
        ELSE l.locktype
    END AS object_name
FROM pg_locks l
LEFT JOIN pg_stat_activity a ON l.pid = a.pid
WHERE a.pid IS NOT NULL
ORDER BY l.granted, a.query_start;

-- 查看当前锁等待情况
SELECT * FROM lock_monitor WHERE NOT granted;

最佳实践总结

1. 锁定策略选择

TIP

选择指南

读多写少：优先使用 ACCESS SHARE 和 ROW SHARE 锁
高并发写入：尽量使用行级锁而非表级锁
批量操作：考虑使用更强的锁级别确保一致性
维护操作：合理安排 ACCESS EXCLUSIVE 锁的使用时间

2. 死锁预防

sql

-- 制定锁定顺序规范
-- 示例：按表名字母顺序、按主键顺序锁定资源

-- 错误做法
BEGIN;
UPDATE table_z SET ... WHERE id = 1;
UPDATE table_a SET ... WHERE id = 2;
COMMIT;

-- 正确做法
BEGIN;
UPDATE table_a SET ... WHERE id = 2;
UPDATE table_z SET ... WHERE id = 1;
COMMIT;

3. 事务优化

sql

-- 缩短事务持续时间
-- 将长时间运行的操作移出事务

-- ❌ 不推荐
BEGIN;
-- 数据修改
UPDATE products SET price = price * 1.1;
-- 长时间运算
SELECT complex_calculation();
-- 用户交互
-- 等待用户确认...
COMMIT;

-- ✅ 推荐
-- 预先计算
SELECT complex_calculation();

-- 短事务
BEGIN;
UPDATE products SET price = price * 1.1;
COMMIT;

4. 监控和告警

建立锁监控机制，及时发现和处理锁等待问题：

sql

-- 创建锁监控函数
CREATE OR REPLACE FUNCTION check_lock_waits()
RETURNS TABLE(
    waiting_pid INTEGER,
    waiting_query TEXT,
    waiting_duration INTERVAL,
    blocking_pid INTEGER,
    blocking_query TEXT
) AS $$
SELECT
    blocked_locks.pid,
    blocked_activity.query,
    NOW() - blocked_activity.query_start,
    blocking_locks.pid,
    blocking_activity.query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
    ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
    ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted
  AND blocked_activity.state = 'active';
$$ LANGUAGE sql;

总结

PostgreSQL 的显式锁定机制提供了强大而灵活的并发控制能力。通过合理使用不同级别的锁，可以在保证数据一致性的同时最大化系统的并发性能。

关键要点：

理解锁层次：掌握不同锁级别的特点和适用场景
避免死锁：制定一致的锁定顺序和超时策略
优化事务：保持事务简短，减少锁持有时间
监控锁状态：建立有效的锁监控和告警机制
合理使用咨询锁：在传统锁不适用的场景下灵活应用

通过深入理解和实践这些概念，可以构建高效、稳定的数据库应用系统。

PostgreSQL 显式锁定完全指南 ​

概述 ​

锁定机制架构 ​

表级锁详解 ​

锁定模式层次结构 ​

1. ACCESS SHARE 锁 ​

2. ROW SHARE 锁 ​

3. ROW EXCLUSIVE 锁 ​

4. SHARE UPDATE EXCLUSIVE 锁 ​

5. SHARE 锁 ​

6. SHARE ROW EXCLUSIVE 锁 ​

7. EXCLUSIVE 锁 ​

8. ACCESS EXCLUSIVE 锁 ​

锁冲突矩阵 ​

表级锁冲突关系 ​

行级锁详解 ​

行级锁模式 ​

1. FOR UPDATE 锁 ​

2. FOR NO KEY UPDATE 锁 ​

3. FOR SHARE 锁 ​

4. FOR KEY SHARE 锁 ​

行级锁冲突矩阵 ​

页级锁 ​

特点 ​

工作机制 ​

死锁检测与处理 ​

死锁的形成 ​

典型死锁场景 ​

死锁预防策略 ​

1. 一致的锁定顺序 ​

2. 使用超时机制 ​

3. 减少事务持续时间 ​

死锁处理最佳实践 ​

咨询锁（Advisory Locks） ​

咨询锁的特点 ​

咨询锁类型 ​

会话级咨询锁 ​

事务级咨询锁 ​

咨询锁函数详解 ​

主要函数列表 ​

高级用法示例 ​

咨询锁的限制和注意事项 ​

1. 内存限制 ​

2. 锁键值管理 ​

3. 危险用法避免 ​

锁监控和诊断 ​

查看当前锁状态 ​

锁等待分析 ​

最佳实践总结 ​

1. 锁定策略选择 ​

2. 死锁预防 ​

3. 事务优化 ​

4. 监控和告警 ​

总结 ​

PostgreSQL 显式锁定完全指南

概述

锁定机制架构

表级锁详解

锁定模式层次结构

1. ACCESS SHARE 锁

2. ROW SHARE 锁

3. ROW EXCLUSIVE 锁

4. SHARE UPDATE EXCLUSIVE 锁

5. SHARE 锁

6. SHARE ROW EXCLUSIVE 锁

7. EXCLUSIVE 锁

8. ACCESS EXCLUSIVE 锁

锁冲突矩阵

表级锁冲突关系

行级锁详解

行级锁模式

1. FOR UPDATE 锁

2. FOR NO KEY UPDATE 锁

3. FOR SHARE 锁

4. FOR KEY SHARE 锁

行级锁冲突矩阵

页级锁

特点

工作机制

死锁检测与处理

死锁的形成

典型死锁场景

死锁预防策略

1. 一致的锁定顺序

2. 使用超时机制

3. 减少事务持续时间

死锁处理最佳实践

咨询锁（Advisory Locks）

咨询锁的特点

咨询锁类型

会话级咨询锁

事务级咨询锁

咨询锁函数详解

主要函数列表

高级用法示例

咨询锁的限制和注意事项

1. 内存限制

2. 锁键值管理

3. 危险用法避免

锁监控和诊断

查看当前锁状态

锁等待分析

最佳实践总结

1. 锁定策略选择

2. 死锁预防

3. 事务优化

4. 监控和告警

总结