Possible for two simple updates on the same table to deadlock?

Question

Table A is:

id integer
version varchar
data jsonb (large data 1mb)
fkToBid integer (references B.id constraint)

Table B is:

id integer
other... 

Processes are aggressively running the two updates below, in any order and outside any transaction.

Updated records in table A sometimes refer to the same record in table B. Also, sometimes the same A record is updated.

UPDATE A.version WHERE A.id=:id
and
UPDATE A.data WHERE A.id=:id

Why, or can, this deadlock? Is it because updated records in table A refer to the same row in table B? Can this deadlock for another reason?

Why do I see an AccessShareLock on the B pk index for these update requests?

Answer 1

Why do I see an AccessShareLock on the B pk index for these update requests?

Postgres takes that lock when it checks foreign key existence in the parent table for an insert or update. From RI_FKey_check in src/backend/utils/adt/ri_triggers.c:

    /*
     * Get the relation descriptors of the FK and PK tables.
     *
     * pk_rel is opened in RowShareLock mode since that's what our eventual
     * SELECT FOR KEY SHARE will get on it.
     */
    fk_rel = trigdata->tg_relation;
    pk_rel = table_open(riinfo->pk_relid, RowShareLock);

The question is why the foreign key relationship is checked at all, since neither of your updates affects the foreign key.

The most likely answer is that Postgres cannot detect that the update will not affect the relationship. From RI_FKey_fk_upd_check_required in the same source file:

    /*
     * If the original row was inserted by our own transaction, we must fire
     * the trigger whether or not the keys are equal.  This is because our
     * UPDATE will invalidate the INSERT so that the INSERT RI trigger will
     * not do anything; so we had better do the UPDATE check.  (We could skip
     * this if we knew the INSERT trigger already fired, but there is no easy
     * way to know that.)
     */
    xminDatum = slot_getsysattr(oldslot, MinTransactionIdAttributeNumber, &isnull);
    Assert(!isnull);
    xmin = DatumGetTransactionId(xminDatum);
    if (TransactionIdIsCurrentTransactionId(xmin))
        return true;

Despite what the code comment says, this limitation applies to a row that is updated twice, as well as the stated case when a row is inserted then updated.

Repro:

  CREATE TABLE b
  (
    id   BIGINT PRIMARY KEY
  );

  INSERT INTO b (id)
  VALUES(1);

  CREATE TABLE a
  (
    id BIGINT PRIMARY KEY,
    version INTEGER NOT NULL,
    data INTEGER NOT NULL,
    b_id BIGINT NOT NULL CONSTRAINT fk_a_b_id REFERENCES b (id)
  );

  INSERT INTO a (id, version, data, b_id)
  VALUES (1, 1, 1, 1);

explain (analyze, costs off)
UPDATE a
SET version = 2
WHERE id = 1;

explain (analyze, costs off)
UPDATE a
SET data = 3
WHERE id = 1;

db<>fiddle demo

Notice only the second update explain includes:

Trigger for constraint fk_a_b_id: time=0.386 calls=1

(Yes, Postgres uses per-row internal triggers to check and enforce RI.)

On Postgres 9.3 and later, this RI check uses FOR KEY SHARE for better concurrency, but this doesn't mean it is deadlock-proof.

Anyway, despite what the question states, it seems multiple updates are being sent in one command. That seems to be required to get Postgres to unnecessarily perform the FK check that produces the parent table lock seen.

If you have other processes accessing the parent table with SELECT FOR UPDATE, you could try changing that to SELECT FOR NO KEY UPDATE as recommended in this Stack Overflow answer by the primary author of the 'Improve concurrency of foreign key locking' Postgres 9.3 patch.

Answer 2

What comes to mind is an old post I wrote about Uber using PostgreSQL and then leaving it.

Here is that post : Why can MySQL handle multiple updates concurrently and PostgreSQL can't?

In that old post of mine, I wrote how PostgreSQL changes the internal ID ctid of each row when updating a column EVEN IF THE COLUMN IS NOT INDEXED.

I see in the question that Table A and Table B have a foreign key relationship between them.

If both tables are being simultaneously updated, the parent would have to wait on the child to update or maybe the child has to wait on the parent to update. Wait for what ??? For each row's ctid to change. That behavior has to serialize on one direction (parent -> child) or the other (child -> parent).

That's my best guess.