Postgresql - 查看锁表信息的实现
查看表锁信息，是DBA常用的脚本之一。
实验环境：
CentOS7
PG10.4
先通过A窗口执行
mytest=#begin; BEGIN mytest=#updatet1setcol1='a'whereid=1; UPDATE1 mytest=#
打开B窗口执行
mytest=#begin; BEGIN mytest=#updatet1setcol1='b'whereid=2; UPDATE1 mytest=#updatet1setcol1='b'whereid=1;
等待了
说明只锁住了行，对于更新其他行没有影响。
再打开一个窗口查看信息
SELECT
a.datname,
locktype,
virtualtransaction,
transactionid,
nspname,
relname,
mode,
granted,
cast(date_trunc('second',query_start)AStimestamp)ASquery_start
FROM
pg_locks
LEFTOUTERJOINpg_classON(pg_locks.relation=pg_class.oid)
LEFTOUTERJOINpg_namespaceON(pg_namespace.oid=pg_class.relnamespace),
pg_stat_activitya
WHERENOTpg_locks.pid=pg_backend_pid()
ANDpg_locks.pid=a.pid;
datname|locktype|virtualtransaction|transactionid|nspname|relname|mode|granted|query_start
---------+---------------+--------------------+---------------+---------+---------+------------------+---------+---------------------
mytest|relation|7/332||public|t1|RowExclusiveLock|t|2018-06-2806:29:58
mytest|virtualxid|7/332||||ExclusiveLock|t|2018-06-2806:29:58
mytest|relation|6/42||public|t1|RowExclusiveLock|t|2018-06-2806:29:35
mytest|virtualxid|6/42||||ExclusiveLock|t|2018-06-2806:29:35
mytest|transactionid|7/332|712|||ExclusiveLock|t|2018-06-2806:29:58
mytest|transactionid|6/42|711|||ExclusiveLock|t|2018-06-2806:29:35
mytest|transactionid|7/332|711|||ShareLock|f|2018-06-2806:29:58
mytest|tuple|7/332||public|t1|ExclusiveLock|t|2018-06-2806:29:58
(8rows)
补充：如何查看PostgreSQL正在执行的SQL以及锁信息
查看当前正在运行的SQL
SELECT procpid, start, now()-startASlap, current_query FROM (SELECT backendid, pg_stat_get_backend_pid(S.backendid)ASprocpid, pg_stat_get_backend_activity_start(S.backendid)ASstart, pg_stat_get_backend_activity(S.backendid)AScurrent_query FROM (SELECTpg_stat_get_backend_idset()ASbackendid)ASS )ASS WHERE current_query<>'' ORDERBY lapDESC; procpid：进程id start：进程开始时间 lap：经过时间 current_query：执行中的sql 怎样停止正在执行的sql SELECTpg_cancel_backend(进程id); 或者用系统函数 kill-9进程id;
查看数据库目前是否有锁
--查看当前事务锁等待、持锁信息的SQL with t_waitas ( selecta.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 frompg_locksa,pg_stat_activitybwherea.pid=b.pidandnota.granted ), t_runas ( selecta.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 frompg_locksa,pg_stat_activitybwherea.pid=b.pidanda.granted ), t_overlapas ( selectr.*fromt_waitwjoint_runron ( r.locktypeisnotdistinctfromw.locktypeand r.databaseisnotdistinctfromw.databaseand r.relationisnotdistinctfromw.relationand r.pageisnotdistinctfromw.pageand r.tupleisnotdistinctfromw.tupleand r.virtualxidisnotdistinctfromw.virtualxidand r.transactionidisnotdistinctfromw.transactionidand r.classidisnotdistinctfromw.classidand r.objidisnotdistinctfromw.objidand r.objsubidisnotdistinctfromw.objsubidand r.pid<>w.pid ) ), t_unionallas ( selectr.*fromt_overlapr unionall selectw.*fromt_waitw ) selectlocktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid, string_agg( 'Pid:'||casewhenpidisnullthen'NULL'elsepid::textend||chr(10)|| 'Lock_Granted:'||casewhengrantedisnullthen'NULL'elsegranted::textend||',Mode:'||casewhenmodeisnullthen'NULL'elsemode::textend||',FastPath:'||casewhenfastpathisnullthen'NULL'elsefastpath::textend||',VirtualTransaction:'||casewhenvirtualtransactionisnullthen'NULL'elsevirtualtransaction::textend||',Session_State:'||casewhenstateisnullthen'NULL'elsestate::textend||chr(10)|| 'Username:'||casewhenusenameisnullthen'NULL'elseusename::textend||',Database:'||casewhendatnameisnullthen'NULL'elsedatname::textend||',Client_Addr:'||casewhenclient_addrisnullthen'NULL'elseclient_addr::textend||',Client_Port:'||casewhenclient_portisnullthen'NULL'elseclient_port::textend||',Application_Name:'||casewhenapplication_nameisnullthen'NULL'elseapplication_name::textend||chr(10)|| 'Xact_Start:'||casewhenxact_startisnullthen'NULL'elsexact_start::textend||',Query_Start:'||casewhenquery_startisnullthen'NULL'elsequery_start::textend||',Xact_Elapse:'||casewhen(now()-xact_start)isnullthen'NULL'else(now()-xact_start)::textend||',Query_Elapse:'||casewhen(now()-query_start)isnullthen'NULL'else(now()-query_start)::textend||chr(10)|| 'SQL(CurrentSQLinTransaction):'||chr(10)|| casewhenqueryisnullthen'NULL'elsequery::textend, chr(10)||'--------'||chr(10) orderby (casemode when'INVALID'then0 when'AccessShareLock'then1 when'RowShareLock'then2 when'RowExclusiveLock'then3 when'ShareUpdateExclusiveLock'then4 when'ShareLock'then5 when'ShareRowExclusiveLock'then6 when'ExclusiveLock'then7 when'AccessExclusiveLock'then8 else0 end)desc, (casewhengrantedthen0else1end) )aslock_conflict fromt_unionall groupby locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid;
以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。