Re: 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