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?
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?
Thank you,
Eudald
Hello!
Thanks David and Tom for your answer.
I'll check out mvcc. Would user defined locks on a specific table make PostgreSql underperform too much?
Thanks again,
Eudald
Eudald Valcàrcel Lacasa <eudald.valcarcel@gmail.com> writes:
> Could it be possible that, somehow, the select query starts before the
> update one, although it's printed as if it's being executed after it?
It's certainly possible that the select is using a snapshot that was
taken before the update commits. You should read this chapter of
the manual for background and some hints:
https://www.postgresql.org/docs/current/mvcc.html
regards, tom lane