Thread: blocking INSERTs

I want to do the following:

BEGIN;
SELECT ... FROM table WHERE a = 1 FOR UPDATE;
UPDATE table SET ... WHERE a = 1;
if that resturns zero then
INSERT INTO table (...) VALUES (...);
END;

The problem is that I need to avoid race conditions.  Sometimes I get
primary key exceptions on the INSERT.

I think I need to lock the table in share mode to keep inserts from
happening, but that blocks vacuums, and blocked vacuums block other
things behind them.  So how do I get around this?

Joseph Shraibman <jks@selectacast.net> writes:

> I want to do the following:
>
> BEGIN;
> SELECT ... FROM table WHERE a = 1 FOR UPDATE;
> UPDATE table SET ... WHERE a = 1;
> if that resturns zero then
> INSERT INTO table (...) VALUES (...);
> END;
>
> The problem is that I need to avoid race conditions.  Sometimes I get
> primary key exceptions on the INSERT.
>
> I think I need to lock the table in share mode to keep inserts from
> happening, but that blocks vacuums, and blocked vacuums block other
> things behind them.  So how do I get around this?

Ummm...  Don't lock the table for very long?  :)  If you commit
your transaction promptly then the table will be unlocked again.

-Doug

On Tue, 7 Jun 2005, Joseph Shraibman wrote:

> BEGIN;
> SELECT ... FROM table WHERE a = 1 FOR UPDATE;
> UPDATE table SET ... WHERE a = 1;
> if that resturns zero then
> INSERT INTO table (...) VALUES (...);
> END;
>
> The problem is that I need to avoid race conditions.  Sometimes I get
> primary key exceptions on the INSERT.

PG uses row locking and the problem above is that if there is no row with
a=1 then there is no row to lock in the first select. The update will
update zero rows and then the you come to the insert and nothing is locked
so 2 transactions can do the insert at the same time. This means that one
of the 2 transactions will fail.

If you use pg 8.0 maybe this example might help you:

http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE

and if not you are correct that you need to lock the table (or just accept
that it fail sometimes and handle that failure in the client).

--
/Dennis

On Wed, 2005-06-08 at 05:31, Joseph Shraibman wrote:
> I want to do the following:
>
> BEGIN;
> SELECT ... FROM table WHERE a = 1 FOR UPDATE;
> UPDATE table SET ... WHERE a = 1;
> if that resturns zero then
> INSERT INTO table (...) VALUES (...);
> END;
>
> The problem is that I need to avoid race conditions.  Sometimes I get
> primary key exceptions on the INSERT.
>
> I think I need to lock the table in share mode to keep inserts from
> happening, but that blocks vacuums, and blocked vacuums block other
> things behind them.  So how do I get around this?
>

I think there's no way to avoid the race condition, I got to this
conclusion while following past discussions (which were many of them,
look for "insert or update" for example).
There is though a solution starting with 8.0, namely you should place a
save point before the inserts which are susceptible to fail, and roll
back to that save point on failure. Then you can update while still
keeping the transaction running. The strategy would be then "insert
first, update if failed".

HTH,
Csaba.