From: Andy Colson <andy@squeakycode.net>
To: Elias Ghanem <e.ghanem@acteos.com>
To: pgsql-performance@postgresql.org
Subject: Re: Dead lock
Date: Mon, 14 Jun 2010 08:59:40 -0500
Message-ID: <4C1635CC.8060801@squeakycode.net> (raw)
In-Reply-To: <4C161793.2090105@acteos.com>
References: <4C161793.2090105@acteos.com>
On 06/14/2010 06:50 AM, Elias Ghanem wrote:
> 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.
I think PG is doing what you want.. if you think about it. You start two transactions at the same time. A transaction is defined as "do this set of operations, all of which must succeed or fail atomicly". One transaction cannot update the exact same row as another transaction because that would break the second transactions "must succeed" rule.
-Andy
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: andy@squeakycode.net, e.ghanem@acteos.com
Subject: Re: Dead lock
In-Reply-To: <4C1635CC.8060801@squeakycode.net>
* 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