ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(71) - Locks(Table-Level):What You Should Know

PostgreSQL DBA(71) - Locks(Table-Level):What You Should Know

翻译 PostgreSQL 作者:husthxd 时间:2019-08-08 17:43:52 0 删除 编辑

本文简单介绍了PostgreSQL Locks(Table-Level)的基础知识,原文详见 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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2653100/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1338
  • 访问量
    3804678