Re: Questions of 'for update'

Hello,

On Mon, Jun 10, 2019 at 11:31 AM Zhenghua Lyu <zlv(at)pivotal(dot)io> wrote:

>
> 1. why after emitting `lockrows` plannode, the result can no longer be
> assumed sorted?
>
The plan corresponding to your select query is as following:
QUERY PLAN
-------------------------------
Limit
-> LockRows
-> Sort
Sort Key: c
-> Seq Scan on t

In LockRows node, the executer tries to lock each tuple which are provided
by the Sort node. In the meantime, it's possible that some transaction
updates a tuple (which is to be locked by the current transaction) and gets
committed. These changes will be visible to the current transaction if it
has a transaction isolation level lesser than REPEATABLE_READ. So, the
current transaction needs to check whether the updated tuple still
satisfies the qual check (in your query, there is no quals, so it always
satisfies). If it satisfies, it returns the updated tuple.
Since, the sort has been performed by an earlier node, the output will no
longer be sorted.

> 2. Is the case above a bug or a feature?
>
> IMHO, it looks like an expected behaviour of a correct transaction
management implementation. The argument can be that the snapshot is
consistent throughout all the nodes. Whatever tuple you've fetched from the
bottom level is locked correctly.

--
Thanks & Regards,
Kuntal Ghosh
EnterpriseDB: http://www.enterprisedb.com