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?
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
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.htmlGreat. It also suggests a workaround.
--
Thanks & Regards,
Kuntal Ghosh
EnterpriseDB: http://www.enterprisedb.com
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:
Show quoted text
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.
--
Thanks & Regards,
Kuntal Ghosh
EnterpriseDB: http://www.enterprisedb.com
<https://urldefense.proofpoint.com/v2/url?u=http-3A__www.enterprisedb.com&d=DwMFaQ&c=lnl9vOaLMzsy2niBC8-h_K-7QJuNJEsFrzdndhuJ3Sw&r=4XHPyPZRSLhdU6MKCd2-Rw&m=xYe6nmboAo9yOHgVlKpvKmLcN1Re8JX2cSDYkaWtysM&s=nCk1b-WTJNHJJMWPzCsAKujWe0vV4wpRH4zpzMGutqc&e=>