企业官网建设流程全解析

上一篇【第22篇】MVCC多版本并发控制——PostgreSQL并发性能的秘密
下一篇【第24篇】VACUUM与垃圾回收——数据库健康运行的守护者

你有没有遇到过这种情况：一个简单的 ALTER TABLE 挂起了十分钟，生产库的查询全部堆积，应用响应超时……最后发现，是一个"不起眼"的锁等待引发了雪崩。本文带你彻底搞懂 PostgreSQL 的锁机制。

一、为什么需要锁？

MVCC 解决了读写不互相阻塞的问题，但某些操作仍然需要锁来保证正确性：

• 两个事务同时对同一行执行UPDATE，必须串行化，否则会丢失更新
• ALTER TABLE需要确保没有其他事务在操作这张表
• CREATE INDEX CONCURRENTLY需要监视数据变化

PostgreSQL 的锁体系分为两大层：表级锁和行级锁。

二、表级锁（Table-Level Locks）

2.1 八种表级锁模式

PostgreSQL 表级锁（从最弱到最强）： ┌────────────────────────────────────────────────────────────┐ │ 1. ACCESS SHARE SELECT 时自动获取（最弱） │ │ 2. ROW SHARE SELECT FOR UPDATE/SHARE 时获取 │ │ 3. ROW EXCLUSIVE INSERT/UPDATE/DELETE 时获取 │ │ 4. SHARE UPDATE EXCLUSIVE VACUUM/ANALYZE/部分DDL 时获取 │ │ 5. SHARE CREATE INDEX（非并发）时获取 │ │ 6. SHARE ROW EXCLUSIVE CREATE TRIGGER/部分 ALTER 时获取 │ │ 7. EXCLUSIVE 稀少使用 │ │ 8. ACCESS EXCLUSIVE DROP/TRUNCATE/大多数DDL 时获取 │ └────────────────────────────────────────────────────────────┘

2.2 锁兼容性矩阵

AS RS RE SUE S SRE E AE AS ✓ ✓ ✓ ✓ ✓ ✓ ✓ ✗ RS ✓ ✓ ✓ ✓ ✓ ✓ ✗ ✗ RE ✓ ✓ ✓ ✓ ✗ ✗ ✗ ✗ SUE ✓ ✓ ✓ ✗ ✗ ✗ ✗ ✗ S ✓ ✓ ✗ ✗ ✓ ✗ ✗ ✗ SRE ✓ ✓ ✗ ✗ ✗ ✗ ✗ ✗ E ✓ ✗ ✗ ✗ ✗ ✗ ✗ ✗ AE ✗ ✗ ✗ ✗ ✗ ✗ ✗ ✗ ✓ = 兼容（可以同时持有） ✗ = 冲突（必须等待） AS=ACCESS SHARE, RS=ROW SHARE, RE=ROW EXCLUSIVE, SUE=SHARE UPDATE EXCLUSIVE, S=SHARE, SRE=SHARE ROW EXCLUSIVE, E=EXCLUSIVE, AE=ACCESS EXCLUSIVE

2.3 常见操作对应的锁

-- 查询：ACCESS SHARE（最温和，几乎不阻塞任何操作）SELECT*FROMorders;-- DML 写操作：ROW EXCLUSIVE（与 SHARE 锁冲突）INSERTINTOorders...;UPDATEordersSET...;DELETEFROMordersWHERE...;-- CREATE INDEX（非并发）：SHARE（阻塞写入）CREATEINDEXidxONorders(created_at);-- VACUUM/ANALYZE：SHARE UPDATE EXCLUSIVE（可以读写，但阻塞 DDL）VACUUM orders;ANALYZEorders;-- DROP TABLE / TRUNCATE：ACCESS EXCLUSIVE（阻塞一切！）DROPTABLEorders;TRUNCATEorders;ALTERTABLEordersADDCOLUMN...;-- 大多数 ALTER 也是 AE 锁

2.4 手动加表锁

-- 显式加表锁LOCKTABLEordersINACCESS EXCLUSIVEMODE;LOCKTABLEordersINSHAREMODE;LOCKTABLEordersINROWEXCLUSIVEMODENOWAIT;-- 获取不到立即报错-- 示例：批量迁移数据时锁定源表BEGIN;LOCKTABLEorders_2023INSHAREMODE;-- 防止其他写操作INSERTINTOorders_archiveSELECT*FROMorders_2023;COMMIT;-- 释放锁

三、行级锁（Row-Level Locks）

3.1 SELECT FOR UPDATE / SHARE

