【postgresql】锁

PostgreSQL 提供了多种锁模式来控制对表和行的并发访问，以确保数据的一致性和完整性。这些锁模式包括表级锁和行级锁，它们可以由应用程序显式控制，也可以在执行大多数 PostgreSQL 命令时自动获取。

锁类型

PostgreSQL类型的锁包括：

• 表级锁（Table-Level Locks）：

  • ACCESS SHARE：通常在SELECT语句中自动获取，只与ACCESS EXCLUSIVE锁冲突。

  • ROW SHARE：通常在SELECT FOR UPDATE/FOR SHARE语句中自动获取，与EXCLUSIVE和ACCESS EXCLUSIVE锁冲突。

  • ROW EXCLUSIVE：通常在INSERT、UPDATE、DELETE语句中自动获取，与SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁冲突。

  • SHARE UPDATE EXCLUSIVE：保护表不受并发模式改变和VACUUM的影响，与SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁冲突。

  • SHARE：通常在CREATE INDEX语句中自动获取，与ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁冲突。

  • SHARE ROW EXCLUSIVE：保护表不受并发数据修改，与ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁冲突。

  • EXCLUSIVE：允许并发只读访问，但阻止其他事务进行写操作，与ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁冲突。

  • ACCESS EXCLUSIVE：最高级别的锁，确保只有持有锁的事务可以访问表，与所有类型的锁冲突。

• 行级锁（Row-Level Locks）：

  • FOR UPDATE：锁定选定的行，防止其他事务对这些行进行修改或删除。

  • FOR NO KEY UPDATE：类似于FOR UPDATE，但不阻止其他事务对这些行进行SELECT FOR KEY SHARE。

  • FOR SHARE：类似于FOR UPDATE，但只锁定读取的行，不阻止其他事务对这些行进行修改。

  • FOR KEY SHARE：类似于FOR SHARE，但只阻止其他事务对这些行进行DELETE或UPDATE关键字列。

使用锁

通过以下方式显式地获取锁：

LOCK TABLE table_name IN lock_mode_name MODE;

获取一个表的ACCESS EXCLUSIVE锁：

BEGIN;

LOCK TABLE my_table IN ACCESS EXCLUSIVE MODE;

COMMIT;

检测和解决死锁

以通过查询系统视图 pg_locks 和 pg_stat_activity 来检测死锁和锁定情况。例如，查询特定表的锁情况可以使用以下 SQL 语句：

SELECT a.locktype, a.database, a.pid, a.mode, a.relation, b.relname
FROM pg_locks a
JOIN pg_class b ON a.relation = b.oid
WHERE upper(b.relname) = 'TABLE_NAME';

---

---

---