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)
> );

...

> 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
mascarm(at)mascari(dot)com