-- SELECT FOR UPDATE：锁定行，防止其他事务修改BEGIN;SELECT*FROMaccountsWHEREuser_id=1FORUPDATE;-- 此时其他事务无法 UPDATE/DELETE 这行，也无法再 SELECT FOR UPDATE-- 处理业务逻辑UPDATEaccountsSETbalance=balance-100WHEREuser_id=1;COMMIT;-- 释放锁-- SELECT FOR SHARE：锁定行，允许其他读，但不允许写SELECT*FROMordersWHEREid=100FORSHARE;

3.2 FOR UPDATE 的变体

-- NOWAIT：获取不到锁立即报错（不等待）SELECT*FROMaccountsWHEREuser_id=1FORUPDATENOWAIT;-- ERROR: could not obtain lock on row in relation "accounts"-- SKIP LOCKED：跳过已锁定的行（用于任务队列）SELECT*FROMtasksWHEREstatus='pending'ORDERBYcreated_atLIMIT10FORUPDATESKIP LOCKED;-- 不等待锁，直接跳过已被其他事务锁定的行

3.3 SKIP LOCKED 实战——并发任务队列

-- 创建任务表CREATETABLEtask_queue(idSERIALPRIMARYKEY,task_dataTEXT,statusVARCHAR(20)DEFAULT'pending',created_atTIMESTAMPDEFAULTNOW());-- 多个 Worker 并发抢占任务（不会重复处理同一任务）BEGIN;WITHnext_taskAS(SELECTidFROMtask_queueWHEREstatus='pending'ORDERBYcreated_atLIMIT1FORUPDATESKIP LOCKED-- 关键：跳过已被其他 Worker 锁定的任务)UPDATEtask_queueSETstatus='processing'WHEREid=(SELECTidFROMnext_task)RETURNING*;-- 每个 Worker 抢到不同的任务，互不干扰COMMIT;

四、死锁——两个事务的"相互等待"

4.1 死锁的产生

死锁场景： ┌─────────────────────────────────────────────────────────────┐ │ 事务 A 事务 B │ │ ───────────────────── ───────────────────── │ │ UPDATE accounts │ │ SET balance=... UPDATE accounts │ │ WHERE id=1; ──锁定行1→ SET balance=... │ │ WHERE id=2; ──锁定行2→ │ │ UPDATE accounts ↑ │ │ SET balance=... ─等待行2─┘ UPDATE accounts │ │ WHERE id=2; ↑ SET balance=... │ │ WHERE id=1; ─等待行1─┘ │ │ 循环等待！死锁！ │ └─────────────────────────────────────────────────────────────┘

-- 复现死锁-- 会话 1BEGIN;UPDATEaccountsSETbalance=balance-100WHEREid=1;-- 会话 2（同时）BEGIN;UPDATEaccountsSETbalance=balance-200WHEREid=2;-- 会话 1（继续）UPDATEaccountsSETbalance=balance+100WHEREid=2;-- 等待会话2释放-- 会话 2（继续）UPDATEaccountsSETbalance=balance+200WHEREid=1;-- 等待会话1释放-- 此时 PostgreSQL 检测到死锁，自动选择一个事务回滚：-- ERROR: deadlock detected-- DETAIL: Process 12345 waits for ShareLock on transaction 67890

4.2 死锁日志分析

# 日志中的死锁信息（postgresql.log） ERROR: deadlock detected DETAIL: Process 1234 waits for ShareLock on transaction 5678; blocked by process 5678. Process 5678 waits for ShareLock on transaction 1234; blocked by process 1234. HINT: See server log for query details. CONTEXT: while updating tuple (0,1) in relation "accounts"

4.3 防止死锁的技巧

-- 技巧1：按固定顺序访问资源-- 错误：有时先锁 id=1，有时先锁 id=2UPDATEaccountsSETbalance=balance-100WHEREid=user_a;UPDATEaccountsSETbalance=balance+100WHEREid=user_b;-- 正确：总是按 id 从小到大锁定-- 在应用层确保：min(user_a, user_b) 先操作UPDATEaccountsSETbalance=balance-100WHEREid=LEAST(user_a,user_b);UPDATEaccountsSETbalance=balance+100WHEREid=GREATEST(user_a,user_b);-- 技巧2：使用 SELECT FOR UPDATE 一次性锁定所有需要的行BEGIN;SELECT*FROMaccountsWHEREidIN(user_a,user_b)ORDERBYid-- 按固定顺序锁定，防止死锁FORUPDATE;UPDATEaccountsSETbalance=balance-100WHEREid=user_a;UPDATEaccountsSETbalance=balance+100WHEREid=user_b;COMMIT;-- 技巧3：减少事务的持锁时间-- 将业务逻辑移到事务外处理，只把最终的写操作放在事务里

五、锁监控——找出谁在阻塞谁

5.1 pg_locks 视图

