Thread: Strange deadlock in foreign key check
I am seeing some odd locking behaviour when deleting a parent record (Postgres 9.4.4).
Setup:create table pk_rel (id int primary key);
create table fk_rel (pk_id int references pk_rel (id), x text unique);
insert into pk_rel values (1), (2);
insert into fk_rel values (1, 'a');
This example works as I expected.
Session 1:
=>begin;
=>update fk_rel set pk_id = 2;
Session 2:
=>delete from pk_rel where id = 1;
=>begin;
=>update fk_rel set pk_id = 2;
Session 2:
=>delete from pk_rel where id = 1;
[Fails with FK violation]
But the following case, I do not understand.
=>begin;
=>update fk_rel set x = 'b';
=>delete from pk_rel where id = 1;
=>insert into fk_rel values (1, 'a');
Sophia Wright wrote: > I am seeing some odd locking behaviour when deleting a parent record > (Postgres 9.4.4). Somewhere in the triggers for FK checks we do "SELECT FOR KEY SHARE" of the PK tuples when the FK tuples are altered; and conversely when we remove tuples from the PK side we need to ensure that there are no referencing tuples in the FK side. The code doesn't distinguish between indexes used in foreign keys from other indexes that *could* be used in foreign keys. Therefore your UNIQUE in the declaration for "x" may be making it difficult for you. I don't have the time to go through this right now, but please try and see what happens if you remove the UNIQUE from that column. We discussed about only considering indexes actually referenced by foreign keys instead of all of them, but there are some fine points to keep in mind if you do that, so we never got around to implementing that optimization. I don't have any immediate suggestion for what to do to work around this issue. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 08/06/2015 07:19 AM, Sophia Wright wrote: > I am seeing some odd locking behaviour when deleting a parent record > (Postgres 9.4.4). > > Setup: > create table pk_rel (id int primary key); > create table fk_rel (pk_id int references pk_rel (id), x text unique); > insert into pk_rel values (1), (2); > insert into fk_rel values (1, 'a'); > > > This example works as I expected. > > Session 1: > =>begin; > =>update fk_rel set pk_id = 2; > > Session 2: > =>delete from pk_rel where id = 1; > [Fails with FK violation] > > > But the following case, I do not understand. > > Session 1: > =>begin; > =>update fk_rel set x = 'b'; > > Session 2: > =>delete from pk_rel where id = 1; > [Blocks waiting for Session 1] > > Session 1: > =>insert into fk_rel values (1, 'a'); > [Blocks waiting for Session 2] > > At this point, Session 1 fails with a deadlock, and Session fails with a > FK violation. > > So, why is this happening? Why doesn't Session 2 fail the FK check > immediately, like in the first case? And why is it that updating > fk_rel.x introduces a lock conflict, but updating fk_rel.pk_id does not? Because fk_rel.x has a UNIQUE index on it. If you try the above using this table definition: create table fk_rel (pk_id int references pk_rel (id), x text); it works as in your first case. See here: http://www.postgresql.org/docs/9.4/interactive/explicit-locking.html FOR UPDATE .... The FOR UPDATE lock mode is also acquired by any DELETE on a row, and also by an UPDATE that modifies the values on certain columns. Currently, the set of columns considered for the UPDATE case are those that have a unique index on them that can be used in a foreign key (so partial indexes and expressional indexes are not considered), but this may change in the future. -- Adrian Klaver adrian.klaver@aklaver.com
On Fri, Aug 7, 2015 at 1:11 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Sophia Wright wrote:
> I am seeing some odd locking behaviour when deleting a parent record
> (Postgres 9.4.4).
Somewhere in the triggers for FK checks we do "SELECT FOR KEY SHARE" of
the PK tuples when the FK tuples are altered; and conversely when we
remove tuples from the PK side we need to ensure that there are no
referencing tuples in the FK side. The code doesn't distinguish between
indexes used in foreign keys from other indexes that *could* be used in
foreign keys. Therefore your UNIQUE in the declaration for "x" may be
making it difficult for you. I don't have the time to go through this
right now, but please try and see what happens if you remove the UNIQUE
from that column.
We discussed about only considering indexes actually referenced by
foreign keys instead of all of them, but there are some fine points to
keep in mind if you do that, so we never got around to implementing that
optimization. I don't have any immediate suggestion for what to do to
work around this issue.
Thanks. Removing the UNIQUE constraint prevents this, but I'm still not clear on why it happens...
Based on your explanation, I can see how a UNIQUE index on the PK side would cause problems. But on the FK side, I'm not sure where this fits in. Why lock the UNIQUE field, but not lock the FK field itself? Isn't it the only part that's relevant here?On 08/06/2015 09:29 AM, Sophia Wright wrote: > On Fri, Aug 7, 2015 at 1:11 AM, Alvaro Herrera <alvherre@2ndquadrant.com > <mailto:alvherre@2ndquadrant.com>> wrote: > > Sophia Wright wrote: > > I am seeing some odd locking behaviour when deleting a parent record > > (Postgres 9.4.4). > > Somewhere in the triggers for FK checks we do "SELECT FOR KEY SHARE" of > the PK tuples when the FK tuples are altered; and conversely when we > remove tuples from the PK side we need to ensure that there are no > referencing tuples in the FK side. The code doesn't distinguish between > indexes used in foreign keys from other indexes that *could* be used in > foreign keys. Therefore your UNIQUE in the declaration for "x" may be > making it difficult for you. I don't have the time to go through this > right now, but please try and see what happens if you remove the UNIQUE > from that column. > > We discussed about only considering indexes actually referenced by > foreign keys instead of all of them, but there are some fine points to > keep in mind if you do that, so we never got around to implementing that > optimization. I don't have any immediate suggestion for what to do to > work around this issue. > > > Thanks. Removing the UNIQUE constraint prevents this, but I'm still not > clear on why it happens... > > Based on your explanation, I can see how a UNIQUE index on the PK side > would cause problems. But on the FK side, I'm not sure where this fits > in. Why lock the UNIQUE field, but not lock the FK field itself? Isn't > it the only part that's relevant here? I would also take a look at Alvaro's explanation. My understanding is that for locking purposes the UNIQUE index is considered sort of like a FK, as it could be used as a FK. This then leads to the FOR UPDATE lock, which from Table 13.3 at the link I sent, conflicts with all the other row locks. -- Adrian Klaver adrian.klaver@aklaver.com
On Fri, Aug 7, 2015 at 2:46 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
I would also take a look at Alvaro's explanation. My understanding is that for locking purposes the UNIQUE index is considered sort of like a FK, as it could be used as a FK. This then leads to the FOR UPDATE lock, which from Table 13.3 at the link I sent, conflicts with all the other row locks.
Like I said, I think it would make sense for a UNIQUE index in pk_rel, i.e. the fk_rel insert would try to lock pk_rel.id with KEY SHARE, and would end up locking any other UNIQUE fields as a result.
But I can't see why the pk_rel deletion would want a KEY SHARE lock on fk_rel. It must be using FOR KEY SHARE rather than FOR UPDATE, since it does not conflict with the update of fk_rel.pk_id in the first example. So why lock fk_rel at all, if the lock doesn't include fk_rel.pk_id? Isn't that the only bit that matters to a pk_rel deletion?
On 08/06/2015 04:24 PM, Sophia Wright wrote: > On Fri, Aug 7, 2015 at 2:46 AM, Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > I would also take a look at Alvaro's explanation. My understanding > is that for locking purposes the UNIQUE index is considered sort of > like a FK, as it could be used as a FK. This then leads to the FOR > UPDATE lock, which from Table 13.3 at the link I sent, conflicts > with all the other row locks. > > Like I said, I think it would make sense for a UNIQUE index in pk_rel, > i.e. the fk_rel insert would try to lock pk_rel.id <http://pk_rel.id> > with KEY SHARE, and would end up locking any other UNIQUE fields as a > result. > > But I can't see why the pk_rel deletion would want a KEY SHARE lock on > fk_rel. It must be using FOR KEY SHARE rather than FOR UPDATE, since it > does not conflict with the update of fk_rel.pk_id in the first example. > So why lock fk_rel at all, if the lock doesn't include fk_rel.pk_id? > Isn't that the only bit that matters to a pk_rel deletion? Well what I see below from the 10000 ft level: Session 1: =>begin; =>update fk_rel set x = 'b'; http://www.postgresql.org/docs/9.4/interactive/explicit-locking.html#LOCKING-ROWS "The FOR UPDATE lock mode is also acquired by any DELETE on a row, and also by an UPDATE that modifies the values on certain columns. Currently, the set of columns considered for the UPDATE case are those that have a unique index on them that can be used in a foreign key (so partial indexes and expressional indexes are not considered), but this may change in the future." You have a UNIQUE index on x, so when you UPDATE it you get a FOR UPDATE lock that from Table 13-3. Conflicting Row-level Locks conflicts with all other locks. Session 2: =>delete from pk_rel where id = 1; [Blocks waiting for Session 1] You want to DELETE from pk_rel an id that has a FK dependency to the row that is now locked above, so the DELETE waits pending the lock release above. Session 1: =>insert into fk_rel values (1, 'a'); [Blocks waiting for Session 2] You want to INSERT a row into fk_rel that has an pk_id that you are trying to DELETE above, so this is waiting to to see what the DELETE does and the DELETE is waiting to see what the UPDATE does and the INSERT is waiting to see what they both do. So until the UPDATE commits/rollbacks the DELETE and INSERT are stuck on what do about the DELETE id=1/ INSERT pk_id=1 dilemma. -- Adrian Klaver adrian.klaver@aklaver.com