From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Joel Jacobson <joel(at)gluefinance(dot)com>, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, Magnus Hagander <magnus(at)hagander(dot)net>, glue(at)pgexperts(dot)com, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Deadlock bug |
Date: | 2010-08-20 19:19:58 |
Message-ID: | 4C6ED55E.9040708@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> It *is* allowed to, and in fact has already done so. The problem is
> that it now needs a sharelock on the referenced row in order to ensure
> that the FK constraint remains satisfied, ie, nobody deletes the
> referenced row before we commit the update. In the general case where
> the referencing row is new (or has a new FK value) in the current
> transaction, such a lock is necessary for correctness. Your case would
> work if we could optimize away the FK check, but with only a limited
> view of what's happened in the current transaction, it's not always
> possible to optimize away the check.
Hmmm. It seems to me that we'd need a sharelock on the referenced row
both times. Is the below sequence missing something?
process 1 process 1 locks process 2 process 2 locks
update session; exclusive lock
session row;
update orders; exclusive lock
orders row;
share lock
session row;
update orders; exclusive lock
requested orders row
(blocks);
share lock session row;
update orders; exclusive lock
orders row;
share lock
session row;
(in this example, there is an fk orders.sessionid --> session.id )
It certainly seems that process 2 is acquiring exactly the same locks
twice, since the referenced value is never being changed. So why does
it need a share lock the 2nd time and not the first? Or is the
sharelock in the first cycle being optimized away improperly?
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
From | Date | Subject | |
---|---|---|---|
Next Message | Devrim GÜNDÜZ | 2010-08-20 19:21:00 | Re: Version Numbering |
Previous Message | David E. Wheeler | 2010-08-20 19:19:03 | Re: Version Numbering |