Hi,
> Instead, I was thinking more like:
>
> ...
> ELSE
> UPDATE ...
Sorry: I neglected that for the nut of the gnarly part.
Now you have to factor how MVCC behaves. My understanding is that,
depending on the connection's transaction-isolation level, READ_COMMITTED
transactions will only see those records committed at the START of the
transaction (actually, I think it's before any modifications - such as
UPDATE, INSERT, etc. - are made in the transaction). I'm presuming here
that READ_SERIALIZABLE is way too heavy-handed for your application.
So it's possible that you can have one or more transactions - clients trying
to add a cart, select a cart, whatever - and not see any changes in any
other transaction until the COMMIT of an INSERTed cart. Then the backends
have to resolve WHO actually gets to INSERT the UNIQUE'ly qualified cart:
only one should win and the others should throw "uniqueness violation"
exceptions. Since postgres doesn't do nested-transactions the client has to
rollback and submit the query again; the new transaction should see the
newly committed record and on you chug.
For support of this line of thinking, view the conversation where Tom Lane
described the overall problem much more illustratively than I can.
http://archives.postgresql.org/pgsql-general/2004-04/msg01153.php
Carl <|};-)>
-----Original Message-----
From: Jeff Davis [mailto:jdavis-pgsql@empires.org]
Sent: Friday, May 21, 2004 3:24 PM
To: Carl E. McMillin
Cc: 'PostgreSQL General'
Subject: Re: [GENERAL] Am I locking more than I need to?
On Fri, 2004-05-21 at 14:33, Carl E. McMillin wrote:
> Scenario:
>
> SELECT ... WHERE cart_id=X FOR UPDATE
>
> IF (NOT FOUND) THEN
> BEGIN
> --Here is where nothing is locked.
> --No way to guarantee no one else will create a record before we do.
> INSERT ...
> END;
> END IF;
>
Instead, I was thinking more like:
BEGIN
SELECT ... WHERE cart_id=X FOR UPDATE
IF (NOT FOUND) THEN
--Here is where nothing is locked.
--No way to guarantee no one else will create a record before we do.
INSERT ...
ELSE
UPDATE ...
END IF;
END;
Won't that "SELECT ... FOR UPDATE" block out a concurrent access to the same
cart until the first one finishes? Of course this assumes all concurrent
accesses also try to "SELECT ... FOR UPDATE" before inserting.
Thanks,
Jeff Davis