From: | Elias Ghanem <e(dot)ghanem(at)acteos(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Dead lock |
Date: | 2010-06-14 11:50:43 |
Message-ID: | 4C161793.2090105@acteos.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Molesworth | 2010-06-14 12:54:24 | Re: Dead lock |
Previous Message | Bruce Momjian | 2010-06-14 11:14:27 | Re: Command Prompt 8.4.4 PRMs compiled with debug/assert enabled |