Thread: Implicit row locking during an UPDATE
I'm still not understanding this, and it's vitally important to the project I'm working on, so I have a question: From my understanding, this: UPDATE account SET value = 10 WHERE number = 99; actually implies all of this: BEGIN; SELECT value FOR UPDATE FROM account WHERE number = 99; UPDATE account SET value = 10 WHERE number = 99; COMMIT; END; Is this correct? If so, there's something I don't understand. In order to test locking stuff, I created a little C function: int pgsleep(int i) { sleep(i); return(i); } which I then linked into PG using CREATE FUNCTION.... It worked just as I expected. So to test locking, I opened up two windows, and ran psql in each. In one window, I run: UPDATE account SET value = pgsleep(20) WHERE number = 99; Then, a few seconds later, in the other window, I run: UPDATE account SET value = 30 WHERE number = 99; What I would expect to happen is that Window 1 would lock the row where number = 99, and then set the value to 20, and then the command in Window 2 would run, setting the value to 30. Instead, the UPDATE in Window 2 runs immediately, setting the value to 30, and then, after the twenty seconds have gone by, the UPDATE in Window 1 finishes, and sets the value to 20. So, what's actually going on here? Thanks for any tips.
"Dr. Evil" <drevil@sidereal.kz> writes: > From my understanding, this: > UPDATE account SET value = 10 WHERE number = 99; > actually implies all of this: > BEGIN; > SELECT value FOR UPDATE FROM account WHERE number = 99; > UPDATE account SET value = 10 WHERE number = 99; > COMMIT; > END; Not really. Plain UPDATE doesn't lock the target tuple in advance of computing the new version; it optimistically assumes that it doesn't need to do that. If that turns out to be wrong when it actually goes to update the tuple, it backs off, recomputes the update, and tries again. Normally this ends up at the same place as your two-step example, but not when the UPDATE targetlist has side effects... regards, tom lane
On Fri, May 25, 2001 at 02:23:50AM -0000, Dr. Evil wrote: > > I'm still not understanding this, and it's vitally important to the > project I'm working on, so I have a question: > > >From my understanding, this: > > UPDATE account SET value = 10 WHERE number = 99; > > actually implies all of this: > > BEGIN; > SELECT value FOR UPDATE FROM account WHERE number = 99; > UPDATE account SET value = 10 WHERE number = 99; > COMMIT; > END; > > Is this correct? If so, there's something I don't understand. No. Maybe you want to read the manual. Specifically look at transaction isolation level under MVCC. Hmm, SET TRANSACTION ISOLATION LEVEL doesn't seem to be taking in 7.1.1... Is that a bug? Guess the only way to be sure is to use the transaction semantics above... -- Eric G. Miller <egm2@jps.net>