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;
以上为个人经验,希望能给大家一个参考,也希望大家多多支持毛票票。如有错误或未考虑完全的地方,望不吝赐教。