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
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] |