PostgreSQL锁机制

概述

锁是数据库用于控制并发访问的机制，确保多个事务同时操作数据时的一致性和完整性。PostgreSQL提供多种类型的锁来满足不同场景的需求。

锁的类型

表级锁

锁模式	说明	冲突模式
ACCESS SHARE	SELECT操作获取	ACCESS EXCLUSIVE
ROW SHARE	SELECT FOR UPDATE/SHARE	EXCLUSIVE, ACCESS EXCLUSIVE
ROW EXCLUSIVE	UPDATE/DELETE/INSERT	SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
SHARE UPDATE EXCLUSIVE	VACUUM, ANALYZE	多种
SHARE	CREATE INDEX	ROW EXCLUSIVE及以上
SHARE ROW EXCLUSIVE	如CREATE TRIGGER	多种
EXCLUSIVE	刷新物化视图	除ACCESS SHARE外全部
ACCESS EXCLUSIVE	ALTER TABLE, DROP TABLE	全部

行级锁

锁模式	说明
FOR UPDATE	最强锁，阻止其他事务修改或锁定
FOR NO KEY UPDATE	允许其他事务获取FOR KEY SHARE
FOR SHARE	阻止UPDATE/DELETE，允许SELECT
FOR KEY SHARE	最弱锁，只阻止DELETE和主键更新

显式锁定

表级锁

-- 锁定整个表
LOCK TABLE users IN ACCESS EXCLUSIVE MODE;

-- 不同锁模式
LOCK TABLE users IN SHARE MODE;
LOCK TABLE users IN EXCLUSIVE MODE;
LOCK TABLE users IN ROW EXCLUSIVE MODE;

-- 带NOWAIT选项（无法获取锁时立即报错）
LOCK TABLE users IN ACCESS EXCLUSIVE MODE NOWAIT;

行级锁

-- SELECT FOR UPDATE（排他锁）
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- SELECT FOR SHARE（共享锁）
SELECT * FROM users WHERE id = 1 FOR SHARE;

-- NOWAIT选项
SELECT * FROM users WHERE id = 1 FOR UPDATE NOWAIT;

-- SKIP LOCKED（跳过已锁定的行）
SELECT * FROM users WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 10;

咨询锁（Advisory Locks）

咨询锁是应用程序级别的锁，PostgreSQL不强制其语义。

会话级咨询锁

-- 获取锁
SELECT pg_advisory_lock(12345);

-- 尝试获取锁（非阻塞）
SELECT pg_try_advisory_lock(12345);

-- 释放锁
SELECT pg_advisory_unlock(12345);

-- 释放所有会话锁
SELECT pg_advisory_unlock_all();

事务级咨询锁

-- 获取事务级锁（事务结束自动释放）
SELECT pg_advisory_xact_lock(12345);

-- 尝试获取事务级锁
SELECT pg_try_advisory_xact_lock(12345);

咨询锁应用示例

-- 使用咨询锁防止并发任务处理
CREATE OR REPLACE FUNCTION process_task(task_id BIGINT)
RETURNS BOOLEAN AS $$
BEGIN
    -- 尝试获取锁
    IF NOT pg_try_advisory_xact_lock(task_id) THEN
        RETURN FALSE;  -- 任务正在被其他进程处理
    END IF;

    -- 处理任务
    UPDATE tasks SET status = 'processing' WHERE id = task_id;
    -- ... 其他处理逻辑

    UPDATE tasks SET status = 'completed' WHERE id = task_id;
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

死锁

死锁示例

-- 事务1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 等待事务2释放id=2的锁
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 事务2（同时）
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
-- 等待事务1释放id=1的锁
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
-- 死锁！

避免死锁

-- 方法1：按固定顺序访问资源
BEGIN;
-- 始终先锁定ID较小的账户
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 方法2：使用SELECT FOR UPDATE预先锁定
BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

死锁检测配置

-- 查看死锁超时设置
SHOW deadlock_timeout;

-- 设置死锁检测超时（默认1秒）
SET deadlock_timeout = '2s';

查看锁信息

查看当前锁

-- 查看所有锁
SELECT * FROM pg_locks;

-- 查看锁定的表
SELECT
    l.locktype,
    l.mode,
    l.granted,
    c.relname as table_name,
    a.usename,
    a.query,
    a.state
FROM pg_locks l
JOIN pg_class c ON l.relation = c.oid
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE c.relkind = 'r';

-- 查看等待锁的进程
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked
    ON blocked_locks.pid = blocked.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.relation = blocked_locks.relation
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking
    ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;

终止阻塞进程

-- 取消查询
SELECT pg_cancel_backend(pid);

-- 终止连接
SELECT pg_terminate_backend(pid);

锁超时设置

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

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

-- 查看当前设置
SHOW lock_timeout;

乐观锁 vs 悲观锁

悲观锁（SELECT FOR UPDATE）

BEGIN;
SELECT * FROM products WHERE id = 1 FOR UPDATE;
-- 其他事务无法修改该行
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

乐观锁（版本号）

-- 使用版本号实现乐观锁
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;

-- 检查是否更新成功
-- 如果affected rows = 0，说明版本冲突，需要重试

最佳实践

1. 尽量缩短事务时间：减少锁持有时间
2. 避免长事务：长事务会阻塞其他操作
3. 使用合适的隔离级别：不要过度使用SERIALIZABLE
4. 按固定顺序访问资源：防止死锁
5. 使用NOWAIT或超时：避免无限等待
6. 监控锁等待：及时发现锁问题
7. 考虑乐观锁：高并发读场景更高效