Implicit row locking during an UPDATE

From: "Dr(dot) Evil" <drevil(at)sidereal(dot)kz>
To: pgsql-general(at)postgreSQL(dot)org
Subject: Implicit row locking during an UPDATE
Date: 2001-05-25 02:23:50
Message-ID: 20010525022350.9184.qmail@sidereal.kz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Eric G. Miller 2001-05-25 04:25:43 Re: Implicit row locking during an UPDATE
Previous Message Steve O'Hagan 2001-05-25 01:07:48 Re: OID wrap around