当多个会话同时访问数据库的同一数据时,理想状态是为所有会话提供高效的访问,同时还要维护严格的数据一致性。那这数据一致性通过什么来维护呢?就是之前文章多次提到的MVCC(多版本并发控制),可以点击下列文章标题回顾早前发布的内容:
PostgreSQL提供了多种锁模式用于控制对表中数据的并发访问,其中最主要的是表级锁与行级锁,此外还有页级锁,咨询锁等等,接下来主要介绍表级锁与行级锁。
通过上面2个案例我们应该比较了解各种锁模式冲突的情况了。接下来我们介绍行级锁。
锁目标存在的数据库的OID,如果目标是一个共享对象则为0,如果目标是一个事务ID则为空。
作为锁目标的关系的OID,如果目标不是一个关系或者只是关系的一部分则此列为空。
作为锁目标的页在关系中的页号,如果目标不是一个关系页或元组则此列为空。
作为锁目标的元组在页中的元组号,如果目标不是一个元组则此列为空。
作为锁目标的事务虚拟ID,如果目标不是一个虚拟事务ID则此列为空。
作为锁目标的事务ID,如果目标不是一个事务ID则此列为空ID。
包含锁目标的系统目录的OID,如果目标不是一个普通数据库对象则此列为空。
锁目标在它的系统目录中的OID,如果目标不是一个普通数据库对象则为空。
锁的目标列号(classid和objid指表本身),如果目标是某种其他普通数据库对象则此列为0,如果目标不是一个普通数据库对象则此列为空。
保持这个锁或者正在等待这个锁的事务的虚拟ID。
保持这个锁或者正在等待这个锁的服务器进程的PID,如果此锁被一个预备事务所持有则此列为空。
此进程已持有或者希望持有的锁模式的名称。
如果锁已授予则为真,如果锁被等待则为假。
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 ;
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;
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/129295.html
摘要:由此可见,自旋锁和各有优劣,他们分别适用于竞争不多和竞争激烈的场景中。每一个试图进入同步代码块的线程都会被封装成对象,它们或在对象的中,或在中,等待成为对象的成为的对象即获取了监视器锁。 前言 系列文章目录 前面两篇文章我们介绍了synchronized同步代码块以及wait和notify机制,大致知道了这些关键字和方法是干什么的,以及怎么用。 但是,知其然,并不知其所以然。 例如...
摘要:删除在使用实现分布式锁的时候,主要就会使用到这三个命令。其实,使用的可靠性是要大于使用实现的分布式锁的,但是相比而言,的性能更好。 选用Redis实现分布式锁原因 Redis有很高的性能 Redis命令对此支持较好,实现起来比较方便 使用命令介绍 SETNX SETNX key val当且仅当key不存在时,set一个key为val的字符串,返回1;若key存在,则什么都不做,返回...
摘要:创建测试表会话一备注会话一在事务里更新的记录,并不提交。会话二备注会话二删除的记录,此时由于这条记录之前被并没有提交,这句仍然处于等待状态。 PosttgreSQL 的SQL被锁情况在数据库维护过程中非常常见,之前博客 PostgreSQL 锁分析 演示了 PostgreSQL 锁的一些场景,在开始本文的介绍之前特做以下说明,假如会话A堵住会话B,我们称会话B为 blocked 会话...
阅读 1249·2023-01-11 13:20
阅读 1558·2023-01-11 13:20
阅读 1012·2023-01-11 13:20
阅读 1680·2023-01-11 13:20
阅读 3971·2023-01-11 13:20
阅读 2519·2023-01-11 13:20
阅读 1355·2023-01-11 13:20
阅读 3486·2023-01-11 13:20