-- 查看当前所有锁SELECTpid,locktype,relation::regclass,mode,grantedFROMpg_locksWHERErelationISNOTNULLORDERBYrelation,pid;-- 查看被锁阻塞的查询SELECTblocked_locks.pidASblocked_pid,blocked_activity.usenameASblocked_user,blocking_locks.pidASblocking_pid,blocking_activity.usenameASblocking_user,blocked_activity.queryASblocked_statement,blocking_activity.queryAScurrent_statement_in_blocking_processFROMpg_catalog.pg_locks blocked_locksJOINpg_catalog.pg_stat_activity blocked_activityONblocked_activity.pid=blocked_locks.pidJOINpg_catalog.pg_locks blocking_locksONblocking_locks.locktype=blocked_locks.locktypeANDblocking_locks.relationISNOTDISTINCTFROMblocked_locks.relationANDblocking_locks.pageISNOTDISTINCTFROMblocked_locks.pageANDblocking_locks.tupleISNOTDISTINCTFROMblocked_locks.tupleANDblocking_locks.virtualxidISNOTDISTINCTFROMblocked_locks.virtualxidANDblocking_locks.transactionidISNOTDISTINCTFROMblocked_locks.transactionidANDblocking_locks.pid!=blocked_locks.pidJOINpg_catalog.pg_stat_activity blocking_activityONblocking_activity.pid=blocking_locks.pidWHERENOTblocked_locks.granted;

5.2 实用的锁等待查询

-- 一键查看锁等待链（最常用）WITHRECURSIVE waiting_chainAS(SELECTpg_blocking_pids(pid)ASblocking_pids,pidASwaiting_pid,queryASwaiting_query,wait_event_type,wait_eventFROMpg_stat_activityWHEREcardinality(pg_blocking_pids(pid))>0)SELECTwaiting_pid,blocking_pids,wait_event_type,wait_event,left(waiting_query,80)ASqueryFROMwaiting_chain;

-- 快速查看：哪个表被锁了，谁持有锁SELECTnspname||'.'||relnameAStable_name,mode,pid,usename,state,left(query,60)ASqueryFROMpg_locks lJOINpg_class cONl.relation=c.oidJOINpg_namespace nONc.relnamespace=n.oidJOINpg_stat_activity aONl.pid=a.pidWHERErelkind='r'ANDNOTgranted-- 等待中的锁ORDERBYtable_name;

5.3 处理锁等待

-- 方式1：等待持锁事务自然结束-- 方式2：取消持锁进程的当前查询（优雅）SELECTpg_cancel_backend(blocking_pid);-- 方式3：强制终止持锁进程（暴力）SELECTpg_terminate_backend(blocking_pid);-- 方式4：批量处理超过 N 分钟的锁等待SELECTpg_cancel_backend(pid)FROMpg_stat_activityWHEREstate='active'ANDwait_event_type='Lock'ANDnow()-query_start>INTERVAL'5 minutes';

六、咨询锁（Advisory Lock）——应用层的分布式锁

咨询锁是 PostgreSQL 提供的一种"应用层语义锁"，不与任何数据库对象绑定，完全由应用控制。

6.1 基本用法

-- 会话级别的咨询锁（直到会话结束才释放）SELECTpg_advisory_lock(12345);-- 获取独占锁（阻塞等待）SELECTpg_try_advisory_lock(12345);-- 尝试获取，失败立即返回 falseSELECTpg_advisory_unlock(12345);-- 释放锁-- 共享咨询锁（多个会话可同时持有）SELECTpg_advisory_lock_shared(12345);SELECTpg_try_advisory_lock_shared(12345);SELECTpg_advisory_unlock_shared(12345);-- 事务级别的咨询锁（事务结束自动释放）SELECTpg_advisory_xact_lock(12345);SELECTpg_try_advisory_xact_lock(12345);

6.2 实战：防止重复任务执行

-- 场景：定时任务，确保同一时间只有一个实例运行DO$$DECLARElock_idBIGINT:=987654321;-- 任务的唯一标识got_lockBOOLEAN;BEGINgot_lock :=pg_try_advisory_lock(lock_id);IFNOTgot_lockTHENRAISE NOTICE'任务已在运行中，跳过';RETURN;ENDIF;-- 执行任务逻辑RAISE NOTICE'开始执行任务...';PERFORM pg_sleep(10);-- 模拟任务执行RAISE NOTICE'任务完成';-- 释放锁PERFORM pg_advisory_unlock(lock_id);END;$$;

6.3 使用字符串作为锁 ID

