Re: Dead lock

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