Avoid PG::TRDeadlockDetected

Question

I am using the below function in a bulk insert, usually the bulk insert has around 60 rows and each row will have the below function. But from time to time I get PG::TRDeadlockDetected: ERROR: deadlock detected (see the full error below).

How can I avoid this? Can I add a EXCEPTION which will return 0 in case this happens, I will prefer not to, but the bulk insert is very convenient for me.

CREATE OR REPLACE FUNCTION "univ"."gc_title_desc"(IN _title text, IN _desc text, OUT result_id int4) RETURNS "int4" 
AS $BODY$BEGIN
LOOP
BEGIN
WITH sel AS (
  SELECT id
  FROM   univ.results
  WHERE  title = _title AND description = _desc
  )
, ins AS (
  INSERT INTO univ.results (title, description)
  SELECT _title, _desc
  WHERE  NOT EXISTS (SELECT 1 FROM sel)
  RETURNING id
  )
SELECT id
FROM   sel NATURAL FULL OUTER JOIN ins 
INTO   result_id;

EXCEPTION WHEN UNIQUE_VIOLATION THEN     -- inserted in concurrent session.
    RAISE NOTICE 'It actually happened!'; -- hardly ever happens
END;

EXIT WHEN result_id IS NOT NULL;
END LOOP;
END
$BODY$
LANGUAGE plpgsql
COST 100
CALLED ON NULL INPUT
SECURITY INVOKER
VOLATILE;

Error:

2015-02-25T15:11:15.078Z 3564 TID-oti3yx4ms WARN: PG::TRDeadlockDetected: ERROR:  deadlock detected
DETAIL:  Process 15507 waits for ShareLock on transaction 14912613; blocked by process 15690.
Process 15690 waits for ShareLock on transaction 14912617; blocked by process 15507.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (12728,26) in relation "uniq_search_results_title_description"
SQL statement "WITH sel AS (
    SELECT id
    FROM   univ.search_results
    WHERE  title = _title AND description = _desc
  )
  , ins AS (
    INSERT INTO univ.search_results (title, description)
    SELECT _title, _desc
    WHERE  NOT EXISTS (SELECT 1 FROM sel)
    RETURNING id
  )
  SELECT id
  FROM sel NATURAL FULL OUTER JOIN ins"
PL/pgSQL function univ.gc_title_desc(text,text) line 5 at SQL statement

Answer 1

Adding a new value into a unique index locks out (until commit) any other transaction from inserting the same value. When doing that with multiple values in the same transaction, these locks accumulate.

Then when several parallel transactions do that concurrently in no particular order, that creates the opportunity for transactions to inter-lock each other, which causes the error you're reporting.

There's no easy technical solution, because it's more of a conceptual problem.

At the conceptual level, there's a contradiction between making sure that different transactions can't insert an identical value into a unique index (that part being enforced by the database engine), and running these in parallel without having them to care about what the others are doing.

At a higher level (inside the function it's too late), the transactions have to be serialized, or the work partitioned so that parallel transactions can not hit the same part of the unique index at the same time.