Postgresql – Is the theory on INSERT deadlocks correct

deadlockinsertpostgresqltransaction

I originally had this (as three separate PG queries, from my applications):

INSERT INTO table1 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
INSERT INTO table2 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
INSERT INTO table3 (col1, col2, col3) VALUES
(
    (SELECT id FROM table1 WHERE blabla = $1),
    (SELECT id FROM table2 WHERE blabla = $2),
$3);

After finally learning about transactions (or at least I thought that I had finally learned about them…), I wanted to group three INSERTs into one transaction because they are deeply related. The third INSERT expects those other tables to be populated before it runs, and the two first INSERTs are meaningless without the third, "main" one. So I naively did this:

BEGIN;
    INSERT INTO table1 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
    INSERT INTO table2 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
    INSERT INTO table3 (col1, col2, col3) VALUES
    (
        (SELECT id FROM table1 WHERE blabla = $1),
        (SELECT id FROM table2 WHERE blabla = $2),
    $3);
COMMIT;

Sadly, it now started logging a bunch of this when running the third INSERT:

pg_query_params(): Query failed: ERROR:  current transaction is aborted, commands ignored until end of transaction block
pg_query_params(): Query failed: ERROR:  deadlock detected
DETAIL:  Process 6276 waits for RowExclusiveLock on relation 939516 of database 53142; blocked by process 7508.
Process 7508 waits for AccessExclusiveLock on relation 939490 of database 53142; blocked by process 6276.

This confused me fundamentally, but then I thought that maybe, the transaction block causes the third INSERT to not "see" the previous INSERTs because they have not yet been committed? Is that it?

I have now changed it into:

BEGIN;
    INSERT INTO table1 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
    INSERT INTO table2 (col1) VALUES ($1) ON CONFLICT DO NOTHING;
COMMIT;
INSERT INTO table3 (col1, col2, col3) VALUES
(
    (SELECT id FROM table1 WHERE blabla = $1),
    (SELECT id FROM table2 WHERE blabla = $2),
$3);

So far, it has not logged any deadlocks. It appears as if my theory is correct, but I wish to hear if this is a false conclusion by me.

Also, if this is the case, I guess there is no way to "group" all three INSERTs? Since I have to commit after the first two for the third to be possible?

Also, I'm confused as to why, in the second code example, the third INSERT's internal SELECTs don't just return "null" values since that's what normally happens when the values don't exist in the tables. I assume it "knows" that it's inside a transaction with relevant changes having been made? That's got to be it?

Best Answer

Process 7508 waits for AccessExclusiveLock on relation 939490

Nothing in your shown code would take an AccessExclusiveLock on a relation. The 7508 member of your deadlocked pair is doing something different. Look in the log file to see if there is a clue about what that is. (The log file will show what statement it was running when the deadlock occured, but that might not be the same statement as what took out the lock, but at least it will be more information than you currently have)

maybe, the transaction block causes the third INSERT to not "see" the previous INSERTs because they have not yet been committed? Is that it?

No, a statement in a transaction can see INSERTs done by earlier statements in the same transaction.

Related Question