long lock in my pg

From: "zhiwei(dot)li(at)melot(dot)cn" <zhiwei(dot)li(at)melot(dot)cn>
To: pgsql-translators <pgsql-translators(at)postgresql(dot)org>
Subject: long lock in my pg
Date: 2015-06-08 10:51:32
Message-ID: 2015060818512695740130@melot.cn
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-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

Browse pgsql-translators by date

  From Date Subject
Next Message Walter Willmertinger 2015-06-30 13:31:22 Problem with error messages
Previous Message Alvaro Herrera 2015-05-12 14:09:35 [tgl@sss.pgh.pa.us: [HACKERS] Back-branch update releases planned for next week]