本文简单介绍了PostgreSQL Locks(Table-Level)的基础知识,原文详见[Postgres Locking Revealed](https://engineering.nordeus.com/postgres-locking-revealed/),有所删减和调整. PostgreSQL把锁分为三类,table-level, row-level and advisory locks.Table和Row级锁可以是显式或隐式锁,advisory locks通常是显式锁.显式锁在显式用户请求时获得,而隐式锁则通过标准SQL命令获得. **Table-level Locks** 大多数的table-level locks通过内置的SQL命令获得,也可通过LOCK命令显式获得.可用的table-level locks包括: ACCESS SHARE – SELECT命令请求该类型的锁,通常情况下只涉及查询相关的表. >The SELECT command acquires this lock on table(s) referenced in query. General rule is that all queries that only read table acquire this lock. ``` [local]:5432 pg12@testdb=# begin pg12@testdb-# ; BEGIN Time: 0.958 ms [local]:5432 pg12@testdb=#* select * from t_lock limit 1; id ---- 1 (1 row) Time: 3.543 ms [local]:5432 pg12@testdb=#* select * from pg_locks where relation=295053; -[ RECORD 1 ]------+---------------- locktype | relation database | 16384 relation | 295053 page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | 4/371 pid | 2836 mode | AccessShareLock granted | t fastpath | t Time: 6.995 ms ``` ROW SHARE – SELECT FOR UPDATE和SELECT FOR SHARE命令请求该锁 >The SELECT FOR UPDATE and SELECT FOR SHARE commands acquire this lock on target table (as well as ACCESS SHARE lock on all referenced tables in query). ``` [local]:5432 pg12@testdb=# begin; BEGIN Time: 0.941 ms [local]:5432 pg12@testdb=#* SELECT * from t_lock FOR UPDATE limit 1; id ---- 1 (1 row) Time: 1.647 ms [local]:5432 pg12@testdb=#* select * from pg_locks where relation=295053; -[ RECORD 1 ]------+------------- locktype | relation database | 16384 relation | 295053 page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | 4/372 pid | 2836 mode | RowShareLock granted | t fastpath | t Time: 3.005 ms ``` ROW EXCLUSIVE – UPDATE, INSERT 和 DELETE命令请求该锁. >The UPDATE, INSERT and DELETE commands acquire this lock on target table (as well as ACCESS SHARE lock on all referenced tables in query). General rule is that all queries that modify table acquire this lock. ``` [local]:5432 pg12@testdb=#* insert into t_lock select x from generate_series(1,1000) as x; INSERT 0 1000 Time: 8.143 ms [local]:5432 pg12@testdb=#* select * from pg_locks where relation=295053; -[ RECORD 1 ]------+----------------- locktype | relation database | 16384 relation | 295053 page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | 4/374 pid | 2836 mode | RowExclusiveLock granted | t fastpath | t Time: 2.615 ms [local]:5432 pg12@testdb=#* end; COMMIT Time: 1.982 ms ``` SHARE UPDATE EXCLUSIVE – VACUUM (不包括FULL选项), ANALYZE, CREATE INDEX CONCURRENTLY >The VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, and some forms of ALTER TABLE commands acquire this lock. ``` [local]:5432 pg12@testdb=# begin; BEGIN Time: 0.902 ms [local]:5432 pg12@testdb=#* analyze t_lock; ANALYZE Time: 11.373 ms [local]:5432 pg12@testdb=#* select * from pg_locks where relation=295053; -[ RECORD 1 ]------+------------------------- locktype | relation database | 16384 relation | 295053 page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | 4/376 pid | 2836 mode | ShareUpdateExclusiveLock granted | t fastpath | f Time: 3.067 ms [local]:5432 pg12@testdb=#* end; COMMIT Time: 1.841 ms ``` SHARE – CREATE INDEX命令请求该锁 >The CREATE INDEX command acquires lock on table referenced in query. ``` [local]:5432 pg12@testdb=# begin; BEGIN Time: 0.964 ms [local]:5432 pg12@testdb=#* create index idx_t_lock_id on t_lock(id); CREATE INDEX Time: 42.750 ms [local]:5432 pg12@testdb=#* select * from pg_locks where relation=295053; -[ RECORD 1 ]------+---------- locktype | relation database | 16384 relation | 295053 page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | 4/377 pid | 2836 mode | ShareLock granted | t fastpath | f Time: 2.652 ms [local]:5432 pg12@testdb=#* end; COMMIT Time: 1.966 ms ``` SHARE ROW EXCLUSIVE – 不能由命令隐式获得,只能通过lock table命令显式获取 >Not acquired implicitly by any command. ``` [local]:5432 pg12@testdb=# begin; BEGIN Time: 1.032 ms [local]:5432 pg12@testdb=#* lock table t_lock in SHARE ROW EXCLUSIVE mode; LOCK TABLE Time: 0.909 ms [local]:5432 pg12@testdb=#* select * from pg_locks where relation=295053; -[ RECORD 1 ]------+---------------------- locktype | relation database | 16384 relation | 295053 page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | 4/388 pid | 2836 mode | ShareRowExclusiveLock granted | t fastpath | f Time: 2.563 ms [local]:5432 pg12@testdb=#* end; COMMIT Time: 0.817 ms ``` EXCLUSIVE – 该模式只允许读取已获取该锁的事务并行处理. >This lock mode allows only reads to process in parallel with transaction that acquired this lock. It is not acquired implicitly by any command. ``` [local]:5432 pg12@testdb=# begin; BEGIN Time: 1.003 ms [local]:5432 pg12@testdb=#* lock table t_lock in EXCLUSIVE mode; LOCK TABLE Time: 1.352 ms [local]:5432 pg12@testdb=#* select * from pg_locks where relation=295053; -[ RECORD 1 ]------+-------------- locktype | relation database | 16384 relation | 295053 page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | 5/221 pid | 3660 mode | ExclusiveLock granted | t fastpath | f Time: 2.564 ms [local]:5432 pg12@testdb=#* end; COMMIT Time: 1.133 ms [local]:5432 pg12@testdb=# ``` ACCESS EXCLUSIVE – ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, 和 VACUUM FULL明确请求该锁.该模式为LOCK命令的默认模式. >The ALTER TABLE, DROP TABLE, TRUNCATE, REINDEX, CLUSTER, and VACUUM FULL commands acquire lock on table referenced in query. This mode is default mode of LOCK command. ``` [local]:5432 pg12@testdb=# begin; BEGIN Time: 0.955 ms [local]:5432 pg12@testdb=#* alter table t_lock add column c2 varchar(10) default 'c2'; ALTER TABLE Time: 2.629 ms [local]:5432 pg12@testdb=#* select * from pg_locks where relation=295053; -[ RECORD 1 ]------+-------------------- locktype | relation database | 16384 relation | 295053 page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | 5/224 pid | 3660 mode | AccessExclusiveLock granted | t fastpath | f Time: 2.625 ms [local]:5432 pg12@testdb=#* end; COMMIT Time: 2.046 ms ``` 特别注意的是,虽然Lock模式中含有Row字眼,但这些Locks都是table-level locks. 下面是table-level locks的冲突列表.两个事务不能同时持有同一个表上存在冲突的锁,同一个事务则没有该限制.  DML&DDL的锁类型和冲突列表  ``` -- session 1 [local]:5432 pg12@testdb=# begin; BEGIN Time: 1.008 ms [local]:5432 pg12@testdb=#* lock table t_lock in SHARE ROW EXCLUSIVE mode; LOCK TABLE Time: 3790.558 ms (00:03.791) [local]:5432 pg12@testdb=#* -- session 2 [local]:5432 pg12@testdb=# begin; BEGIN Time: 2.325 ms [local]:5432 pg12@testdb=#* select * from pg_locks where relation=295053; -[ RECORD 1 ]------+---------------------- locktype | relation database | 16384 relation | 295053 page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | 5/229 pid | 3660 mode | ShareRowExclusiveLock granted | t fastpath | f Time: 2.960 ms [local]:5432 pg12@testdb=#* lock table t_lock in EXCLUSIVE mode nowait; ERROR: could not obtain lock on relation "t_lock" Time: 0.937 ms [local]:5432 pg12@testdb=#! end; ROLLBACK Time: 1.144 ms ``` 这是在同一个session&transaction中 ``` [local]:5432 pg12@testdb=# begin; BEGIN Time: 1.001 ms [local]:5432 pg12@testdb=#* lock table t_lock in SHARE ROW EXCLUSIVE mode; LOCK TABLE Time: 0.848 ms [local]:5432 pg12@testdb=#* lock table t_lock in EXCLUSIVE mode; LOCK TABLE Time: 0.772 ms [local]:5432 pg12@testdb=#* select * from pg_locks where relation=295053; -[ RECORD 1 ]------+---------------------- locktype | relation database | 16384 relation | 295053 page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | 5/227 pid | 3660 mode | ShareRowExclusiveLock granted | t fastpath | f -[ RECORD 2 ]------+---------------------- locktype | relation database | 16384 relation | 295053 page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | 5/227 pid | 3660 mode | ExclusiveLock granted | t fastpath | f Time: 2.724 ms [local]:5432 pg12@testdb=#* end; COMMIT Time: 0.837 ms ``` 不存在冲突的锁可由多个事务同时持有,当获取某种lock后,一直持有到事务结束.如创建了savepoint后获得lock,在回滚至savepoint时立即释放锁. **参考资料** [Postgres Locking Revealed](https://engineering.nordeus.com/postgres-locking-revealed/)