Обсуждение: Questions of 'for update'
Hi,
I am reading the code that generating plan for `rowmarks` of Postgres 9.4 (https://github.com/postgres/postgres/blob/REL9_4_STABLE/src/backend/optimizer/plan/planner.c#L2070)
After emitting the `LockRows` plannode, the results cannot be considered in order, and there are comments there:
/** The result can no longer be assumed sorted, since locking might* cause the sort key columns to be replaced with new values.*/
I do not understand the reason and after some guess, I come up with a case:
```
create table t(c int);
insert into t values (1), (2), (3), (4);
-- Transaction 1
begin;
update t set c = 999 where c = 1; -- change the smallest value to a very big one
-- transaction 1 not commit yet
-- Transaction 2, another session
begin;
select * from t order by c limit 1 for update; -- Want to find the smallest value, and then update it
-- this transaction will be blocked by transaction 1
-- then, transaction 1 commit and transaction 2 will return the tuple with value 999
```
I think the reason is that EvalPlanQual does not check the order.
I try this case under mysql, it will output 2 (which is the correct value for the meaning of smallest).
So, in summary, my questions are:
1. why after emitting `lockrows` plannode, the result can no longer be assumed sorted?
2. Is the case above a bug or a feature?
Thanks!
Best Regards,
Zhenghua Lyu
Hello,
On Mon, Jun 10, 2019 at 11:31 AM Zhenghua Lyu <zlv@pivotal.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?
Hi, On Mon, Jun 10, 2019 at 3:50 PM Kuntal Ghosh <kuntalghosh.2007@gmail.com> wrote: > On Mon, Jun 10, 2019 at 11:31 AM Zhenghua Lyu <zlv@pivotal.io> wrote: >> 2. Is the case above a bug or a feature? >> > IMHO, it looks like an expected behaviour of a correct transaction management implementation. This is documented behavior; see the Caution for The Locking Clause on the SELECT reference page: https://www.postgresql.org/docs/11/sql-select.html Best regards, Etsuro Fujita
On Mon, Jun 10, 2019 at 12:42 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
Hi,
On Mon, Jun 10, 2019 at 3:50 PM Kuntal Ghosh <kuntalghosh.2007@gmail.com> wrote:
> On Mon, Jun 10, 2019 at 11:31 AM Zhenghua Lyu <zlv@pivotal.io> wrote:
>> 2. Is the case above a bug or a feature?
>>
> IMHO, it looks like an expected behaviour of a correct transaction management implementation.
This is documented behavior; see the Caution for The Locking Clause on
the SELECT reference page:
https://www.postgresql.org/docs/11/sql-select.html
Great. It also suggests a workaround.
Thanks so much.
I understand now.
Best Regards,
Zhenghua Lyu
On Mon, Jun 10, 2019 at 3:22 PM Kuntal Ghosh <kuntalghosh.2007@gmail.com> wrote:
On Mon, Jun 10, 2019 at 12:42 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:Hi,
On Mon, Jun 10, 2019 at 3:50 PM Kuntal Ghosh <kuntalghosh.2007@gmail.com> wrote:
> On Mon, Jun 10, 2019 at 11:31 AM Zhenghua Lyu <zlv@pivotal.io> wrote:
>> 2. Is the case above a bug or a feature?
>>
> IMHO, it looks like an expected behaviour of a correct transaction management implementation.
This is documented behavior; see the Caution for The Locking Clause on
the SELECT reference page:
https://www.postgresql.org/docs/11/sql-select.htmlGreat. It also suggests a workaround.--