资讯专栏INFORMATION COLUMN

PostgreSQL锁浅析

IT那活儿 / 449人阅读
PostgreSQL锁浅析
点击上方“IT那活儿”公众号,关注后了解更多内容,不管IT什么活儿,干就完了!!! 

锁存在的意义

在了解PostgreSQL锁之前,我们需要了解锁存在的意义是啥?

当多个会话同时访问数据库的同一数据时,理想状态是为所有会话提供高效的访问,同时还要维护严格的数据一致性。那这数据一致性通过什么来维护呢?就是之前文章多次提到的MVCC(多版本并发控制),可以点击下列文章标题回顾早前发布的内容:

聊聊PostgreSQL事务id那点事
Mvcc机制
MVCC:每个SQL语句看到的都只是当前事务开始的数据快照,而不管底层数据的当前状态。这样可以保护语句不会看到可能由其他在相同数据行上执行更新的并发事务造成的不一致数据,为每一个数据库会话提供事务隔离。MVCC避免了传统的数据库系统的锁定方法,将通过锁争夺最小化的方法来达到多会话并发访问时的性能最大化目的。

PostgreSQL提供了多种锁模式用于控制对表中数据的并发访问,其中最主要的是表级锁与行级锁,此外还有页级锁,咨询锁等等,接下来主要介绍表级锁与行级锁。

表级锁

表级锁通常会在执行各种命令执行时自动获取,或者通过在事务中使用LOCK语句显式获取。
每种锁都有自己的冲突集合, 两个事务在同一时刻不能在同一个表上持有属于相互冲突模式的锁,但可以持有不冲突的锁。
表级锁总共有八种模式,其存在于PG的共享内存中,可以通过pg_locks系统视图查阅。
1. ACCESS SHARE
只与ACCESS EXCLUSIVE锁模式冲突。
SELECT命令在被引用的表上获得一个这种模式的锁。通常,任何只读取表而不修改它的查询都将获得这种锁模式。
2. ROW SHARE
与EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。
SELECT FOR UPDATE和SELECT FOR SHARE命令在目标表上取得一个这种模式的锁 (加上在被引用但没有选择FOR UPDATE/FOR SHARE的任何其他表上的ACCESS SHARE锁)。
3. ROW EXCLUSIVE
与SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。
命令UPDATE、DELETE和INSERT在目标表上取得这种锁模式(加上在任何其他被引用表上的ACCESS SHARE锁)。通常,这种锁模式将被任何修改表中数据的命令取得。
4. SHARE UPDATE EXCLUSIVE
与SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。这种模式保护一个表不受并发模式改变和VACUUM运行的影响。
由VACUUM(不带FULL)、ANALYZE、 CREATE INDEX CONCURRENTLY、REINDEX CONCURRENTLY、 CREATE STATISTICS以及某些ALTER INDEX和 ALTER TABLE的变体获得。
5. SHARE
与ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。这种模式保护一个表不受并发数据改变的影响。
由CREATE INDEX(不带CONCURRENTLY)取得。
6. SHARE ROW EXCLUSIVE
与ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。这种模式保护一个表不受并发数据修改所影响,并且是自排他的,这样在一个时刻只能有一个会话持有它。
由CREATE TRIGGER和某些形式的ALTER TABLE所获得。
7. EXCLUSIVE
与ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。这种模式只允许并发的ACCESS SHARE锁,即只有来自于表的读操作可以与一个持有该锁模式的事务并行处理。
由REFRESH MATERIALIZED VIEW CONCURRENTLY获得。
8. ACCESS EXCLUSIVE
与所有模式的锁冲突(ACCESS SHARE、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE)。这种模式保证持有者是访问该表的唯一事务。
由ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL和REFRESH MATERIALIZED VIEW(不带CONCURRENTLY)命令获取。很多形式的ALTER INDEX和ALTER TABLE也在这个层面上获得锁。这也是未显式指定模式的LOCK TABLE命令的默认锁模式。
表级锁模式冲突表:
注:X表示冲突。
怎么去看上面这个图呢?
我们在这里以2个例子说明:
场景一
当一个会话运行了update语句,此时会话表上的锁模式为ROW EXCLUSIVE,从上图我们可以看出ROW EXCLUSIVE与SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。
也就是说在这个会话未提交事务释放锁之前,我们不能做申请SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE相关的操作,例如CREATE INDEX(不带CONCURRENTLY)、ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL和REFRESH MATERIALIZED VIEW(不带CONCURRENTLY)等。
会话一执行update语句:
会话二执行alter table语句时处于等待状态:
我们查看等待情况如下:
注:Lock_Granted: true即为堵塞源。
一直到会话一结束,会话二语句才执行成功。
场景二
当一个会话运行了truncate语句,此时会话表上的锁模式为ACCESS EXCLUSIVE,从图上看我们可以看到它和所有锁模式都冲突,意味着在当前会话未结束之前,这个表上的其他操作都做不了。
会话一执行truncate语句:
会话二执行select语句时处于等待状态:

查看锁等待情况如下:

通过上面2个案例我们应该比较了解各种锁模式冲突的情况了。接下来我们介绍行级锁。

行级锁

同一个事务可能会在相同的行上保持冲突的锁,甚至是在不同的子事务中。但是除此之外,两个事务永远不可能在相同的行上持有冲突的锁。行级锁不影响数据查询,它们只阻塞对同一行的写入者和加锁者。行级锁在事务结束时或保存点回滚的时候释放,就像表级锁一样。
行级锁模式
1. FOR UPDATE
FOR UPDATE会导致由SELECT语句检索到的行被锁定,就好像它们要被更新。这可以阻止它们被其他事务锁定、修改或者删除,一直到当前事务结束。
也就是说其他尝试UPDATE、DELETE、SELECT FOR UPDATE、SELECT FOR NO KEY UPDATE、SELECT FOR SHARE或者SELECT FOR KEY SHARE这些行的事务将被阻塞,直到当前事务结束。
反过来,SELECT FOR UPDATE将等待已经在相同行上运行以上这些命令的并发事务,并且接着锁定并且返回被更新的行(或者没有行,因为行可能已被删除)。
2. FOR NO KEY UPDATE
行为与FOR UPDATE类似,不过获得的锁较弱:这种锁将不会阻塞尝试在相同行上获得锁的SELECT FOR KEY SHARE命令。任何不获取FOR UPDATE锁的UPDATE也会获得这种锁模式。
3. FOR SHARE
行为与FOR NO KEY UPDATE类似,不过它在每个检索到的行上获得一个共享锁而不是排他锁。
一个共享锁会阻塞其他事务在这些行上执行UPDATE、DELETE、SELECT FOR UPDATE或者SELECT FOR NO KEY UPDATE,但是它不会阻止它们执行SELECT FOR SHARE或者SELECT FOR KEY SHARE。
4. FOR KEY SHARE
行为与FOR SHARE类似,不过锁较弱:SELECT FOR UPDATE会被阻塞,但是SELECT FOR NO KEY UPDATE不会被阻塞。一个键共享锁会阻塞其他事务执行修改键值的DELETE或者UPDATE,但不会阻塞其他UPDATE,也不会阻止SELECT FOR NO KEY UPDATE、SELECT FOR SHARE或者SELECT FOR KEY SHARE。
行级锁模式冲突表:
PG中我们要查看锁信息,pg_locks视图提供了详细的信息,不同于oracle的dba_locks,pg_locks稍显复杂。
接下来我们对pg_locks视图各字段做下详细介绍:
  • locktype
    可锁对象的类型:relation, extend, frozenid, page, tuple, transactionid, virtualxid, spectoken, object, userlock, or advisory.
    locktype的等待事件:
  • database oid (参考 pg_database.oid)

    锁目标存在的数据库的OID,如果目标是一个共享对象则为0,如果目标是一个事务ID则为空。

  • relation oid (参考 pg_class.oid)

    作为锁目标的关系的OID,如果目标不是一个关系或者只是关系的一部分则此列为空。

  • page int4

    作为锁目标的页在关系中的页号,如果目标不是一个关系页或元组则此列为空。

  • tuple int2

    作为锁目标的元组在页中的元组号,如果目标不是一个元组则此列为空。

  • virtualxid text

    作为锁目标的事务虚拟ID,如果目标不是一个虚拟事务ID则此列为空。

  • transactionid xid

    作为锁目标的事务ID,如果目标不是一个事务ID则此列为空ID。

  • classid oid (参考 pg_class.oid)

    包含锁目标的系统目录的OID,如果目标不是一个普通数据库对象则此列为空。

  • objid oid (参考 any OID column)

    锁目标在它的系统目录中的OID,如果目标不是一个普通数据库对象则为空。

  • objsubid int2

    锁的目标列号(classid和objid指表本身),如果目标是某种其他普通数据库对象则此列为0,如果目标不是一个普通数据库对象则此列为空。

  • virtualtransaction text

    保持这个锁或者正在等待这个锁的事务的虚拟ID。

  • pid int4

    保持这个锁或者正在等待这个锁的服务器进程的PID,如果此锁被一个预备事务所持有则此列为空。

  • mode text

    此进程已持有或者希望持有的锁模式的名称。

  • granted bool

    如果锁已授予则为真,如果锁被等待则为假。

  • fastpath bool
    如果锁通过快速路径获得则为真,通过主锁表获得则为假。
