Re: More FOR UPDATE/FOR SHARE problems - Mailing list pgsql-hackers

>>> Jeff Davis <pgsql@j-davis.com> wrote: 
> On Mon, 2009-01-26 at 14:31 -0600, Kevin Grittner wrote:
>> > Do you re-run the query to find new tuples that might now satisfy
>> > the search condition that didn't before?
>>  
>> There can't be any.  Blocks taken during the reading of rows so far
>> have not been released, and would preclude the update from changing
>> results read so far.
> 
> Let's say the sequence is:
> 
> Data:
>  i  j
> --------
>  1  10
>  2  10
>  3  20
>  4  20
> 
> Session1:
> BEGIN;
> UPDATE a SET j = (j - 10) WHERE i = 2 OR i = 3;
OK, the description would be trivial if we assume page level locks
(the default), so I'll assume row level locks.  I'll also assume READ
COMMITTED.  (In SERIALIZABLE, no lock is ever released until COMMIT or
ROLLBACK, although the update locks can be downgraded or upgraded.)
Simplified a bit, this places an exclusive lock on rows 2 and 3 and
then updates these two rows in place (no new tuples are created).
> Session2:
> BEGIN;
> UPDATE a SET j = j + 100 WHERE j = 10;
This might update row 1 before blocking on the attempt to read row 2. 
Let's say, for sake of argument, that it does.  So it is holding an
exclusive lock on row 1 and attempting to acquire an update lock to
read row 2.  (It will upgrade this to an exclusive lock if it decides
to update it, or downgrade it to shared if it decides not to do so.) 
Session2 is blocked for now.
> Session1:
> COMMIT;
After the COMMIT succeeds, the locks from Session1 are released. 
Session2 acquires its update lock and reads row 2, finds that it
doesn't match its update criteria, downgrades the lock to shared,
acquires an update lock on row 3, finds that it does match the
selection criteria, upgrades the lock to exclusive, updates it,
acquires and update lock on row 4 finds that it doesn't match the
update criteria, downgrades the lock to shared, hits the end of table,
releases the shared locks.
> Session2:
> COMMIT;
After the COMMIT succeeds, the locks from Session2 are released.
> In PostgreSQL, the result is:
> 
>  i |  j  
> ---+-----
>  4 |  20
>  2 |   0
>  3 |  10
>  1 | 110
> (4 rows)
> 
> Which cannot be obtained by any serial execution. What is the result
> in Sybase, Oracle, etc.?
I can't be sure about Oracle, but I think its results would match
PostgreSQL.  In Sybase, with either READ COMMITTED or SERIALIZABLE,
the result would be:i |  j  
---+-----1 | 1102 |   03 | 1104 |  20
(4 rows)
If that explanation wasn't clear, let me know.
Let me restate -- I don't propose that PostgreSQL implement this
locking scheme.  I think it can and should do better in approaching
compliance with the standard, and with ACID properties, without
compromising concurrency and performance to the degree required by
this sort of locking and blocking.
-Kevin