Обсуждение: 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>