From: Elias Ghanem <e.ghanem@acteos.com>
To: pgsql-performance@postgresql.org
Subject: Fwd: Dead lock
Date: Mon, 14 Jun 2010 18:36:58 +0300
Message-ID: <4C164C9A.5030607@acteos.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: e.ghanem@acteos.com
Subject: Re: Fwd: Dead lock
In-Reply-To: <4C164C9A.5030607@acteos.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox