Обсуждение: [Fwd: Re: haven't forgotten about you...]
I didn't hear anything back on this. Does someone have a little time or a pointer to a good resource that will clarify the use of the SELECT FOR UPDATE syntax? Tim -------- Original Message -------- Subject: Re: haven't forgotten about you... Date: Mon, 07 Aug 2000 16:08:29 -0700 From: Tim Perdue <tperdue@valinux.com> To: Benjamin Adida <ben@mit.edu> CC: scrappy@hub.org References: <B5934C52.708E%ben@mit.edu> Benjamin Adida wrote: > > on 7/13/00 10:39 AM, Tim Perdue at tperdue@valinux.com wrote: > > > I wouldn't really worry about that right now. > > Oh okay, I thought this was an emergency because you were looking at > switching possibly to another DB. I hope you won't make the Oracle jump! > > > I *would* like to see an article on transactions though. > > Okay, fair enough. I'll get working on that ASAP. Are you going to do this? I've been recently asked to write an article for Linux Journal about "Deploying a Serious Application With PHP". I'd like to use postgres for a "serious" application rather than MySQL, but I would like to see this tutorial to understand the nuances first. (as I mentioned, I don't think I understand the SELECT * FOR UPDATE syntax) Tim -- Founder - PHPBuilder.com / Geocrawler.com Lead Developer - SourceForge VA Linux Systems 408-542-5723
Ben Adida wrote: > begin transaction > select balance from accounts where account_id=2 for update > > will select the balance and lock the row for account #2 > You can then perform some math on the balance, and do something like: > > update accounts set balance= $new_balance where account_id=2 > end transaction > Great - I assume end transaction is going to do a commit. If you don't do an end transaction and you don't issue a rollback... I assume it rolls back? This is pretty slick - over the last month or so I've come up with about 8 different places where I really wish I had transactions/rollbacks on SourceForge. Also running into lots of places where I really, really wish I had fscking subselects... Tim -- Founder - PHPBuilder.com / Geocrawler.com Lead Developer - SourceForge VA Linux Systems 408-542-5723
At 09:13 PM 8/10/00 -0700, Tim Perdue wrote: >Ben Adida wrote: >> begin transaction >> select balance from accounts where account_id=2 for update >> >> will select the balance and lock the row for account #2 >> You can then perform some math on the balance, and do something like: >> >> update accounts set balance= $new_balance where account_id=2 >> end transaction >> > >Great - I assume end transaction is going to do a commit. If you don't >do an end transaction and you don't issue a rollback... I assume it >rolls back? It is best not to assume, and to do so explicitly. I base this on the theory that you ought to know what your code does, and what it did to get there. (end transaction is indeed "commit", you can use "commit" if you prefer). >This is pretty slick - over the last month or so I've come up with about >8 different places where I really wish I had transactions/rollbacks on >SourceForge. Yes. That's the realization one comes to when working on complex database apps. >Also running into lots of places where I really, really >wish I had fscking subselects... As someone who uses Oracle, I feel the same way, but Postgres doesn't make me feel that way nearly as often as MySQL would :) (and actually, Oracle's outer join syntax requires subselects if you are to mix and match inner and outer joins and control the priority of execution order - which the vastly superior SQL92 syntax solves in a reasonably elegant manner). - Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert Serviceand other goodies at http://donb.photo.net.
Tim Perdue wrote: > I didn't hear anything back on this. Does someone have a little time or > a pointer to a good resource that will clarify the use of the SELECT FOR > UPDATE syntax? Uggh, just when I finally had some time to answer :) Let me attempt to answer it anyways. SELECT for UPDATE is a means of explicitly locking a row for later updating within the same transaction. For example (this is a simplified example): begin transaction select balance from accounts where account_id=2 for update will select the balance and lock the row for account #2 You can then perform some math on the balance, and do something like: update accounts set balance= $new_balance where account_id=2 end transaction Thus, this construct makes this safe in a multi-client environment. Even if two clients perform these actions simultaneously, the "for update" will guarantee that one of the two locks that row at the select statement level, and the second waits until the first transaction commits (at which point the lock is transparently released). Note that if you *didn't* have the "for update", no lock would be acquired at the select level, and you could run into a race condition where two processes grab the same balance from the account, and independently update that amount, thereby losing the effect of one of those updates (and probably robbing you of money). Note also that the lock acquired is row-level, which means that if two processes are updating two different accounts, both processes can proceed without blocking each other. This will thus behave not only correctly, but as efficiently as possible. I hope this clears things up. I am writing that article about transactions and locking, it's on its way, I swear. -Ben
Tim Perdue wrote: > Great - I assume end transaction is going to do a commit. If you don't > do an end transaction and you don't issue a rollback... I assume it > rolls back? Yes, when I said end transaction, I meant commit. The precise behavior you're inquiring about is dependent on your web server / driver setup. In AOLserver's Postgres driver, if a database handle is released when a transaction is still open, the transaction is rolled back. I can imagine other drivers behaving differently, but implicit commits sound very dangerous to me. > This is pretty slick - over the last month or so I've come up with about > 8 different places where I really wish I had transactions/rollbacks on > SourceForge. Also running into lots of places where I really, really > wish I had fscking subselects... Yes, Postgres is definitely pretty slick... -Ben