Postgresql 解决锁表

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Postgresql 解决锁表，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4154字，纯文字阅读大概需要6分钟。

内容图文

##一、postgresql解决锁表
--查询是否锁表了
select oid from pg_class where relname='可能锁表了的表'
select pid from pg_locks where relation='上面查出的oid'
--如果查询到了结果，表示该表被锁 则需要释放锁定
select pg_cancel_backend(上面查到的pid)
12345 二、查看锁的其他方法
转载地址：https://yq.aliyun.com/ask/184?order=ctime
--? 用一个函数来将锁转换为数字，
create or replace function f_lock_level(i_mode text) returns int as
$$ declare
begin
? case i_mode
??? when 'INVALID' then return 0;
??? when 'AccessShareLock' then return 1;
??? when 'RowShareLock' then return 2;
??? when 'RowExclusiveLock' then return 3;
??? when 'ShareUpdateExclusiveLock' then return 4;
??? when 'ShareLock' then return 5;
??? when 'ShareRowExclusiveLock' then return 6;
??? when 'ExclusiveLock' then return 7;
??? when 'AccessExclusiveLock' then return 8;
??? else return 0;
? end case;
end; $$
?language plpgsql strict;
-- 修改查询语句，按锁级别排序：
with t_wait as????????????????????
(?? 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,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
),
t_run as
(
?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,b.xact_start,b.query_start,
?b.usename,b.datname
?from pg_locks a,pg_stat_activity b
?where a.pid=b.pid and a.granted
)
select r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,r.relation::regclass,r.pid r_pid,
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,w.mode w_mode,w.pid w_pid,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?
from t_wait w,t_run r
where 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
order by f_lock_level(w.mode)+f_lock_level(r.mode) desc,r.xact_start;
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455
--现在可以排在前面的就是锁级别高的等待，优先干掉这个。
-[ RECORD 1 ]-+---------------------------------------------------------------------
locktype????? | relation? -- 冲突类型
r_mode??????? | ShareUpdateExclusiveLock? -- 持锁模式
r_user??????? | postgres? -- 持锁用户
r_db????????? | postgres? -- 持锁数据库
relation????? | tbl? -- 持锁对象
r_pid???????? | 25656? -- 持锁进程
r_xact_start? | 2015-05-10 14:11:16.08318+08? -- 持锁事务开始时间
r_query_start | 2015-05-10 14:11:16.08318+08? -- 持锁SQL开始时间
r_locktime??? | 00:01:49.460779? -- 持锁时长
r_query?????? | vacuum freeze tbl;? --? 持锁SQL,注意不一定是这个SQL带来的锁,也有可能是这个事务在之前执行的SQL加的锁
w_mode??????? | AccessExclusiveLock? -- 等待锁模式
w_pid???????? | 26731? -- 等待锁进程
w_xact_start? | 2015-05-10 14:11:17.987362+08? --? 等待锁事务开始时间
w_query_start | 2015-05-10 14:11:17.987362+08? --? 等待锁SQL开始时间
w_locktime??? | 00:01:47.556597? --? 等待锁时长
w_query?????? | truncate tbl;? -- 等待锁SQL
-[ RECORD 2 ]-+---------------------------------------------------------------------
locktype????? | relation
r_mode??????? | ShareUpdateExclusiveLock
r_user??????? | postgres
r_db????????? | postgres
relation????? | tbl
r_pid???????? | 25656
r_xact_start? | 2015-05-10 14:11:16.08318+08
r_query_start | 2015-05-10 14:11:16.08318+08
r_locktime??? | 00:01:49.460779
r_query?????? | vacuum freeze tbl;
w_mode??????? | RowExclusiveLock
w_pid???????? | 25582
w_xact_start? | 2015-05-10 14:11:22.845+08
w_query_start | 2015-05-10 14:11:22.845+08
w_locktime??? | 00:01:42.698959
w_query?????? | insert into tbl(crt_time) select now() from generate_series(1,1000);? -- 这个SQL其实等待的是truncate tbl的锁;
......

内容总结

以上是互联网集市为您收集整理的Postgresql 解决锁表全部内容，希望文章能够帮你解决Postgresql 解决锁表所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---