Re: Queries getting older values (autocommit enabled) - Mailing list pgsql-bugs

From Eudald Valcàrcel Lacasa
Subject Re: Queries getting older values (autocommit enabled)
Date
Msg-id CANEx+AWFL=3oqNNZJf5WnkpYTr6wTOkxFAP7aufPKe7YciqrkA@mail.gmail.com
Whole thread Raw
In response to Re: Queries getting older values (autocommit enabled)  ("David G. Johnston" <david.g.johnston@gmail.com>)
Responses Re: Queries getting older values (autocommit enabled)
List pgsql-bugs
Hello David,
Thanks for your answer.
The actual problem is that my SELECT query is being executed meanwhile the UPDATE one, but instead of waiting for the UPDATE to be executed, it doesn't wait and gets the value from the field prior to the update.
I believed it was because of the MVCC, and I was looking for a workaround to force the select to wait for the UPDATE to finish, instead of taking values of a snapshot.

Eudald

El sáb., 25 abr. 2020 a las 21:36, David G. Johnston (<david.g.johnston@gmail.com>) escribió:
On Sat, Apr 25, 2020 at 12:07 PM Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com> wrote:
Hello again,
I've been looking for this issue and I'd like to know the behavior of FOR UPDATE SKIP LOCKED in the following scenario:
* One query does an UPDATE targeting a row in the table
* Another query run in parallel does a SELECT...FOR UPDATE SKIP LOCKED targeting the same (being updated) row on the table.
From SKIP LOCKED definition: . With SKIP LOCKED, any selected rows that cannot be immediately locked are skipped.

Would it mean that the 2nd query wouldn't check the affected row since it's locked by the first query?

Yes.  What else would it mean?

If that's the behavior, is there any way I could make the SELECT query wait for the UPDATE LOCK? Is it recommended? Are there downsides to this approach?


"To prevent the operation from waiting for other transactions to commit, use either the NOWAIT or SKIP LOCKED option."

Which means that if you don't include the NOWAIT clause you, well, wait.

David J.

pgsql-bugs by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: Queries getting older values (autocommit enabled)
Next
From: "David G. Johnston"
Date:
Subject: Re: Queries getting older values (autocommit enabled)