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: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Mike Mascari
Sent: 03 December 2003 17:00
To: Dr NoName
Cc: pgsql-general@postgresql.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
mascarm@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
