Re: lock table question

On Mon, 30 Dec 2002 15:48:38 -0500, "Andy Kriger"
<akriger(at)greaterthanone(dot)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