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
-
参考资料
- https://www.postgresql.org/docs/11/explicit-locking.html
- https://www.postgresql.org/docs/9.3/view-pg-locks.html
- https://www.postgresql.org/docs/11/sql-lock.html