Re: More FOR UPDATE/FOR SHARE problems

On Mon, 2009-01-26 at 09:26 -0800, Jeff Davis wrote:
> On Mon, 2009-01-26 at 10:48 -0600, Kevin Grittner wrote:
> > I guess the issue of whether this violation of ACID properties should
> > be considered a bug or a feature is a separate discussion, but calling
> > it a feature seems like a hard sell to me.
> >
>
> I think I understand the other perspective on this now: SELECT FOR
> UPDATE/SHARE is an entirely separate command that is more similar (in
> transactional semantics) to UPDATE than to SELECT.

You can think of SELECT FOR UPDATE as first half of UPDATE command

UPDATE is in this case split in two
SELECT FOR UPDATE
UPDATE WHERE CURRENT

which means that yes, it has to follow UPDATE semantics to be of any use
in FOR UPDATE case.

> In fact, it's probably most similar to UPDATE ... RETURNING, which will
> give the same result (that breaks atomicity or isolation, depending on
> your point of view), which is correct for READ COMMITTED isolation
> level.
>
> Because the command begins with SELECT, I would expect it to follow the
> rules of SELECT with the side effect of locking. I would think that the
> standard would have something to say about this*.
>
> I certainly don't think it's intuitive behavior.
>
> Regards,
> Jeff Davis.
>
> *: It appears that SELECT ... FOR UPDATE is not in the standard, which
> would indicate to me that the SELECT statement should still behave
> according to SELECT isolation/snapshot rules. But when I guess about the
> standard, I'm usually wrong.
>

--
------------------------------------------
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training