Re: Select for update with offset interferes with concurrent transactions

Hmm...

May I ask how this look in details. If e.g. I do select * from myeshop offset
100 limit 20, I have 1000 rows which rows will be locked?

a) 0 to 120, or
b) all rows will be locked.?

Kind regards,
Radek

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> Tuesday 01 February 2011 18:18:17
> In 9.0, LIMIT/OFFSET processing is done after FOR UPDATE locking, which
> means that rows skipped over by OFFSET still get locked, which means
> that different sessions executing this query are now practically certain
> to block each other, rather than just likely to block each other.
> This was an intentional change to improve the predictability of FOR
> UPDATE's interactions with LIMIT/OFFSET, and indeed it's improved the
> predictability of the behavior for you, just not in the direction you'd
> like :-(
>
> regards, tom lane