Re: SELECT ... FOR UPDATE OF <table> SKIP LOCKED returns can same row when table is filtered on different table than locked

On Thu, Jul 7, 2016 at 12:59 AM, Kim Rose Carlsen <krc(at)hiper(dot)dk> wrote:
> Rows in target table is not probably locked when using SELECT ... FOR UPDATE
> OF <table> SKIP LOCKED when query is filtered on <table2>

If you modify your program to print out queue1.ctid, queue1.status and
queue2.ctid, queue2.status from the SELECT when your UPDATE finds no
rows, you'll see that you have different tuples on the two sides of
that self-join. Those are versions of the same logical row from
different times. That may seem pretty surprising, but it's because
FOR UPDATE follows the update chain, allowing you to see committed
tuples that aren't visible to the active snapshot, as longs as they
still satisfy the WHERE clause. You're using queue2 to find rows with
status = 'NEW', but it sees rows as they were at the moment the query
started (assuming READ COMMITTED), and a concurrent session might do
the same thing and then manage to commit before your session scans the
row, so you finish up locking a row that already has status = 'DONE'
(as you'd see if you printed out queue1.status). You'd need to make
sure that the row locking applies to the same relation(s) as the WHERE
clause to avoid that.

This is related to the problem highlighted in the caution box under
'The Locking Clause' in the manual [1]. That section is talking about
how the ordering can be wrong when you combine FOR UPDATE and ORDER
BY, because the sorting happens before the row locks are obtained by
the LockRows executor node, but the LockRows node fast-forwards to the
latest committed version of the row, which might then be out of order.
The WHERE clause equivalent of that problem is handled automatically
by rechecking tuples agains the WHERE clauses and throwing away tuples
that no longer satisfy it... as long as FOR UPDATE is covering the
right relation(s).

You don't need SKIP LOCKED to see the effect by the way: it's possibly
easier to produce without it.

[1] https://www.postgresql.org/docs/9.5/static/sql-select.html#SQL-FOR-UPDATE-SHARE

--
Thomas Munro
http://www.enterprisedb.com