Re: [Glue] Deadlock bug

In my example,

Process 1: Process 2:
BEGIN;
SELECT pg_backend_pid();
BEGIN;
SELECT
pg_backend_pid();
UPDATE A SET Col1 = 1 WHERE AID = 1;
SELECT * FROM vLocks WHERE PID IN (2165,2157);
UPDATE B SET Col2 =
1 WHERE BID = 2;
SELECT * FROM vLocks
WHERE PID IN (2165,2157);
UPDATE B SET Col2 = 1 WHERE BID = 2;
SELECT * FROM vLocks WHERE PID IN (2165,2157);
UPDATE B SET Col2 =
1 WHERE BID = 2;
SELECT * FROM vLocks
WHERE PID IN (2165,2157);

Process 2 is aborted due to deadlock, while process 1 is allowed to commit.

If the locking logic would be modified to allow process 2 to go through, and
instead abort process 1, I understand some other scenarios would of course
be affected, where the situation would be handled in a less optimal way.

Is there any example of scenarios where it is optimal to handle this kind of
locking situation in this way?

I am totally fine living with a feature, which is a problem in some cases,
and something good in other cases, as long as the good cases are more common
than the problem cases.

Another question, Tom referred to a comment in
src/backend/command/trigger.c.
My example does not contain any triggers, nor insert commands. Is the
trigger.c-comment still relevant or is it a misunderstanding?

2010/8/20 Josh Berkus <josh(at)agliodbs(dot)com>

> On 8/20/10 7:18 AM, Tom Lane wrote:
> > It does go through without any deadlock, *if* there is no foreign key
> > involved. You didn't tell us exactly what the FK relationship is, but
> > I suspect the reason for the deadlock is that one process is trying to
> > update a row that references some row already updated by the other.
> > That will require a row-level share lock on the referenced row, so you
> > can get a deadlock.
>
> That's correct. This is the generic example I was talking about earlier
> on -hackers. I'm not certain it's a bug per spec; I wanted to talk
> through with Kevin what we *should* be doing in this situation.
>
> This is one example of a set of user-hostile FK-related deadlock
> behavior we have. I'm just not certain it's logically possible to
> improve it.
>
> --
> -- Josh Berkus
> PostgreSQL Experts Inc.
> http://www.pgexperts.com
>

--
Best regards,

Joel Jacobson
Glue Finance

E: jj(at)gluefinance(dot)com
T: +46 70 360 38 01

Postal address:
Glue Finance AB
Box 549
114 11 Stockholm
Sweden

Visiting address:
Glue Finance AB
Birger Jarlsgatan 14
114 34 Stockholm
Sweden