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