5

Setup

I have a bulk insert function set_interactions(arg_rows text) that looks like this:

with inserts as (
    insert into interaction (
        thing_id,
        associate_id, created_time)
    select t->>'thing_id', t->>'associate_id', now() from
    json_array_elements(arg_rows::json)  t
    ON CONFLICT (thing_id, associate_id) DO NOTHING
    RETURNING thing_id, associate_id
) select into insert_count count(*) from inserts;

-- Followed by an insert in an unrelated table that has two triggers, neither of which touch any of the tables here (also not by any of their triggers, etc.)

(I wrap it that way because I need to get a count of actual inserts, without the "fake row updates" trick.)

The table interaction has:

  1. Just one constraint: A multi-column primary key (thing_id, associate_id)
  2. No indices
  3. Just one trigger: After insert, for each row.

The trigger does this:

DECLARE associateId text;

BEGIN

-- Go out and get the associate_id for this thing_id
BEGIN
    SELECT thing.associate_id INTO STRICT associateId FROM thing WHERE thing.id = NEW.thing_id;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE EXCEPTION 'Could not map the thing to an associate!';
    WHEN TOO_MANY_ROWS THEN
        RAISE EXCEPTION 'Could not map the thing to a SINGLE associate!'; -- thing PK should prevent this
END;

-- We don't want to add an association between an associate interacting with their own things
IF associateId != NEW.associate_id THEN

    -- Insert the new association, if it doesn't yet exist
    INSERT INTO associations ("thing_owner", "associate")
    VALUES (associateId, NEW.associate_id)
    ON CONFLICT DO NOTHING;

END IF;

RETURN NULL;

END;

Both interactions and associations have no more columns than you see in the above statements.

Problem

Occasionally, I get an error deadlock detected from PostgreSQL 9.6.5 when the application calls set_interactions(). It may call it with 1-100 rows' worth of data, unsorted; "conflicting" batches may or may not have identical input (at the whole batch level or for each conflicting row).

Error details:

deadlock detected
while inserting index tuple (37605,46) in relation "associations"
SQL statement INSERT INTO associations ("thing_owner", "associate")
    VALUES (associateId, NEW.associate_id)
    ON CONFLICT DO NOTHING;
PL/pgSQL function aud.addfriendship() line 19 at SQL statement
SQL statement "with inserts as (
        insert into interaction (
            thing_id,
            associate_id, created_time)
        select t->>'thing_id', t->>'associate_id', now() from
        json_array_elements(arg_rows::json)  t
        ON CONFLICT (thing_id, associate_id) DO NOTHING
        RETURNING thing_id, associate_id
    ) select                  count(*) from inserts"
PL/pgSQL function setinteractions(text) line 7 at SQL statement
Process 31370 waits for ShareLock on transaction 111519214; blocked by process 31418.
Process 31418 waits for ShareLock on transaction 111519211; blocked by process 31370.
error: deadlock detected

What I've tried

I thought that maybe the function was sometimes being called with duplicate data in a single call. Not so: That instead results in a guaranteed error, ON CONFLICT DO UPDATE command cannot affect row a second time.

