Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 6DEF16302CB
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 14 Jun 2010 09:50:04 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 70156-10
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon, 14 Jun 2010 12:49:55 +0000 (UTC)
X-Greylist: delayed 00:58:49.900118 by SQLgrey-1.7.6
Received: from mailrelay100-out.ornis.com (mailrelay100-out.ornis.com
	[81.93.1.91])
	by mail.postgresql.org (Postfix) with ESMTP id D722D62F1B2
	for <pgsql-performance@postgresql.org>;
	Mon, 14 Jun 2010 09:49:56 -0300 (ADT)
Received: from fsecure100.ornis.com ([192.168.162.100])
	by mailrelay100-out.ornis.com (8.13.1/8.13.1) with ESMTP id
	o5EBp3U3010591
	for <pgsql-performance@postgresql.org>; Mon, 14 Jun 2010 13:51:03 +0200
Received: from unknown (HELO relay-ext.ornis.com) ([192.168.160.6])
	by mailgate402.ornis.com with ESMTP; 14 Jun 2010 13:51:03 +0200
Received: from [192.168.0.71] ([80.79.155.178])
	by relay-ext.ornis.com (8.12.9/8.12.3) with ESMTP id o5EBp0lu000804
	for <pgsql-performance@postgresql.org>; Mon, 14 Jun 2010 13:51:01 +0200
Message-ID: <4C161793.2090105@acteos.com>
Date: Mon, 14 Jun 2010 14:50:43 +0300
From: Elias Ghanem <e.ghanem@acteos.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB;
	rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Dead lock
Content-Type: multipart/alternative;
	boundary="------------060101050504020706040401"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.499 tagged_above=-10 required=5
	tests=BAYES_05=-0.5, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201006/242
X-Sequence-Number: 39098

This is a multi-part message in MIME format.
--------------060101050504020706040401
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi all,
I have 2 data bases trying to perform an update query at the same time 
on a same table in a third data base using db link.
I'm getting a dead lock exception:
ERROR:  deadlock detected
DETAIL:  Process 27305 waits for ShareLock on transaction 55575; blocked 
by process 27304.
Process 27304 waits for ShareLock on transaction 55576; blocked by 
process 27305.
HINT:  See server log for query details.
Actually the folowing function is installed on 2 dbs DB1 and DB2. This 
function issues an update query on DB3.
When this function is running simultaneously on DB1 and DB2, it produces 
a dead lock making one of the functions (in DB1 or DB2) stop with the 
above exception:
Is it normal? should'nt postgres be able to handle such situations, for 
ex:  let one transaction wait untill the other commits or rollback then 
continue with the first transaction?
Is there a parameter that should be set in postgresql.conf to allow 
handling of concurrent transaction...?

CREATE OR REPLACE FUNCTION TEST_DB_LINK(VARCHAR)
RETURNS VOID AS'
DECLARE
C INTEGER;
P ALIAS FOR $1;
DUMMY  VARCHAR;
BEGIN
     C:= 0;
     LOOP
         EXIT WHEN C > 15;
         C:= C+1;
         SELECT INTO DUMMY DBLINK_EXEC(''CONNECTION_STRING TO DB3', 
''UPDATE IN_FICHE_PRODUIT SET VALIDE = 1'');
     RAISE NOTICE ''%, %'', C,P;
     END LOOP;
END;'
LANGUAGE 'plpgsql';

Thanks for your time.

--------------060101050504020706040401
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>

<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi all,<br>
I have 2 data bases trying to perform an update query at the same time
on a same table in a third data base using db link.<br>
I'm getting a dead lock exception:<br>
<font color="#3366ff">ERROR:&nbsp; deadlock detected<br>
DETAIL:&nbsp; Process 27305 waits for ShareLock on transaction 55575;
blocked by process 27304.<br>
Process 27304 waits for ShareLock on transaction 55576; blocked by
process 27305.<br>
HINT:&nbsp; See server log for query details.<br>
</font>Actually the folowing function is installed on 2 dbs DB1 and
DB2. This function issues an update query on DB3.<br>
When this function is running simultaneously on DB1 and DB2, it
produces a dead lock making one of the functions (in DB1 or DB2) stop
with the above exception:<br>
Is it normal? should'nt postgres be able to handle such situations, for
ex:&nbsp; let one transaction wait untill the other commits or rollback then
continue with the first transaction?<br>
Is there a parameter that should be set in postgresql.conf to allow
handling of concurrent transaction...?<br>
<br>
CREATE OR REPLACE FUNCTION TEST_DB_LINK(VARCHAR)<br>
RETURNS VOID AS'<br>
DECLARE <br>
C INTEGER;<br>
P ALIAS FOR $1;<br>
DUMMY&nbsp; VARCHAR;<br>
BEGIN&nbsp;&nbsp;&nbsp; <br>
&nbsp;&nbsp;&nbsp; C:= 0;<br>
&nbsp;&nbsp;&nbsp; LOOP<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; EXIT WHEN C &gt; 15;<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; C:= C+1;<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; SELECT INTO DUMMY DBLINK_EXEC(''CONNECTION_STRING TO DB3',
''UPDATE IN_FICHE_PRODUIT SET VALIDE = 1'');<br>
&nbsp;&nbsp;&nbsp; RAISE NOTICE ''%, %'', C,P;<br>
&nbsp;&nbsp;&nbsp; END LOOP;<br>
END;'<br>
LANGUAGE 'plpgsql';<br>
<br>
Thanks for your time.<br>
</body>
</html>

--------------060101050504020706040401--