Thread

1. Implicit row locking during an UPDATE

   Dr. Evil <drevil@sidereal.kz> — 2001-05-25T02:23:50Z

   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.
   
   

2. Re: Implicit row locking during an UPDATE

   Eric G. Miller <egm2@jps.net> — 2001-05-25T04:25:43Z

   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>
   
   

3. Re: Implicit row locking during an UPDATE

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-25T04:45:32Z

   "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