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

最新推荐文章于 2022-06-28 15:54:41 发布
最新推荐文章于 2022-06-28 15:54:41 发布
阅读量299 收藏
点赞数

本文简单介绍了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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/6906/viewspace-2653100/

cuichao1900
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
postgreSQL 中的 Lock
小屋
01-13 2911
typedef struct LOCK{/* hash key */LOCKTAG   tag;    /* unique identifier of lockable object *//* data */LOCKMASK grantMask;   /* bitmask for lock types already granted */LOCKMASK waitMask;   /* bitm
Postgresql | 锁机制详解(表锁和行锁）
最新发布
Albirostris
11-10 4100
锁机制
postgresql锁检测及处理(二)---识别lock table发起的表级锁
weixin_43230594的博客
03-19 1018
1.在session 1，begin事务执行lock table命令lock t1表在eclusive mode,不要commit或rollback 2.开起session 2,begin事务执行同样的操作，这个事务将会hang住，等候先前的事务执行释放lock 3.执行下面的SQL识别锁 SELECT act1.query as blocking_query, act2.query as blocked_query, l1.pid AS blocked_pid, l2.pid AS blocking_
PostgreSql递归包含层级level
chuan_day的专栏
11-11 5370
当在用oracle做递归时，往往会用到一个虚列level，biaoshi
oracle pl/sql level妙用
evildog的专栏
05-09 2万+
1、当需要把一个字符串按某一分隔符分隔后，变为数据列，即把字符串行变为列，可以使用level关键字,例子：   with t as (select 'a;b;c;d;e' as str from dual) select level, t.str, substr(t.str, 2 * (level - 1) + 1, 1) as str_signle from t con
bitnami-docker-postgresql-repmgr：具有Replication Manager Docker映像的Bitnami PostgreSQL
02-15
$ docker run --name postgresql-repmgr bitnami/postgresql-repmgr:latest Docker撰写 $ curl -sSL ...
powa-archivist：powa-archivist：powa PostgreSQL扩展
02-05
标题和描述中提到的“powa-archivist”是针对PostgreSQL数据库的一个扩展，它主要用于性能分析和优化。"powa"是"PostgreSQL Online Analytics"的缩写，它提供了一组工具来帮助用户深入了解其数据库的运行情况。"powa...
PostgreSQL（postgresql-13.5-1-windows-x64.exe）
11-20
PostgreSQL（postgresql-13.5-1-windows-x64.exe）适用于Windows x86-64 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS），是以加州大学计算机系开发的POSTGRES，4.2版本为基础的...
PostgreSQL DBA认证 PGCE-E-092-中级SQL题目
08-26
"PostgreSQL DBA认证 PGCE-E-092-中级SQL题目" PostgreSQL DBA认证 PGCE-E-092-中级SQL题目 PostgreSQL DBA认证PGCE-E-092-中级SQL题目是一个测试SQL掌握程度的题目，涵盖了PostgreSQL数据库管理系统的各种知识点...
orkestra-postgresql-document-store:[只读] Orkestra组件，可以轻松地将PostgreSQL用作文档存储
04-21
composer require morebec/orkestra-postgresql-document-store 用法 Document Store使用doctrine/dbal来访问数据库。 因此，它需要DBAL Connection作为构造函数依赖项。 它也依赖于ClockInterface从morebec/...
odoo15 Module operations are not possible at this time, please try again later or contact your syste
fqfq123456的专栏
06-28 1084
2022-06-28 07:35:06,267 8560 INFO shui005 odoo.addons.base.models.ir_module: User #2 triggered module installation 2022-06-28 07:35:06,273 8560 ERROR shui005 odoo.sql_db: bad query: SELECT * FROM ir_cron FOR UPDATE NOWAIT ERROR: could not obtain lock on r
ubuntu18.04下出现Could not get lock的解决方法
u013121418的博客
05-28 2928
故障案例---innodb表出现大量的Waiting for table level lock
cug_jiang126com的专栏
06-21 2万+
故障现象 show  full processlist发现大量的innodb表出现Waiting for table level lock，业务将近不可用 原因分析 1 一开始当然是认为这是myisam引擎导致的，扫了一圈发现该db下确实有一个myisam表； 2 不过故障时无论是show  processlist还是innodb_trx等结果看，都没有发现这个myisam表的记录； 3
Mysql锁(表级锁，页级锁，行级锁)
杨杨杨~~的博客
06-04 3490
一、表级锁，页级锁，行级锁 1、表级锁定（table-level）:引擎 MyISAM ， 理解为锁住整个表，可以同时读，写不行 仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作。 详情:表级锁是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，所以获取锁和释放锁的速度很快。 优点是表级锁会一次将整个表锁定，所以可以很好的避免死锁的问题。 缺点是锁定颗粒度大是出现锁定资源争用的概率也会最高，致使并发度大打折扣。...
Postgresql杂谈 16—Postgresql中的锁机制
Hyman的博客
08-13 6734
今天，我们学习下Postgresql中的锁机制。锁是数据库事务的基础，通过锁才能保证数据库在并发时能够保证数据的安全和一致，才能够达到事务的一致性和隔离性。但是任何事物都有它的两面性，引入锁同样会增加性能开销和可能的死锁问题，不过好在都存在解决方法。Postgresql和其它数据库一样，从锁作用的对象上来说可以分为表锁、行锁两种，从排他性来说可以大体分为共享锁和排他锁，但是由于Postgresql是通过多版本的方式对数据库进行更新，它也引入了Access锁。接下来我们就对这些内容分类进行学习。...
mysql update nowait_for update 和 for update nowait的区别
weixin_30572707的博客
02-17 1198
for update 和 for update nowait的相同点对操作的数据行进行加锁，在事务提交前防止其他操作对数据的修改使用for update测试工具 pgadmin，打开SQL窗口，关闭事务的自动提交,改成手动提交事务select * from table1 where id ='001' for update;再用pgadmin打开新的一个SQL窗口，打开事务的自动提交，在执行同样的...
PostgreSQL的几种常见问题和解决方法
热门推荐
international24的博客
04-30 7万+
1. 前言 1.1 概述 本文介绍了postgresql的几种常见问题，并从现象出发，逐步排查问题，分析导致问题的原因并给出解决方案。 本文介绍的问题分为两大类：一类是关于PostgreSQL无法启动的问题，另一类是PostgreSQL启动后，部分数据库对象无法访问的问题。 1.2软件环境 本文使用的 PostgreSQL 版本是 9.2。 1.3一些约定术语 PostgreSQ...
PostgreSQL 之 锁机制
HighGO
02-28 7412
当要增删改查表中的数据时，首先是要获得表上的锁，然后再获得行上的锁postgresql中有8种表锁最普通的是共享锁 share 和排他锁 exclusive因为多版本的原因，修改一条语句的同时，允许了读数据，为了处理这种情况，又增加了两种锁”access share”和”acess excusive”，锁中的关键字 access 是与多版本相关的为了处理表锁和行锁之间的关系，有了 意向锁 的...
PostgreSQL中的表锁
ispringmw的博客
07-13 4986
PostgreSQL的并发控制以**快照隔离（SI）为主，以两阶段锁定（2PL）**机制为辅。PostgreSQL对DML（增删改查等命令）使用SI，对DDL（CREATE TABLE 等命令）使用2PL。 PostgreSQL有好几类锁，其中最主要的是表级锁与行级锁，此外还有页级锁，咨询锁等等。表级锁通常是各种命令执行时自动获取的，或者通过事务中的LOCK 语句显式获取；而行级锁则是由SELECT FOR UPDATE|SHARE 语句显式获取的。本文主要介绍PostgreSQL中的表锁。 表级锁 表级
PostgreSQL 11-16版本主要特性解析：哈希分区与并行操作
"本文档详细介绍了PostgreSQL数据库系统从11到16版本的主要特性，重点关注了哈希分区的支持、存储过程的事务化、并行创建索引和并行执行等增强功能。" PostgreSQL是一个开源的关系型数据库管理系统，随着时间的推移...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值