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';