I'm not able to reproduce the deadlock even trying 1,000 calls to set_interactions() all at once with identical parameters, or even with identical pairs of rows having (different within the pair) thing_id and associate_id values but other values too, so they don't get optimized away somehow before hitting PostgreSQL (they also shouldn't get optimized away by the database, since the function is marked volatile.) This is from a single-threaded back end; but at the same time, the application itself only runs one such back end in production, where the deadlock is occurring. I've even tried running these 1,000 calls against a full copy of the production database, and even under load from a second back end, and additionally from pgAdmin via a very-long-running query that selects from interactions. They succeed without complaint.

https://rcoh.svbtle.com/postgres-unique-constraints-can-cause-deadlock mentions trying to avoid relying on a unique index (which is what the PK amounts to, as I understand it) when inserting duplicates. However, that was before ON CONFLICT DO UPDATE, which I thought would solve this issue.

How is this query deadlocking "randomly", and how can I fix it? (Also, why can't I reproduce it with the above method?)

6

The ON CONFLICT clause can prevent duplicate key errors. There can still be friction with concurrent transactions trying to enter the same keys or update the same rows. So it's no insurance against deadlocks.

Most importantly, add a consistent order to input rows with ORDER BY. To make sure the order is enforced I use a CTE, which materializes the result. (I think it should work with a subquery, too; just to be sure.) Else, mutually entangled inserts trying to enter identical index tuples in the unique index can lead to the deadlock you observed. The manual:

The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order.

Also, since set_interactions() is a PL/pgSQL function this is simpler & cheaper:

WITH data AS (
   SELECT t->>'thing_id' AS t_id, t->>'associate_id' AS a_id
   -- Or, if not type text, cast right away:
   -- SELECT (t->>'thing_id')::int AS t_id, (t->>'associate_id')::int AS a_id
   FROM   json_array_elements(arg_rows::json) t
   ORDER  BY 1, 2  -- deterministic, stable order (!!)
   )
INSERT INTO interaction (thing_id, associate_id, created_time)
SELECT t_id, a_id, now()
FROM   data
ON     CONFLICT (thing_id, associate_id) DO NOTHING;

GET DIAGNOSTICS insert_count = ROW_COUNT;

No need for another CTE, RETURNING and another count(*). More:

The trigger function looks bloated, too. No need for a nested block, since you are not catching errors, only raising exceptions that roll back the whole transaction either way. And the exceptions are pointless, too.

The trigger function boils down to:

BEGIN
   -- Insert the new association, if it doesn't yet exist
   INSERT INTO associations (thing_owner, associate)
   SELECT t.associate_id, NEW.associate_id
   FROM   thing t
   WHERE  t.id = NEW.thing_id          --     -- PK guarantees 0 or 1 result
   AND    t.associate_id <> NEW.associate_id  -- exclude association to self
   ON     CONFLICT DO NOTHING;

   RETURN NULL;
END

You might remove the trigger and the function set_interactions() altogether and just run this query, doing everything useful I can see in the question:

WITH data AS (
   SELECT (t->>'thing_id')::int AS t_id, (t->>'associate_id')::int AS a_id  -- asuming int
   FROM   json_array_elements(arg_rows::json) t
   ORDER  BY 1, 2  -- (!!)
   )
 , ins_inter AS (
   INSERT INTO interaction (thing_id, associate_id, created_time)
   SELECT t_id, a_id, now()
   FROM   data
   ON     CONFLICT (thing_id, associate_id) DO NOTHING
   RETURNING thing_id, associate_id
   )
 , ins_ass AS (
   INSERT INTO associations (thing_owner, associate)
   SELECT t.associate_id, i.associate_id
   FROM   ins_inter i
   JOIN   thing     t ON t.id = i.thing_id
                     AND t.associate_id <> i.associate_id  -- exclude association to self
   ON     CONFLICT DO NOTHING
   )
SELECT count(*) FROM ins_inter;

Now, I don't see any more chance for deadlocks. Of course, all other transactions possibly also writing to the same table concurrently must stick to the same order of rows.

If that's not possible and you are still considering SKIP LOCKED, see:

  • OK, thanks for the confirmation. Just tying up a last loose thread: The exception does actually get thrown there (only NO_DATA_FOUND) and I thought that EXCEPTION WHEN was "catching" it by definition. My guess is that (per my previous comment) if I want to keep the EXCEPTION WHEN I don't need a nested block in this case, but if I had more than one EXCEPTION handler (in the original I consider this to be "one" handler with multiple WHENs) then I would need a nested block for each one in order to differentiate--otherwise they all get defined at the whole-function-block level. – Kev Jan 15 '18 at 18:08
  • 1
    @Kev: Yes, you are catching / trapping two particular exceptions, but all you do is RAISE EXCEPTION so you are not catching them after all. The only effect is a custom error message. The whole transaction is rolled back either way. A nested block would make sense to actually catch an exception (and not raise another exception instead), so that control can pass to the next statement after the nested block. The way you have it, all you get is extra cost. You can have the same, cheaper without nested block, place the EXCEPTION clause in the outer block directly. – Erwin Brandstetter Jan 15 '18 at 18:30
  • 1
    To make sure the order is enforced I use a CTE, which materializes the result is no longer true in Postgres 12 for non-mutating, non-recursive CTEs. MATERIALIZED must be used. – mınxomaτ Sep 6 at 9:12
  • @mınxomaτ: Notably so. But in this case it's ok as the CTE was just overly cautious and the query will keep working as desired with a subquery (as suspected already above). – Erwin Brandstetter Sep 9 at 0:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.