https://www.postgresql.org/docs/13/explicit-locking.html#LOCKING-ROWS
表级锁
ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS 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 | ||
ACCESS EXCLUSIVE | × | × | × | × | × | × | × | × | DROP/TRUNCATE/REINDEX/CLUSTER VACUUM FULL等 | 同一时间只允许一个session持有 ,prevents concurrent transactions from reading and writeing |
Advisory locks
应用层并发控制