PostgreSQL中如何判断锁是否可获取及ALTER TABLE锁的获取状态？

一、判断某一锁是否可以被获取 #

PostgreSQL没有直接提供“预检查锁能否获取”的内置函数，但可以通过查询系统视图间接判断，核心逻辑是检查目标对象上是否存在与目标锁类型冲突的已持有锁：

1. 确定目标对象的OID
   先获取要操作的表、索引等对象的OID，以查询表的OID为例：

   SELECT oid FROM pg_class 
   WHERE relname = 'your_table_name' 
     AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'your_schema');
   

2. 查询当前持有锁并判断冲突
   查询pg_locks视图，筛选目标对象的锁记录，结合PostgreSQL的锁冲突规则判断：

   SELECT locktype, mode, pid
   FROM pg_locks
   WHERE relation = 'your_table_oid'::oid
     AND mode != 'ACCESS EXCLUSIVE'; -- 假设你要获取的是ACCESS EXCLUSIVE锁，排除自身可能持有的锁
   

   核心冲突规则参考：

   • ACCESS EXCLUSIVE锁与所有其他锁类型冲突
   • SHARE锁与ROW EXCLUSIVE、SHARE ROW EXCLUSIVE、ACCESS EXCLUSIVE冲突
   • ROW EXCLUSIVE锁与SHARE、SHARE ROW EXCLUSIVE、ACCESS EXCLUSIVE冲突
     如果查询结果中存在与目标锁冲突的mode，说明当前无法立即获取该锁，需要等待其他会话释放冲突锁。

二、判断ALTER TABLE ADD CONSTRAINT是否会立即获取锁 #

ALTER TABLE ADD CONSTRAINT的锁类型取决于约束类型和是否需要验证现有数据：

• 默认验证数据场景：添加CHECK、NOT NULL、FOREIGN KEY等需要验证全表数据的约束时，PostgreSQL会先获取SHARE ROW EXCLUSIVE锁完成数据扫描验证，之后升级为ACCESS EXCLUSIVE锁完成约束添加。ACCESS EXCLUSIVE锁会阻塞所有其他对该表的读写操作。
• 非验证约束（NOVALIDATE）：如果使用ADD CONSTRAINT ... NOVALIDATE（仅支持FOREIGN KEY、CHECK等部分约束类型），操作仅需要SHARE ROW EXCLUSIVE锁，不会阻塞普通的读写操作，但会阻塞其他DDL操作。

要判断执行该语句是否需要等待，可按以下步骤操作：

1. 明确操作所需锁类型：根据约束类型和是否使用NOVALIDATE确定对应的锁模式。
2. 查询当前对象锁状态：通过pg_locks视图检查是否存在与所需锁模式冲突的已持有锁。比如需要ACCESS EXCLUSIVE锁时，只要当前有其他会话持有该表的任何锁（除自身），就需要等待；需要SHARE ROW EXCLUSIVE锁时，只要没有会话持有SHARE、SHARE ROW EXCLUSIVE、ACCESS EXCLUSIVE锁，即可立即获取。

另外，也可以通过事务测试来快速验证：

BEGIN;
LOCK TABLE your_table_name IN ACCESS EXCLUSIVE MODE NOWAIT; -- 用NOWAIT避免实际等待
ROLLBACK;

如果执行成功，说明可以立即获取锁；如果报错could not obtain lock on relation "your_table_name"，则说明需要等待其他会话释放锁。

内容的提问来源于stack exchange，提问作者Misamoto