附上一些网上摘录的常用查锁SQL:
1)查看当前事务锁等待、持锁信息的SQL:
with
t_wait as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted
),
t_run as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted
),
t_overlap as
(
select r.* from t_wait w join t_run r on
(
r.locktype is not distinct from w.locktype and
r.database is not distinct from w.database and
r.relation is not distinct from w.relation and
r.page is not distinct from w.page and
r.tuple is not distinct from w.tuple and
r.virtualxid is not distinct from w.virtualxid and
r.transactionid is not distinct from w.transactionid and
r.classid is not distinct from w.classid and
r.objid is not distinct from w.objid and
r.objsubid is not distinct from w.objsubid and
r.pid <> w.pid
)
),
t_unionall as
(
select r.* from t_overlap r
union all
select w.* from t_wait w
)
select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,
string_agg(
Pid: ||case when pid is null then NULL else pid::text end||chr(10)||
Lock_Granted: ||case when granted is null then NULL else granted::text end|| , Mode: ||case when mode is null then NULL else mode::text end|| , FastPath: ||case when fastpath is null then NULL else fastpath::text end|| , VirtualTransaction: ||case when virtualtransaction is null then NULL else virtualtransaction::text end|| , Session_State: ||case when state is null then NULL else state::text end||chr(10)||
Username: ||case when usename is null then NULL else usename::text end|| , Database: ||case when datname is null then NULL else datname::text end|| , Client_Addr: ||case when client_addr is null then NULL else client_addr::text end|| , Client_Port: ||case when client_port is null then NULL else client_port::text end|| , Application_Name: ||case when application_name is null then NULL else application_name::text end||chr(10)||
Xact_Start: ||case when xact_start is null then NULL else xact_start::text end|| , Query_Start: ||case when query_start is null then NULL else query_start::text end|| , Xact_Elapse: ||case when (now()-xact_start) is null then NULL else (now()-xact_start)::text end|| , Query_Elapse: ||case when (now()-query_start) is null then NULL else (now()-query_start)::text end||chr(10)||
SQL (Current SQL in Transaction): ||chr(10)||
case when query is null then NULL else query::text end,
chr(10)||--------||chr(10)
order by
( case mode
when INVALID then 0
when AccessShareLock then 1
when RowShareLock then 2
when RowExclusiveLock then 3
when ShareUpdateExclusiveLock then 4
when ShareLock then 5
when ShareRowExclusiveLock then 6
when ExclusiveLock then 7
when AccessExclusiveLock then 8
else 0
end  ) desc,
(case when granted then 0 else 1 end)
) as lock_conflict
from t_unionall
group by
locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;
输出结果格式如下:
注:Lock_Granted: true即为堵塞源。
2)查看堵塞会话,并生成kill sql:
with recursive tmp_lock as (
select distinct
--w.mode w_mode,w.page w_page,
--w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,
--now()-w.query_start w_locktime,w.query w_query
w.pid as id,--w_pid,
r.pid as parentid--r_pid,
--r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,
--r.relation::regclass,
--r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,
--r.query_start r_query_start,
--now()-r.query_start r_locktime,r.query r_query,
from (
select a.mode,a.locktype,a.database,
a.relation,a.page,a.tuple,a.classid,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,
a.transactionid,
b.query as query,
b.xact_start,b.query_start,b.usename,b.datname
from pg_locks a,
pg_stat_activity b
where a.pid=b.pid
and not a.granted
) w,
(
select a.mode,a.locktype,a.database,
a.relation,a.page,a.tuple,a.classid,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,
a.transactionid,
b.query as query,
b.xact_start,b.query_start,b.usename,b.datname
from pg_locks a,
pg_stat_activity b -- select pg_typeof(pid) from pg_stat_activity
where a.pid=b.pid
and a.granted
) r
where 1=1
and r.locktype is not distinct from w.locktype
and r.database is not distinct from w.database
and r.relation is not distinct from w.relation
and r.page is not distinct from w.page
and r.tuple is not distinct from w.tuple
and r.classid is not distinct from w.classid
and r.objid is not distinct from w.objid
and r.objsubid is not distinct from w.objsubid
and r.transactionid is not distinct from w.transactionid
and r.pid <> w.pid

),tmp0 as (
select *
from tmp_lock tl
union all
select t1.parentid,0::int4
from tmp_lock t1
where 1=1
and t1.parentid not in (select id from tmp_lock)
),tmp3 (pathid,depth,id,parentid) as (
SELECT array[id]::text[] as pathid,1 as depth,id,parentid
FROM tmp0
where 1=1
and parentid=0
union
SELECT t0.pathid||array[t1.id]::text[] as pathid,t0.depth+1 as depth,t1.id,t1.parentid
FROM tmp0 t1,
tmp3 t0
where 1=1
and t1.parentid=t0.id
)
select distinct
/||array_to_string(a0.pathid,/) as pathid,
a0.depth,
a0.id,a0.parentid,lpad(a0.id::text, 2*a0.depth-1+length(a0.id::text), ) as tree_id,
--select pg_cancel_backend(||a0.id|| ); as cancel_pid,
--select pg_terminate_backend(||a0.id|| ); as term_pid,
case when a0.depth =1 then select pg_terminate_backend(|| a0.id || ); else null end  as term_pid,
case when a0.depth =1 then select cancel_backend(|| a0.id || ); else null end  as cancel_pid
,a2.datname,a2.usename,a2.application_name,a2.client_addr,a2.wait_event_type,a2.wait_event,a2.state
--,a2.backend_start,a2.xact_start,a2.query_start
from tmp3 a0
left outer join (select distinct /||id||/ as prefix_id,id
from tmp0
where 1=1 ) a1
on position( a1.prefix_id in /||array_to_string(a0.pathid,/)||/ ) >0
left outer join pg_stat_activity a2 -- select * from pg_stat_activity
on a0.id = a2.pid
order by /||array_to_string(a0.pathid,/),a0.depth;
输出结果格式如下:

