PostgreSQL锁类型介绍

Table-level Locks

• ACCESS SHARE

  only reads a table and does not modify it will acquire this lock mode.

• ROW SHARE

  SELECT FOR UPDATE 
  SELECT FOR SHARE
  

• ROW EXCLUSIVE

  INSERT --增
  DELETE --删
  UPDATE --改
  

• SHARE UPDATE EXCLUSIVE

  CREATE INDEX CONCURRENTLY --创建索引（CONCURRENTLY）
  ANALYZE --收集统计信息
  VACUUM (without FULL) --垃圾清理(without FULL)
  CREATE STATISTICS --创建统计信息
  

• SHARE

  CREATE INDEX --创建索引（without CONCURRENTLY）
  

• SHARE ROW EXCLUSIVE

  ALTER TABLE --更改表
  CREATE TRIGGER --创建触发器
  CREATE COLLATION --创建规则
  

• EXCLUSIVE

  REFRESH MATERIALIZED VIEW CONCURRENTLY --更新物化视图（CONCURRENTLY）
  

• ACCESS EXCLUSIVE

  DROP TABLE --删除表
  TRUNCATE --晴空表
  REINDEX --重建索引
  CLUSTER --聚类表
  VACUUM FULL --垃圾清理（FULL）
  REFRESH MATERIALIZED VIEW  ----更新物化视图（without CONCURRENTLY）
  

• Conflicting Lock Modes
  

Row-level Locks

• FOR UPDATE

  SELECT * FROM TableA WHERE ID = 1 FOR UPDATE
  

• FOR NO KEY UPDATE

  SELECT * FROM TableA WHERE ID = 1 FOR NO KEY UPDATE
  

• FOR SHARE

  SELECT * FROM TableA WHERE ID = 1 FOR SHARE
  

• FOR KEY SHARE

  SELECT * FROM TableA WHERE ID = 1 FOR KEY SHARE
  

• Conflicting Row-level Locks
  

• 参考资料

  1. https://www.postgresql.org/docs/11/explicit-locking.html
  2. https://www.postgresql.org/docs/9.3/view-pg-locks.html
  3. https://www.postgresql.org/docs/11/sql-lock.html