Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 1BB0A632281 for ; Mon, 14 Jun 2010 12:37:25 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 51433-01 for ; Mon, 14 Jun 2010 15:37:17 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mailrelay101-out.ornis.com (mailrelay101-out.ornis.com [81.93.1.89]) by mail.postgresql.org (Postfix) with ESMTP id D110D6321B1 for ; Mon, 14 Jun 2010 12:37:16 -0300 (ADT) Received: from fsecure101.ornis.com ([192.168.162.101]) by mailrelay101-out.ornis.com (8.13.1/8.13.1) with ESMTP id o5EFbDgN028249 for ; Mon, 14 Jun 2010 17:37:13 +0200 Received: from unknown (HELO relay-ext.ornis.com) ([192.168.160.6]) by mailgate401.ornis.com with ESMTP; 14 Jun 2010 17:37:13 +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 o5EFbAlu003398 for ; Mon, 14 Jun 2010 17:37:11 +0200 Message-ID: <4C164C9A.5030607@acteos.com> Date: Mon, 14 Jun 2010 18:36:58 +0300 From: Elias Ghanem 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: Fwd: Dead lock Content-Type: multipart/alternative; boundary="------------030400080803020608000606" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=-10 required=5 tests=BAYES_40=-0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201006/248 X-Sequence-Number: 39104 This is a multi-part message in MIME format. --------------030400080803020608000606 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi, Actually i guess the problem is related to the way PG uses to aquire lock on the rows that will be updated. Suppose the update query will affect 5 rows: A, B, C, D and E. Apparently the folowing senario is happening: 1- Transaction1 locks row A 2- Trnasaction2 locks row B 3- Transaction1 updates row A 4- Tranasaction2 updates row B 5- Transaction1 *tries *to acquire lock on row B(and fail because row B is still locked by transaction2) 6- Transaction2 *tries *to acquire lock on row A(and fail because row A is still locked by transaction1) Hence the dead lock. Is this a plausible explanation of what is going on? If yes, what can be done to avoid the dead lock? Thanks again. -------- Original Message -------- Subject: Dead lock Date: Mon, 14 Jun 2010 14:50:43 +0300 From: Elias Ghanem To: pgsql-performance@postgresql.org 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. --------------030400080803020608000606 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hi,
Actually i guess the problem is related to the way PG uses to aquire lock on the rows that will be updated.
Suppose the update query will affect 5 rows: A, B, C, D and E.
Apparently the folowing senario is happening:
    1- Transaction1 locks row A
    2- Trnasaction2 locks row B
    3- Transaction1 updates row A
    4- Tranasaction2 updates row B
    5- Transaction1 tries to acquire lock on row B(and fail because row B is still locked by transaction2)
    6- Transaction2 tries to acquire lock on row A(and fail because row A is still locked by transaction1)
Hence the dead lock.
Is this a plausible explanation of what is going on?
If yes, what can be done to avoid the dead lock?
Thanks again.


-------- Original Message --------
Subject: Dead lock
Date: Mon, 14 Jun 2010 14:50:43 +0300
From: Elias Ghanem <e.ghanem@acteos.com>
To: pgsql-performance@postgresql.org


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.

--------------030400080803020608000606--