From: zhiwei.li@melot.cn <zhiwei.li@melot.cn>
To: pgsql-translators <pgsql-translators@postgresql.org>
Subject: long lock in my pg
Date: Mon, 8 Jun 2015 18:51:32 +0800
Message-ID: <2015060818512695740130@melot.cn> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-translators>
hi .
my postgres version is 9.3.5 。and i use pgbouncer with client to pg 。 yesterday i have a plpgsql procedure with：
CREATE OR REPLACE FUNCTION kkplay.p_kb_room_info_hot_update_onlinecount
(
IN i_userid integer,
IN i_onlinecount integer,
OUT o_tagcode text
)
RETURNS text AS
$$
begin
update kkplay.kb_room_info_hot set online_count=i_onlinecount where user_id=i_userid;
o_tagcode := '00000000';
exception when others then
o_tagcode := '01';
end
$$
LANGUAGE 'plpgsql';
this update is frequently . i accept it lock in short time 。 but i use the following view to monitor db lock
create view lock_monitor as
SELECT bl.pid AS blocked_pid,
a.usename AS blocked_user,
a.client_addr AS blocked_ip,
ka.query AS blocking_statement,
now() - ka.query_start AS blocking_duration,
kl.pid AS blocking_pid,
ka.usename AS blocking_user,
ka.client_addr AS blocking_ip,
a.query AS blocked_statement,
now() - a.query_start AS blocked_duration
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid
JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;
i get the resault:
blocked_pid|blocked_user|blocked_ip|blocking_statement|blocking_duration|blocking_pid|blocking_user|blocking_ip|blocked_statement|blocked_duration
7474|kkplay|127.0.0.1|select * from kkplay.p_kb_room_info_hot_update_onlinecount($1,$2)|04:38:20.702674|7473|kkplay|127.0.0.1|select * from kkplay.p_kb_room_info_hot_update_onlinecount($1,$2)|04:38:20.689211
lock 4 hours. then cpu use near 100% 。then i killed pid 7473 and 7474 then system return normal working。
i dont know this easy sql why cause lock longtime。 is my application have some problem or this is bug to pg。
thank you for you help.
zhiweiLi
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-translators@postgresql.org
Cc: zhiwei.li@melot.cn
Subject: Re: long lock in my pg
In-Reply-To: <2015060818512695740130@melot.cn>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox