Postgresql – Avoid PG::TRDeadlockDetected

postgresqlpostgresql-9.3

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

Best Answer

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.

Related Solutions

PostgreSQL – How to Resolve Deadlock on CREATE TABLE

Let's assume that the target table of the INSERT ("TableD") has a column with a foreign key to "TableC". An INSERT to this table must acquire a shared lock on "TableC" and will keep that lock until the transaction ends.

Say two concurrent transactions, T1 and T2, start with such an INSERT. Each takes a shared lock on "TableC".

Later on, in order to do a CREATE TABLE involving a foreign key to "TableC", T1 must acquire an access exclusive lock on "TableC".

Since T2 has already a shared lock on "TableC", T1 will be put to wait until T2 commits or rollbacks.

At this point, if T2 tries to create a table also involving a foreign key to "TableC" (which again implies acquiring an access exclusive lock on "TableC"), it must wait for T1 to commit or rollback, because T1 holds a shared lock on "TableC".

So now T1 is waiting for T2 to finish and T2 is waiting for T1 to finish: there's the deadlock.

To avoid this situation, an explicit and exclusive lock may be taken at the beginning of the transaction, or at least at the beginning of the sequence of instructions that can't be run safely in parallel by concurrent transactions.

Postgresql – How to avoid duplications using a function

I have re-tested your function and it seems to work as advertised.

If, in fact a duplicate value was entered, you would get an exception that would roll back the transaction.
The function is designed to trap such an exception (UNIQUE_VIOLATION) and to try again, which would find the (now) existing row and return the existing id.

Your report of "duplications" doesn't seem possible. There must be a misunderstanding somewhere.

I only have minor improvements to offer:

Make that a UNIQUE CONSTRAINT, that's cleaner than a unique index, while doing the same for the case at hand. Since you already have a unique index, you can use the special form:

ALTER TABLE univ.landings ADD CONSTRAINT landings_name_key UNIQUE
USING INDEX index_name univ.uniq_index_landings_on_name;

You can simplify the function a bit. Your additional variable landing_name is just a waste:

CREATE OR REPLACE FUNCTION univ.gc_landing(_name text, OUT landing_id int)
  RETURNS int AS
$func$
BEGIN
LOOP
   BEGIN
   WITH sel AS (
     SELECT id
     FROM   univ.landings
     WHERE  name = _name
     )
   , ins AS (
     INSERT INTO univ.landings (name)
     SELECT _name
     WHERE  NOT EXISTS (SELECT 1 FROM sel)
     RETURNING id
     )
   SELECT id
   FROM   sel NATURAL FULL OUTER JOIN ins 
   INTO   landing_id;

   EXCEPTION WHEN UNIQUE_VIOLATION THEN
       RAISE NOTICE 'It actually happened!';
   END;

   EXIT WHEN landing_id IS NOT NULL;
END LOOP;
END
$func$  LANGUAGE plpgsql;

But note that this function is designed for individual inserts. For bulk operations like you mention, a different approach might be much more efficient: fold duplicates in the source before you bulk-insert into the target table.

Best Answer

Related Solutions

PostgreSQL – How to Resolve Deadlock on CREATE TABLE

Postgresql – How to avoid duplications using a function

Related Question