RE: How to use locks, for DB noivces?

SELECT FOR UPDATE should work. Did you use it in a transaction? For example:

Session 1:

CREATE TABLE accounts (amount float8);
INSERT INTO accounts VALUES (10);
BEGIN;
SELECT * FROM accounts FOR UPDATE;

Session 2:

BEGIN;
SELECT * FROM accounts FOR UPDATE; <---- This should block

Session 1:

UPDATE accounts SET amount = 1;
END;

Session 2:
<--- This should now return '1'
END;

Hope that helps,

Mike Mascari
mascarm@mascari.com

-----Original Message-----
From:    drevil@sidereal.kz [SMTP:drevil@sidereal.kz]
Sent:    Friday, March 09, 2001 4:13 PM
To:    pgsql-general@postgresql.org
Subject:    [GENERAL] How to use locks, for DB noivces?


I've never used the locking features of Postgres before, and now I
find that I need to use them.  I read through the instructions, but
I've never taken a database theory course so I don't understand all
the terms they used.

Here's what I need to do: The database keeps accounts.  If one process
is accessing an account, no other process should be able to access it
at the same time.  I need to do the following logical sequence of
things:

1. Lock the account

2. Check to see if the change to the account is permitted

3. Make the change

4. Unlock the account

I need this so that there isn't a race condition.  Ie, if there are
$10 in the account, and one backen says "withdraw $9" and the other
also says "withdraw $9" at the same time, I need to make sure they
they don't execute at the same time, which would result in a negative
balance.

I tried to do this with SELECT FOR UPDATE but I couldn't get that to
work.

Thanks

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly