table locking.. FK tables locking on insert

[Ati Rosselet]

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?
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Stephan Szabo]

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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Jan Wieck]

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. #
#================================================= = Ja******@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Stephan Szabo]

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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Jan Wieck]

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. #
#================================================= = Ja******@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster