Re: Deadlock bug - Mailing list pgsql-hackers

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> The surprising thing is that a particular row is (using the
> identifiers from the attachment):

> Process 2 updates a particular row without blocking.
> Process 1 updates the same row, which blocks.
> Process 2 updates the same row again (with *exactly* the same UPDATE
> statement), which fails with a deadlock.

> I'm not sure I consider that a bug, but it moves the needle on the
> astonishment meter.

OK, I looked a bit closer.  The first update in process 2 is changing
a row in B that has an FK reference to an already-modified row in A.
The only reason that doesn't block is that we optimize away taking a
sharelock on the referenced row if the update doesn't change the FK
column(s), as this doesn't.  However, the *second* update doesn't
get the benefit of that optimization, as per this comment in trigger.c:

                    * There is one exception when updating FK tables: if the
                    * updated row was inserted by our own transaction and the
                    * FK is deferred, we still need to fire the trigger. This
                    * is because our UPDATE will invalidate the INSERT so the
                    * end-of-transaction INSERT RI trigger will not do
                    * anything, so we have to do the check for the UPDATE
                    * anyway.

So it goes and waits for sharelock on the A row, and then you have a
deadlock because process 1 has exclusive lock on that row and is already
blocked waiting for process 2.

The Glue guys aren't the first to complain of this behavior, so it'd
be nice to improve it.

If we knew that the already-updated row was one for which we'd been able
to optimize away the FK check, then we could do so again on the second
update (assuming it still didn't change the FK columns), but I don't see
any practical way to know that.  We only have our hands on the current
update's old and new tuples, not on previous versions; and there's no
convenient way to find the previous version because the update ctid
links run the other way.

[ thinks for awhile... ]  Conceivably we could get around this by
programming the ON INSERT trigger to chase forward to the latest live
row version, rather than just doing nothing when the initially inserted
row has been outdated.  It'd be a pretty ticklish thing to get right,
though.

                       regards, tom lane