https://www.postgresql.org/docs/13/explicit-locking.html#LOCKING-ROWS

表级锁


ACCESS SHAREROW SHAREROW EXCLUSIVESHARE UPDATE EXCLUSIVESHARESHARE ROW EXCLUSIVEEXCLUSIVEACCESS EXCLUSIVE持锁语句描述
ACCESS SHARE







×

SELECT



ROW SHARE






×

×
SELECT FOR UPDATE
SELECT FOR SHARE


ROW EXCLUSIVE




×

×

×

×
INSERT/UPDATE/DELETE



SHARE UPDATE EXCLUSIVE



×

×

×

×

×
CREATE INDEX CURRENTLY/ ANALYZE/
ALTER TABLE/VALIDATE/ VACUUM(not full)

避免并发schema changes
和VACUUM
SHARE


×

×


×

×

×
CREATE INDEX(without CURRENTLY)


避免并发data changes
SHARE ROW EXCLUSIVE

×

×

×

×

×

×

×
CREATE TRIGGER


避免并发data changes,并且是self-exclusive
的,同一时间只能有一个session持有该锁
EXCLUSIVE

×

×

×

×

×

×

×

REFRESH MATERIALIZED VIEW 
CONCURRENTLY


ACCESS EXCLUSIVE
×

×

×

×

×

×

×

×
DROP/TRUNCATE/REINDEX/CLUSTER
VACUUM FULL等

同一时间只允许一个session持有 ,prevents
concurrent  transactions   from reading and writeing 

Advisory locks

应用层并发控制

  • No labels