From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | "'drevil(at)sidereal(dot)kz'" <drevil(at)sidereal(dot)kz>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | RE: How to use locks, for DB noivces? |
Date: | 2001-03-09 21:27:26 |
Message-ID: | 01C0A8B5.D4CA7980.mascarm@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)mascari(dot)com
-----Original Message-----
From: drevil(at)sidereal(dot)kz [SMTP:drevil(at)sidereal(dot)kz]
Sent: Friday, March 09, 2001 4:13 PM
To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly
From | Date | Subject | |
---|---|---|---|
Next Message | Creager, Robert S | 2001-03-09 21:34:40 | COPY problem |
Previous Message | drevil | 2001-03-09 21:12:55 | How to use locks, for DB noivces? |