postgresql locks the whole table! - Mailing list pgsql-general

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 |
RowExclusiveLock | 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/