Thread: deadlock problems with foreign keys
I've a severe problem with deadlocks in postgres, when using referential integrity it's quite easy to trigger deadlocks.I think the may be a bug in ri_trigger.c (discussed later). Here's some short example: create table languages ( id integer not null, name text not null, primary key(id) ); create table entry ( id integer not null, lang_id integer, sometext text, primary key (id), foreign key ( lang_id) references languages (id) ); insert into languages values (1, 'english'); insert into languages values (2, 'german'); insert into entry values (1, 1, 'text 1'); insert into entry values (2, 1, 'text 2'); transaction A: begin; transaction A: update entry set sometext='text 1.1' where id=1; transaction A: .... do more time-consuming processing here... meanwhile, B: begin; B: update entry set sometext='text 2.1' where id=2; -- both processes hang now I think this is too much locking here, because the logfile show's something like this: 'select 1 from "languages" where id=$1 for update' (2 times). Now I've a lot of tables (around 30) and use referential integrity a lot on ~10 columns (language, country....) , and withmore fields it's very easy to deadlock the whole system (it happens a lot in my web applicaiton with ~20 concorrent users). IMHO the "select ... for update" on languages is not necessary, since I do not want to update "lang_id", but I might be wrong.The other problem is, that this will make postgres in benchmarks very slow (with many concurrent connections), at leastif the application is not trivial. IMO the problem is in ri_trigger.c around line 390: /* ---------- * The query string built is * SELECT 1 FROMONLY <pktable> WHERE pkatt1 = $1 [AND ...] * The type id's for the $ parameters are those of the * correspondingFK attributes. Thus, SPI_prepare could * eventually fail if the parser cannot identify some way * howto compare these two types by '='. * ---------- */ Any ideas if this is a bug or simply strict SQL standard? Best regards,Mario Weilguni
There was no deadlock in 7.2 with what was provided -- but the second transaction was blocked from doing it's thing by the lock from the first. Perhaps a deadlock is caused by 'do other stuff'? I will agree that a FOR UPDATE is heavy. There is no intention to update the record, we just want to ensure it's NOT updated or deleted. A FOR PREVENT UPDATE lock may be preferable and it should block any other locks while allowing the lock to be 'upgraded' in the case where you hold the only PREVENT UPDATE lock. It wouldn't be exclusive to itself, only other types of locks. All that said, SET CONSTRAINTS ALL DEFERRED at the beginning of the transaction also caused a block on the update with the second transaction. That interests me. Why doesn't the second transaction go through and block the first from using COMMIT? -- Rod Taylor This message represents the official view of the voices in my head ----- Original Message ----- From: "Mario Weilguni" <mario.weilguni@icomedias.com> To: "Postgresql Mailinglist (E-Mail)" <pgsql-hackers@postgresql.org> Sent: Thursday, March 28, 2002 9:44 AM Subject: [HACKERS] deadlock problems with foreign keys I've a severe problem with deadlocks in postgres, when using referential integrity it's quite easy to trigger deadlocks. I think the may be a bug in ri_trigger.c (discussed later). Here's some short example: create table languages ( id integer not null, name text not null, primary key(id) ); create table entry ( id integer not null, lang_id integer, sometext text, primary key (id), foreign key ( lang_id) references languages (id) ); insert into languages values (1, 'english'); insert into languages values (2, 'german'); insert into entry values (1, 1, 'text 1'); insert into entry values (2, 1, 'text 2'); transaction A: begin; transaction A: update entry set sometext='text 1.1' where id=1; transaction A: .... do more time-consuming processing here... meanwhile, B: begin; B: update entry set sometext='text 2.1' where id=2; -- both processes hang now I think this is too much locking here, because the logfile show's something like this: 'select 1 from "languages" where id=$1 for update' (2 times). Now I've a lot of tables (around 30) and use referential integrity a lot on ~10 columns (language, country....) , and with more fields it's very easy to deadlock the whole system (it happens a lot in my web applicaiton with ~20 concorrent users). IMHO the "select ... for update" on languages is not necessary, since I do not want to update "lang_id", but I might be wrong. The other problem is, that this will make postgres in benchmarks very slow (with many concurrent connections), at least if the application is not trivial. IMO the problem is in ri_trigger.c around line 390: /* ---------- * The query string built is * SELECT 1 FROM ONLY <pktable> WHERE pkatt1 = $1 [AND ...] * The type id's for the $ parameters are those of the * corresponding FK attributes. Thus, SPI_prepare could * eventually fail if the parser cannot identify some way * how to compare these two types by '='. * ---------- */ Any ideas if this is a bug or simply strict SQL standard? Best regards, Mario Weilguni ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Mario Weilguni wrote: > I've a severe problem with deadlocks in postgres, when using referential integrity it's quite easy to trigger deadlocks.I think the may be a bug in ri_trigger.c (discussed later). Here's some short example: > > create table languages ( > id integer not null, > name text not null, > primary key(id) > ); > > create table entry ( > id integer not null, > lang_id integer, > sometext text, > primary key (id), > foreign key ( lang_id ) references languages (id) > ); > > insert into languages values (1, 'english'); > insert into languages values (2, 'german'); > > insert into entry values (1, 1, 'text 1'); > insert into entry values (2, 1, 'text 2'); > > > transaction A: begin; > transaction A: update entry set sometext='text 1.1' where id=1; > transaction A: .... do more time-consuming processing here... > meanwhile, B: begin; > B: update entry set sometext='text 2.1' where id=2; > > -- both processes hang now Cannot reproduce that problem in v7.2. Only B blocks until A either commits or rolls back. So what exactly is your "more time-consuming processing"? > > I think this is too much locking here, because the logfile show's something like this: > 'select 1 from "languages" where id=$1 for update' (2 times). > > Now I've a lot of tables (around 30) and use referential integrity a lot on ~10 columns (language, country....) , and withmore fields it's very easy to deadlock the whole system (it happens a lot in my web applicaiton with ~20 concorrent users). > > IMHO the "select ... for update" on languages is not necessary, since I do not want to update "lang_id", but I might bewrong. The other problem is, that this will make postgres in benchmarks very slow (with many concurrent connections), atleast if the application is not trivial. > > IMO the problem is in ri_trigger.c around line 390: > /* ---------- > * The query string built is > * SELECT 1 FROM ONLY <pktable> WHERE pkatt1 = $1 [AND ...] > * The type id's for the $ parameters are those of the > * corresponding FK attributes. Thus, SPI_prepare could > * eventually fail if the parser cannot identify some way > * how to compare these two types by '='. > * ---------- > */ > > Any ideas if this is a bug or simply strict SQL standard? It does a SELECT ... FOR UPDATE because we don't have a SELECT ... AND PLEASE DO NOT REMOVE. If we would only check if the PK is there now, another concurrent transaction could delete the PK, it's owncheck cannot see our uncommitted row yet and we end up with a violation. And if you look at the comment a fewlines up, it explains why we cannot skip the check even if the key value doesn't change. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
just to clarify this, my example does not deadlock. I wanted to provide a simple expample, because my application has 109(this time I counted) tables with a few (~10) "central" tables like "languages", where a lot of other table referenceto. And deadlocks are quite easy to trigger with more tables. I'll try to create a testcase and post it. -----Ursprüngliche Nachricht----- Von: Rod Taylor [mailto:rbt@zort.ca] Gesendet: Donnerstag, 28. März 2002 16:15 An: Mario Weilguni; Hackers List Betreff: Re: [HACKERS] deadlock problems with foreign keys There was no deadlock in 7.2 with what was provided -- but the second transaction was blocked from doing it's thing by the lock from the first. Perhaps a deadlock is caused by 'do other stuff'? I will agree that a FOR UPDATE is heavy. There is no intention to update the record, we just want to ensure it's NOT updated or deleted. A FOR PREVENT UPDATE lock may be preferable and it should block any other locks while allowing the lock to be 'upgraded' in the case where you hold the only PREVENT UPDATE lock. It wouldn't be exclusive to itself, only other types of locks. All that said, SET CONSTRAINTS ALL DEFERRED at the beginning of the transaction also caused a block on the update with the second transaction. That interests me. Why doesn't the second transaction go through and block the first from using COMMIT? -- Rod Taylor This message represents the official view of the voices in my head ----- Original Message ----- From: "Mario Weilguni" <mario.weilguni@icomedias.com> To: "Postgresql Mailinglist (E-Mail)" <pgsql-hackers@postgresql.org> Sent: Thursday, March 28, 2002 9:44 AM Subject: [HACKERS] deadlock problems with foreign keys I've a severe problem with deadlocks in postgres, when using referential integrity it's quite easy to trigger deadlocks. I think the may be a bug in ri_trigger.c (discussed later). Here's some short example: create table languages ( id integer not null, name text not null, primary key(id) ); create table entry ( id integer not null, lang_id integer, sometext text, primary key (id), foreign key ( lang_id) references languages (id) ); insert into languages values (1, 'english'); insert into languages values (2, 'german'); insert into entry values (1, 1, 'text 1'); insert into entry values (2, 1, 'text 2'); transaction A: begin; transaction A: update entry set sometext='text 1.1' where id=1; transaction A: .... do more time-consuming processing here... meanwhile, B: begin; B: update entry set sometext='text 2.1' where id=2; -- both processes hang now I think this is too much locking here, because the logfile show's something like this: 'select 1 from "languages" where id=$1 for update' (2 times). Now I've a lot of tables (around 30) and use referential integrity a lot on ~10 columns (language, country....) , and with more fields it's very easy to deadlock the whole system (it happens a lot in my web applicaiton with ~20 concorrent users). IMHO the "select ... for update" on languages is not necessary, since I do not want to update "lang_id", but I might be wrong. The other problem is, that this will make postgres in benchmarks very slow (with many concurrent connections), at least if the application is not trivial. IMO the problem is in ri_trigger.c around line 390: /* ---------- * The query string built is * SELECT 1 FROM ONLY <pktable> WHERE pkatt1 = $1 [AND ...] * The type id's for the $ parameters are those of the * corresponding FK attributes. Thus, SPI_prepare could * eventually fail if the parser cannot identify some way * how to compare these two types by '='. * ---------- */ Any ideas if this is a bug or simply strict SQL standard? Best regards, Mario Weilguni ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
On Thu, 28 Mar 2002, Mario Weilguni wrote: > I've a severe problem with deadlocks in postgres, when using > referential integrity it's quite easy to trigger deadlocks. I think > the may be a bug in ri_trigger.c (discussed later). Here's some short You might want to see recent messages about foreign keys for more information (in the RI triggers and schemas thread, specifically the message from Alex Hayward). Jan's example of a failure case is why it does what it currently does since AFAIK we don't have a weaker mechanism available to us through SPI currently that is still sufficiently strong.
> It does a SELECT ... FOR UPDATE because we don't have a > SELECT ... AND PLEASE DO NOT REMOVE. > > If we would only check if the PK is there now, another > concurrent transaction could delete the PK, it's own check > cannot see our uncommitted row yet and we end up with a > violation. And if you look at the comment a few lines up, it > explains why we cannot skip the check even if the key value > doesn't change. But it does not apply here since there are no "on update set default" here. So IMO this case should not apply if there arenot "on update set default"? Or are other cases where the same restriction applies? The problem is this, at the moment there is no such thing as "row level locking" in postgres when you use foreign key constraints.This really hits concurrency. _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Rod Taylor wrote: > There was no deadlock in 7.2 with what was provided -- but the second > transaction was blocked from doing it's thing by the lock from the > first. Perhaps a deadlock is caused by 'do other stuff'? > > I will agree that a FOR UPDATE is heavy. There is no intention to > update the record, we just want to ensure it's NOT updated or deleted. > A FOR PREVENT UPDATE lock may be preferable and it should block any > other locks while allowing the lock to be 'upgraded' in the case where > you hold the only PREVENT UPDATE lock. It wouldn't be exclusive to > itself, only other types of locks. > > > All that said, SET CONSTRAINTS ALL DEFERRED at the beginning of the > transaction also caused a block on the update with the second > transaction. That interests me. Why doesn't the second transaction > go through and block the first from using COMMIT? SET CONSTRAINTS ALL DEFERRED only set's DEFERRABLE constraints to DEFERRED. Constraints default to NOT DEFERRABLE, so unless you explicitly allowed it at table creation, you did a noop. Jan > > > -- > Rod Taylor > > This message represents the official view of the voices in my head > > ----- Original Message ----- > From: "Mario Weilguni" <mario.weilguni@icomedias.com> > To: "Postgresql Mailinglist (E-Mail)" <pgsql-hackers@postgresql.org> > Sent: Thursday, March 28, 2002 9:44 AM > Subject: [HACKERS] deadlock problems with foreign keys > > > I've a severe problem with deadlocks in postgres, when using > referential integrity it's quite easy to trigger deadlocks. I think > the may be a bug in ri_trigger.c (discussed later). Here's some short > example: > > create table languages ( > id integer not null, > name text not null, > primary key(id) > ); > > create table entry ( > id integer not null, > lang_id integer, > sometext text, > primary key (id), > foreign key ( lang_id ) references languages (id) > ); > > insert into languages values (1, 'english'); > insert into languages values (2, 'german'); > > insert into entry values (1, 1, 'text 1'); > insert into entry values (2, 1, 'text 2'); > > > transaction A: begin; > transaction A: update entry set sometext='text 1.1' where id=1; > transaction A: .... do more time-consuming processing here... > meanwhile, B: begin; > B: update entry set sometext='text 2.1' where id=2; > > -- both processes hang now > > I think this is too much locking here, because the logfile show's > something like this: > 'select 1 from "languages" where id=$1 for update' (2 times). > > Now I've a lot of tables (around 30) and use referential integrity a > lot on ~10 columns (language, country....) , and with more fields it's > very easy to deadlock the whole system (it happens a lot in my web > applicaiton with ~20 concorrent users). > > IMHO the "select ... for update" on languages is not necessary, since > I do not want to update "lang_id", but I might be wrong. The other > problem is, that this will make postgres in benchmarks very slow (with > many concurrent connections), at least if the application is not > trivial. > > IMO the problem is in ri_trigger.c around line 390: > /* ---------- > * The query string built is > * SELECT 1 FROM ONLY <pktable> WHERE pkatt1 = $1 [AND ...] > * The type id's for the $ parameters are those of the > * corresponding FK attributes. Thus, SPI_prepare could > * eventually fail if the parser cannot identify some way > * how to compare these two types by '='. > * ---------- > */ > > Any ideas if this is a bug or simply strict SQL standard? > > Best regards, > Mario Weilguni > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com