本文作者:魏 斌(上海新炬王翦团队)

本文来源:“IT那活儿”公众号

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/129295.html

相关文章

  • 线程间的同步与通信(3)——浅析synchronized的实现原理

    摘要:由此可见,自旋锁和各有优劣,他们分别适用于竞争不多和竞争激烈的场景中。每一个试图进入同步代码块的线程都会被封装成对象,它们或在对象的中,或在中,等待成为对象的成为的对象即获取了监视器锁。 前言 系列文章目录 前面两篇文章我们介绍了synchronized同步代码块以及wait和notify机制,大致知道了这些关键字和方法是干什么的,以及怎么用。 但是,知其然,并不知其所以然。 例如...

    keithxiaoy 评论0 收藏0
  • 浅析如何用Redis实现分布式

    摘要:删除在使用实现分布式锁的时候,主要就会使用到这三个命令。其实,使用的可靠性是要大于使用实现的分布式锁的,但是相比而言,的性能更好。 选用Redis实现分布式锁原因 Redis有很高的性能 Redis命令对此支持较好,实现起来比较方便 使用命令介绍 SETNX SETNX key val当且仅当key不存在时,set一个key为val的字符串,返回1;若key存在,则什么都不做,返回...

    张率功 评论0 收藏0
  • PostgreSQL9.6:新增pg_blocking_pids函数准确定位 Blocking SQ

    摘要:创建测试表会话一备注会话一在事务里更新的记录,并不提交。会话二备注会话二删除的记录,此时由于这条记录之前被并没有提交,这句仍然处于等待状态。 PosttgreSQL 的SQL被锁情况在数据库维护过程中非常常见,之前博客 PostgreSQL 锁分析 演示了 PostgreSQL 锁的一些场景,在开始本文的介绍之前特做以下说明,假如会话A堵住会话B,我们称会话B为 blocked 会话...

    liuchengxu 评论0 收藏0

发表评论

0条评论

IT那活儿

|高级讲师

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<