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.
