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 <j...@agliodbs.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: j...@gluefinance.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

Reply via email to