Re: [Glue] Deadlock bug - Mailing list pgsql-hackers
| From | Joel Jacobson |
|---|---|
| Subject | Re: [Glue] Deadlock bug |
| Date | |
| Msg-id | AANLkTikN1Kb86w1pLgjAfe7DYs3XBCebY6-+raZ56rj1@mail.gmail.com Whole thread Raw |
| In response to | Re: [Glue] Deadlock bug (Josh Berkus <josh@agliodbs.com>) |
| Responses |
Re: [Glue] Deadlock bug
Re: [Glue] Deadlock bug |
| List | pgsql-hackers |
In my example,
--
Best regards,
Joel Jacobson
Glue Finance
E: jj@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
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@agliodbs.com>
On 8/20/10 7:18 AM, Tom Lane wrote:That's correct. This is the generic example I was talking about earlier
> 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.
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@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
pgsql-hackers by date: