Thread: table locking.. FK tables locking on insert
I have several tables with common FKs.. the problem occurs when performing a large number of inserts on any of the tables at the same time. The first to start inserting seems to get a lock on the referenced FK tables and doesn't release them until its done, forcing the other apps to just wait... and wait..since they also seem to want a lock for their FK tables..... something just doesnt seem right here :( when I remove the FKs, everything goes fine.. but I need my foreign keys.. really I do. What does postgres do here? or is the problem in the jdbc driver.. or maybe the server? (using Jboss 3.2.1, hibernate and 7.3.2 postgres).. any ideas?
On Tue, 28 Oct 2003, Ati Rosselet wrote: > I have several tables with common FKs.. the problem occurs when > performing a large number of inserts on any of the tables at the same > time. The first to start inserting seems to get a lock on the > referenced FK tables and doesn't release them until its done, forcing > the other apps to just wait... and wait..since they also seem to want a > lock for their FK tables..... something just doesnt seem right here :( > > when I remove the FKs, everything goes fine.. but I need my foreign > keys.. really I do. > > What does postgres do here? or is the problem in the jdbc driver.. or > maybe the server? (using Jboss 3.2.1, hibernate and 7.3.2 postgres).. > > any ideas? A lock is grabbed on the associated pk row (so that some other transaction can't delete it). Unfortunately that lock conflicts with other fk modifications attempting to grab the lock. Two possible solutions involve read locks or dirty reads. Neither of these is trivial, one will get implemented eventually, but there's no concrete timetable. For more details, you may want to look up info in the archives.
Stephan Szabo wrote: > On Tue, 28 Oct 2003, Ati Rosselet wrote: > >> I have several tables with common FKs.. the problem occurs when >> performing a large number of inserts on any of the tables at the same >> time. The first to start inserting seems to get a lock on the >> referenced FK tables and doesn't release them until its done, forcing >> the other apps to just wait... and wait..since they also seem to want a >> lock for their FK tables..... something just doesnt seem right here :( >> >> when I remove the FKs, everything goes fine.. but I need my foreign >> keys.. really I do. >> >> What does postgres do here? or is the problem in the jdbc driver.. or >> maybe the server? (using Jboss 3.2.1, hibernate and 7.3.2 postgres).. >> >> any ideas? > > A lock is grabbed on the associated pk row (so that some other transaction > can't delete it). Unfortunately that lock conflicts with other fk > modifications attempting to grab the lock. Two possible solutions involve > read locks or dirty reads. Neither of these is trivial, one will get > implemented eventually, but there's no concrete timetable. > > For more details, you may want to look up info in the archives. This problem is fixed for v7.4 and backpatched into v7.3.4. There will be a 7.3.5 release shortly that fixes a possible foreign key violation related to deferred checking due to this change. So you might want to wait for 7.3.5. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Mon, 3 Nov 2003, Jan Wieck wrote: > Stephan Szabo wrote: > > > On Tue, 28 Oct 2003, Ati Rosselet wrote: > > > >> I have several tables with common FKs.. the problem occurs when > >> performing a large number of inserts on any of the tables at the same > >> time. The first to start inserting seems to get a lock on the > >> referenced FK tables and doesn't release them until its done, forcing > >> the other apps to just wait... and wait..since they also seem to want a > >> lock for their FK tables..... something just doesnt seem right here :( > >> > >> when I remove the FKs, everything goes fine.. but I need my foreign > >> keys.. really I do. > >> > >> What does postgres do here? or is the problem in the jdbc driver.. or > >> maybe the server? (using Jboss 3.2.1, hibernate and 7.3.2 postgres).. > >> > >> any ideas? > > > > A lock is grabbed on the associated pk row (so that some other transaction > > can't delete it). Unfortunately that lock conflicts with other fk > > modifications attempting to grab the lock. Two possible solutions involve > > read locks or dirty reads. Neither of these is trivial, one will get > > implemented eventually, but there's no concrete timetable. > > > > For more details, you may want to look up info in the archives. > > This problem is fixed for v7.4 and backpatched into v7.3.4. There will Are you sure, Jan? His problem seems to be related to inserts which AFAIK still run the for update queries that might lock against other inserting transactions as opposed to the update problems that I think that patch fixed.
Stephan Szabo wrote: > On Mon, 3 Nov 2003, Jan Wieck wrote: > >> Stephan Szabo wrote: >> >> > On Tue, 28 Oct 2003, Ati Rosselet wrote: >> > >> >> I have several tables with common FKs.. the problem occurs when >> >> performing a large number of inserts on any of the tables at the same >> >> time. The first to start inserting seems to get a lock on the >> >> referenced FK tables and doesn't release them until its done, forcing >> >> the other apps to just wait... and wait..since they also seem to want a >> >> lock for their FK tables..... something just doesnt seem right here :( >> >> >> >> when I remove the FKs, everything goes fine.. but I need my foreign >> >> keys.. really I do. >> >> >> >> What does postgres do here? or is the problem in the jdbc driver.. or >> >> maybe the server? (using Jboss 3.2.1, hibernate and 7.3.2 postgres).. >> >> >> >> any ideas? >> > >> > A lock is grabbed on the associated pk row (so that some other transaction >> > can't delete it). Unfortunately that lock conflicts with other fk >> > modifications attempting to grab the lock. Two possible solutions involve >> > read locks or dirty reads. Neither of these is trivial, one will get >> > implemented eventually, but there's no concrete timetable. >> > >> > For more details, you may want to look up info in the archives. >> >> This problem is fixed for v7.4 and backpatched into v7.3.4. There will > > Are you sure, Jan? His problem seems to be related to inserts which AFAIK > still run the for update queries that might lock against other inserting > transactions as opposed to the update problems that I think that patch > fixed. Hmmm ... er ... you're right. His only option currently is deferring the check then. I think we really need read locks someday. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #