>>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> A re-sort after locking doesn't really make things all nice and
> intuitive either. Suppose that the values of X are 10,20,30,40,50
> and we do SELECT ... ORDER BY x LIMIT 3 FOR UPDATE. Concurrently
> someone updates the 20 to 100. The existing code locks the 10,20,30
> rows, then notices the 20 got updated to 100, and returns you
> 10,100,30. If it re-sorted you would get 10,30,100, but on what
> grounds is that the correct answer and not 10,20,40? If you want
> to argue that 10,20,40 is the correct answer, how are you going to
> arrive at it without locking more rows than are returned?
Would it make any sense to roll back and generate a
SERIALIZATION_FAILURE? In other databases I've seen this failure
code used for all situations where they can't perform the requested
operation due to concurrent transactions, regardless of transaction
isolation level.
That seems more intuitive to me than any of the alternatives discussed
so far, which all seem to involve quietly returning something other
than what was specified.
-Kevin