On Mon, 30 Dec 2002 15:48:38 -0500, "Andy Kriger"
<akriger@greaterthanone.com> wrote:
>>Does SELECT ... FOR UPDATE not do what you want?
>It doesn't lock the row from being read.
It does, if the other transaction also tries a SELECT ... FOR UPDATE.
For transaction isolation level read committed the following works:
Session 1 Session 2
BEGIN;
SELECT quantity
FROM inv
WHERE id=7
FOR UPDATE;
-- quantity = 100
BEGIN;
SELECT quantity
FROM inv
WHERE id=7
FOR UPDATE;
-- is blocked here ...
UPDATE inv
SET quantity=90
WHERE id=7;
COMMIT;
-- continues, sees quantity = 90
UPDATE inv
SET quantity=95
WHERE id=7;
COMMIT;
> I want to make sure the row cannot
>be read until I have done my read and updated if necessary.
Do you really want to block sessions that are not going to update the
locked row? You can guarantee that a read only transaction always
sees a consistent state by setting its transaction isolation level to
serializable.
Servus
Manfred