postgresql locks the whole table!

[Dr NoName]

Help!

I have a table that multiple processes must be able to
write to concurrently. However, it for some reason
gets locked in exclusive mode. I narrowed it down to
one SQL statement + some weirdness with foreign keys.
To debug this, I opened two psql sessions and typed in
the sql statements manually. Here is the situation:

CREATE TABLE take2
(
id serial not null,
timestamp timestamp NOT NULL DEFAULT now(),
description text,
iteration smallint,
asset_id integer,
-- FOREIGN KEY (asset_id) REFERENCES public.asset
(id), -- ON UPDATE CASCADE ON DELETE CASCADE,

primary key(id)
);

(notice that the foreign key statement is commented
out). Just to make sure I am not causing excessive
locking unintentionally , I did "set transaction
isolation level read committed" in both psql shells
(default was serializable).

Now I type the following commands:

shell 1:

1. BEGIN
2. insert into take2 values(default, 'now()', 't1', 1,
1);
shell 2:

1. BEGIN
2. insert into take2 values(default, 'now()', 't2', 1,
1);

this works.

However, if I uncomment the foreign key statement and
recreate the table, then the second shell blocks on
the insert statement. As soon as the first transaction
is either committed or rolled back, the insert
statement goes through.

My question is why??? The two insert operations do not
conflict with each other (at least not in the
real-world situation). Also, why does the foreign key
make a difference?

looking at pg_locks, I see the following:

relation | database | transaction | pid |
mode | granted
----------+----------+-------------+-------+------------------+---------
39356 | 34862 | NULL | 18671 |
AccessShareLock | t
39356 | 34862 | NULL | 18671 |
RowExclusiveLoc k | t
NULL | NULL | 9914 | 18671 |
ExclusiveLock | t
39354 | 34862 | NULL | 18671 |
AccessShareLock | t
34886 | 34862 | NULL | 18671 |
AccessShareLock | t
34886 | 34862 | NULL | 18671 |
RowShareLock | t
16759 | 34862 | NULL | 18671 |
AccessShareLock | t
(7 rows)

Where does the ExclusiveLock come from? What is being
locked?

It is critical for us to run multiple transactions
concurrently -- in fact that was one of the reasons
for choosing PostgreSQL over MySQL. There are a lot of
file system operations and other processing that need
to happen along side the DB transaction. Those things
take a long time, so there is typically up to a
5-minute span between BEGIN and COMMIT. We cannot
block the production floor for 5 minutes when a user
tries to run a transaction, so as a temporary fix, we
got rid of the begin/commit. But obviously we would
rather not lose the atomicity.

So, in summary:
why does PostgreSQL lock the entire table?
what can we do about it?

This was tested on PostgreSQL 7.4.0 and 7.3.2.

thanks in advance,

Eugene

_______________ _______________ ____
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.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

[Mike Mascari]

Dr NoName wrote:

Help!

I have a table that multiple processes must be able to
write to concurrently. However, it for some reason
gets locked in exclusive mode. I narrowed it down to
one SQL statement + some weirdness with foreign keys.
To debug this, I opened two psql sessions and typed in
the sql statements manually. Here is the situation:

CREATE TABLE take2
(
id serial not null,
timestamp timestamp NOT NULL DEFAULT now(),
description text,
iteration smallint,
asset_id integer,
-- FOREIGN KEY (asset_id) REFERENCES public.asset
(id), -- ON UPDATE CASCADE ON DELETE CASCADE,

primary key(id)
);
....
1. BEGIN
2. insert into take2 values(default, 'now()', 't1', 1,
1);
....
So, in summary:
why does PostgreSQL lock the entire table?
It isn't locking the entire table, it is locking the row of asset
where asset_id is 1 FOR UPDATE. When two simultaneous inserts occur in
the same child table for the same parent row, it is the equivalent of
two concurrent SELECT ... FOR UPDATE queries being executed against
the parent row.
what can we do about it?

Not much, I'm afraid. PostgreSQL badly needs a lock level whereby a
row is only locked for UPDATEs and DELETEs and not a pseudo SELECT ...
FOR RI_CHECK....

Mike Mascari
ma*****@mascari .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

[Jan Wieck]

Dr NoName wrote:

Help!

I have a table that multiple processes must be able to
write to concurrently. However, it for some reason
gets locked in exclusive mode. I narrowed it down to
one SQL statement + some weirdness with foreign keys.
To debug this, I opened two psql sessions and typed in
the sql statements manually. Here is the situation:

CREATE TABLE take2
(
id serial not null,
timestamp timestamp NOT NULL DEFAULT now(),
description text,
iteration smallint,
asset_id integer,
-- FOREIGN KEY (asset_id) REFERENCES public.asset
(id), -- ON UPDATE CASCADE ON DELETE CASCADE,

primary key(id)
);

(notice that the foreign key statement is commented
out). Just to make sure I am not causing excessive
locking unintentionally , I did "set transaction
isolation level read committed" in both psql shells
(default was serializable).

Now I type the following commands:

shell 1:

1. BEGIN
2. insert into take2 values(default, 'now()', 't1', 1,
1);
shell 2:

1. BEGIN
2. insert into take2 values(default, 'now()', 't2', 1,
1);

this works.

However, if I uncomment the foreign key statement and
recreate the table, then the second shell blocks on
the insert statement. As soon as the first transaction
is either committed or rolled back, the insert
statement goes through.

My question is why??? The two insert operations do not
conflict with each other (at least not in the
real-world situation). Also, why does the foreign key
make a difference?
Because PostgreSQL does not implement shared read locks on the row level
and therefore the "lightest" lock the foreign key constraint can take is
a write lock.

If you cannot make your transactons shorter (and please don't tell me
that you have user interaction going on while holding any open
transactions), then you might be able to increase your concurrency by
deferring the foreign key check until commit.
Jan

looking at pg_locks, I see the following:

relation | database | transaction | pid |
mode | granted
----------+----------+-------------+-------+------------------+---------
39356 | 34862 | NULL | 18671 |
AccessShareLock | t
39356 | 34862 | NULL | 18671 |
RowExclusiveLoc k | t
NULL | NULL | 9914 | 18671 |
ExclusiveLock | t
39354 | 34862 | NULL | 18671 |
AccessShareLock | t
34886 | 34862 | NULL | 18671 |
AccessShareLock | t
34886 | 34862 | NULL | 18671 |
RowShareLock | t
16759 | 34862 | NULL | 18671 |
AccessShareLock | t
(7 rows)

Where does the ExclusiveLock come from? What is being
locked?

It is critical for us to run multiple transactions
concurrently -- in fact that was one of the reasons
for choosing PostgreSQL over MySQL. There are a lot of
file system operations and other processing that need
to happen along side the DB transaction. Those things
take a long time, so there is typically up to a
5-minute span between BEGIN and COMMIT. We cannot
block the production floor for 5 minutes when a user
tries to run a transaction, so as a temporary fix, we
got rid of the begin/commit. But obviously we would
rather not lose the atomicity.

So, in summary:
why does PostgreSQL lock the entire table?
what can we do about it?

This was tested on PostgreSQL 7.4.0 and 7.3.2.

thanks in advance,

Eugene

_______________ _______________ ____
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.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

--
#============== =============== =============== =============== ===========#
# 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 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Greg Stark]

Dr NoName <sp********@yah oo.com> writes:

My question is why??? The two insert operations do not
conflict with each other (at least not in the
real-world situation). Also, why does the foreign key
make a difference?

It's not locking the whole table, it's locking the record that the foreign key
references. Note that they're both referencing the same foreign key.

It does this because it's afraid someone will go and delete that key before
the transaction commits. It has to take a lock that will prevent someone from
deleting the record (or updating the referenced column).

Unfortunately the only lock to choose from is an exclusive write lock. That's
overkill as you've noticed. I think this is something multiple people would
like to fix by introducing shared locks, but I wouldn't expect a solution
soon.

I don't know if there's any work-around better than just dropping the foreign
key reference.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Scott Ribe]

> My question is why??? The two insert operations do not

conflict with each other (at least not in the
real-world situation). Also, why does the foreign key
make a difference?

I don't know if this would help, but given the other explanations you've
gotten I would try setting the foreign key constraint to deferrable, then at
the beginning of the transaction defer constraints. The reasoning being that
if the check is deferred until commit, maybe the lock won't be taken until
commit, thus the window of time during which your 2 example inserts could
conflict would be more like what you expect, a brief instant.
--
Scott Ribe
sc********@kill erbytes.com
http://www.killerbytes.com/
[phone] voice
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Dr NoName]

> If you cannot make your transactons shorter (and

please don't tell me
that you have user interaction going on while
holding any open
transactions), then you might be able to increase
your concurrency by
deferring the foreign key check until commit.

oh! my! gawd!!!
THANK YOU!

_______________ _______________ ____
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Dr NoName]

> If you cannot make your transactons shorter (and

please don't tell me
that you have user interaction going on while
holding any open
transactions), then you might be able to increase
your concurrency by
deferring the foreign key check until commit.

oh! my! gawd!!!
THANK YOU! The deferred foreign key checks are exactly
what I needed. They are quite useful for other reasons
too. I think that should be the default for foreign
keys. Interestingly, the severe concurrency
degradation caused by immediate foreign key checks is
not explained in any of the documentation I looked at.

btw, there is no user interaction during the
transaction, just a lot of CPU- and IO-intensive
processing.

thanks,

Eugene

_______________ _______________ ____
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Jan Wieck]

Dr NoName wrote:

If you cannot make your transactons shorter (and
please don't tell me
that you have user interaction going on while
holding any open
transactions), then you might be able to increase
your concurrency by
deferring the foreign key check until commit.

oh! my! gawd!!!
THANK YOU! The deferred foreign key checks are exactly
what I needed. They are quite useful for other reasons
too. I think that should be the default for foreign

The way it is is the way it is defined by the standard.
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 5: Have you checked our extensive FAQ?

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

[Scott Ribe]

> The deferred foreign key checks are exactly

what I needed. They are quite useful for other reasons
too.

I believe Postgres is just following standards.

Yes, deferred is very useful for other things, like a real data model layer
mediating between UI and database--without it you have to worry about
performing inserts (and updates) in a particular order. That can be really
painful to code, and in some cases (cyclic relationships) impossible to do
except by leaving some constraints out.
--
Scott Ribe
sc********@kill erbytes.com
http://www.killerbytes.com/
[phone] voice
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Alistair Hopkins]

Just a thought...
What if you defer the foregn key constraint?
Won't this prevent the select for update until the end of the transaction,
so the lock will be as short as possible?

CONSTRAINTS

SET CONSTRAINTS affects the behavior of constraint evaluation in the
current transaction. SET CONSTRAINTS, specified in SQL3, has these allowed
parameters:

constraintlist

Comma separated list of deferrable constraint names.
mode

The constraint mode. Allowed values are DEFERRED and IMMEDIATE.

In IMMEDIATE mode, foreign key constraints are checked at the end of
each query.

In DEFERRED mode, foreign key constraints marked as DEFERRABLE are
checked only at transaction commit or until its mode is explicitly set to
IMMEDIATE. This is actually only done for foreign key constraints, so it
does not apply to UNIQUE or other constraints.

Not tried this, but...

-----Original Message-----
From: pg************* ****@postgresql .org
[mailto:pg****** ***********@pos tgresql.org]On Behalf Of Mike Mascari
Sent: 03 December 2003 17:00
To: Dr NoName
Cc: pg***********@p ostgresql.org
Subject: Re: [GENERAL] postgresql locks the whole table!
Dr NoName wrote:

Help!

I have a table that multiple processes must be able to
write to concurrently. However, it for some reason
gets locked in exclusive mode. I narrowed it down to
one SQL statement + some weirdness with foreign keys.
To debug this, I opened two psql sessions and typed in
the sql statements manually. Here is the situation:

CREATE TABLE take2
(
id serial not null,
timestamp timestamp NOT NULL DEFAULT now(),
description text,
iteration smallint,
asset_id integer,
-- FOREIGN KEY (asset_id) REFERENCES public.asset
(id), -- ON UPDATE CASCADE ON DELETE CASCADE,

primary key(id)
);
....
1. BEGIN
2. insert into take2 values(default, 'now()', 't1', 1,
1);
....
So, in summary:
why does PostgreSQL lock the entire table?
It isn't locking the entire table, it is locking the row of asset
where asset_id is 1 FOR UPDATE. When two simultaneous inserts occur in
the same child table for the same parent row, it is the equivalent of
two concurrent SELECT ... FOR UPDATE queries being executed against
the parent row.
what can we do about it?

Not much, I'm afraid. PostgreSQL badly needs a lock level whereby a
row is only locked for UPDATEs and DELETEs and not a pseudo SELECT ...
FOR RI_CHECK....

Mike Mascari
ma*****@mascari .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

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