Re: 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)
> );
>
> (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 |
> 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/
>
> ---------------------------(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. #
#================================================== JanWieck(at)Yahoo(dot)com #