-- hashtext 函数将字符串转换为整数SELECTpg_advisory_lock(hashtext('daily_report_job'));SELECTpg_advisory_lock(hashtext('user_sync_job'));-- 更好的方式：使用两个 INT4 构成唯一锁 ID（避免哈希碰撞）SELECTpg_advisory_lock(1,2);-- 用模块ID + 任务ID

七、锁超时配置——避免无限等待

7.1 锁超时参数

-- 全局设置（postgresql.conf 或 ALTER SYSTEM）ALTERSYSTEMSETlock_timeout='30s';ALTERSYSTEMSETdeadlock_timeout='1s';-- 死锁检测间隔（默认 1 秒）-- 会话级设置SETlock_timeout='5s';-- 等待锁超过 5 秒报错SETlock_timeout='0';-- 0 = 无限等待（默认）-- 事务级设置BEGIN;SETLOCALlock_timeout='3s';UPDATEaccountsSETbalance=0WHEREid=1;-- 如果 3 秒内获取不到行锁：-- ERROR: canceling statement due to lock timeoutCOMMIT;

7.2 statement_timeout vs lock_timeout

-- statement_timeout：整个语句的最大执行时间SETstatement_timeout='60s';-- 超过 60 秒的查询会被强制取消（包括等待锁的时间）-- lock_timeout：等待锁的最大时间SETlock_timeout='10s';-- 只有等待锁的时间超过才取消，不限制查询本身的执行时间-- 推荐：同时设置两个超时SETstatement_timeout='30s';SETlock_timeout='5s';-- lock_timeout 应 < statement_timeout

八、常见锁问题与解决方案

8.1 ALTER TABLE 引发的锁等待雪崩

问题场景： 1. 大量查询持有 ACCESS SHARE 锁 2. DBA 执行 ALTER TABLE（需要 ACCESS EXCLUSIVE 锁） 3. ALTER TABLE 等待所有现有查询完成 4. 新来的查询都要等 ALTER TABLE 完成 5. 连接池耗尽，系统雪崩！

-- 解决方案1：设置 lock_timeout 避免 ALTER 无限等待SETlock_timeout='3s';ALTERTABLEordersADDCOLUMNnoteTEXT;-- 3 秒内获取不到锁就报错，重试-- 解决方案2：使用 CREATE INDEX CONCURRENTLY 代替 CREATE INDEX-- 普通方式（锁表）：CREATEINDEXidx_orders_dateONorders(created_at);-- 锁表期间无法写入-- 并发方式（不锁表）：CREATEINDEXCONCURRENTLY idx_orders_dateONorders(created_at);-- 只需较弱的锁-- 解决方案3：分批执行 DDL（如分区表迁移）-- 将 LOCK TABLE 分成小批次，减少锁持有时间

8.2 长事务导致的锁等待

-- 找出长时间持有锁的事务SELECTpid,usename,state,now()-xact_startASduration,left(query,100)ASqueryFROMpg_stat_activityWHEREstateIN('active','idle in transaction')ANDnow()-xact_start>INTERVAL'2 minutes'ORDERBYdurationDESC;-- 找出等待锁超过 1 分钟的查询SELECTpid,wait_event_type,wait_event,now()-state_changeASwaiting_duration,left(query,100)ASqueryFROMpg_stat_activityWHEREwait_event_type='Lock'ANDnow()-state_change>INTERVAL'1 minute';

九、总结

锁使用快速参考 ┌────────────────────────────────────────────────────────────┐ │ 场景 推荐方案 │ │─────────────────────────────────────────────────────────────│ │ 并发抢占任务队列 SELECT FOR UPDATE SKIP LOCKED │ │ 悲观锁防止并发修改 SELECT FOR UPDATE │ │ 乐观锁（版本号控制） UPDATE WHERE version = ? │ │ 应用层分布式互斥 pg_advisory_lock │ │ 防止 ALTER TABLE 雪崩 设置 lock_timeout │ │ 创建索引不锁表 CREATE INDEX CONCURRENTLY │ │ 防止死锁 按固定顺序访问资源 │ │ 监控锁等待 pg_stat_activity + pg_locks │ └────────────────────────────────────────────────────────────┘

锁机制是 PostgreSQL 并发控制的另一面。MVCC 让大多数读写无需锁，而锁机制保证了真正需要串行化的操作的正确性。理解锁，才能写出不会在生产环境造成雪崩的数据库代码。

下一篇，我们深入探讨VACUUM 与垃圾回收机制，揭秘如何让 PostgreSQL 保持长期健康运行。

标签：PostgreSQL、锁机制、死锁、MVCC、咨询锁、SELECT FOR UPDATE、性能优化

上一篇【第22篇】MVCC多版本并发控制——PostgreSQL并发性能的秘密
下一篇【第24篇】VACUUM与垃圾回收——数据库健康运行的守护者