Debugging deadlocks

From: "Guy Rouillier" <guyr(at)masergy(dot)com>
To: "PostgreSQL General" <pgsql-general(at)postgresql(dot)org>
Subject: Debugging deadlocks
Date: 2005-03-27 06:54:28
Message-ID: CC1CF380F4D70844B01D45982E671B2348E753@mtxexch01.add0.masergy.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm getting the following in the server log:

2005-03-27 06:04:21 GMT estat DETAIL: Process 20928 waits for ShareLock
on transaction 7751823; blocked by process 20929.
Process 20929 waits for ShareLock on transaction 7768115;
blocked by process 20928.
2005-03-27 06:04:21 GMT estat CONTEXT: SQL statement "SELECT 1 FROM
ONLY "rumba"."service_plane" x WHERE "service_plane_id" = $1 FOR UPDATE
OF x"
SQL statement " INSERT INTO FIVE_MIN_STATS_200503 (traffic_id,
service_plane_id, datestamp, sample_bucket_no, service_id,
data_collector_device_id, bit_delta, packet_delta, bit_rate,
packet_rate, bit_drop_delta, packet_drop_delta, bit_drop_rate,
packet_drop_rate, updated) VALUES (
'1','4','2005-03-21','1','MV008816','3', 0, 0, 0,
0,0,0,0,0,'N' )"
PL/pgSQL function "initialize_five_minute_samples" line 34 at
execute statement
SQL statement "SELECT INITIALIZE_FIVE_MINUTE_SAMPLES( $1 , $2
, $3 , $4 , $5 , 1, 288)"
PL/pgSQL function "ins_updt_five_min_sample" line 28 at perform

FIVE_MIN_STATS_200503 has a foreign key into "rumba"."service_plane".
The service_plane table is a reference table, i.e., a fixed set of
values used only to validate foreign keys. So the code doesn't have any
update statements on that table. I'm assuming PostgreSQL is generating
that SQL to validate the foreign key. But why is it selecting for
update?

--
Guy Rouillier

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Guy Rouillier 2005-03-27 07:06:47 Re: Command prompt window (8.0)
Previous Message Tom Lane 2005-03-27 06:49:31 Re: create or replace trigger?