Thread: 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
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
Thank you! That worked perfectly. I didn't know that I needed to put in the BEGIN/END transaction stuff, but of course that makes sense. I tried it by having two process, and sure enough, process #2 blocked until the END was issued on process #1. And I can rely on this being absolutely true, right? No amount of load or clever timing will make this condition not be true? Thanks for the help. I can say that Postgres has _way_ better support than any kind of Microsoft product. 7.1 should be awesome. Free software these days just rocks.
On Fri, Mar 09, 2001 at 09:12:55PM -0000, drevil@sidereal.kz wrote:
> 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.
In theory, you could also make a CHECK condition for that column that
would throw an exception if the balance goes negative. Depending on
how your code is written, that might be easier or tougher.
Chris
--
chris@mt.sri.com -----------------------------------------------------
Chris Jones SRI International, Inc.
www.sri.com