From: | Joel Jacobson <joel(at)gluefinance(dot)com> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | glue(at)pgexperts(dot)com, Magnus Hagander <magnus(at)hagander(dot)net>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: [Glue] Deadlock bug |
Date: | 2010-08-20 18:38:15 |
Message-ID: | AANLkTikN1Kb86w1pLgjAfe7DYs3XBCebY6-+raZ56rj1@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Max Bowsher | 2010-08-20 18:38:18 | Re: git: uh-oh |
Previous Message | David E. Wheeler | 2010-08-20 18:36:55 | Re: Version Numbering |