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

Subject:

Dead lock

Date:

Mon, 14 Jun 2010 14:50:43 +0300

From:

To:

On Mon, Jun 14, 2010 at 10:36 AM, Elias Ghanem <e.ghanem@acteos.com> wrote:

=20
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:
=C2=A0=C2=A0=C2=A0 1- Transaction1 locks row A
=C2=A0=C2=A0=C2=A0 2- Trnasaction2 locks row B
=C2=A0=C2=A0=C2=A0 3- Transaction1 updates row A
=C2=A0=C2=A0=C2=A0 4- Tranasaction2 updates row B
=C2=A0=C2=A0=C2=A0 5- Transaction1 tries to acquire lock on row B(an= d fail because row B is still locked by transaction2)
=C2=A0=C2=A0=C2=A0 6- Transaction2 tries to acquire lock on row A(an= d 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:=C2=A0 deadlock detected
DETAIL:=C2=A0 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:=C2=A0 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:=C2=A0 let one transaction wait untill the other commits or rollback the= n 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=C2=A0 VARCHAR;
BEGIN=C2=A0=C2=A0=C2=A0
=C2=A0=C2=A0=C2=A0 C:=3D 0;
=C2=A0=C2=A0=C2=A0 LOOP
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 EXIT WHEN C > 15;
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 C:=3D C+1;
=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 SELECT INTO DUMMY DBLINK_EXEC('&#= 39;CONNECTION_STRING TO DB3', ''UPDATE IN_FICHE_PRODUIT SET VALIDE =3D 1'');
=C2=A0=C2=A0=C2=A0 RAISE NOTICE ''%, %'', C,P;
=C2=A0=C2=A0=C2=A0 END LOOP;
END;'
LANGUAGE 'plpgsql';

Thanks for your time.