Re: lock table question

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