Обсуждение: SELECT ... FOR UPDATE OF SKIP LOCKED returns can same row when table is filtered on different table than locked Поиск
Список
Период
Сортировка

SELECT ... FOR UPDATE OF

SKIP LOCKED returns can same row when table is filtered on different table than locked
От
Kim Rose Carlsen
Дата:


Hi


Rows in target table is not probably locked when using SELECT ... FOR UPDATE OF <table> SKIP LOCKED when query is filtered on <table2>


How to reproduce:

        CREATE TABLE IF NOT EXISTS queue (
            id     SERIAL PRIMARY KEY,
            status VARCHAR
        );

        INSERT INTO queue (status)
        SELECT 'NEW' FROM generate_series(1,10000);

Run the following query in parrallel

begin transaction
       SELECT * 
         FROM queue as queue1
         JOIN queue as queue2
           ON queue1.id = queue2.id
        WHERE queue2.status = 'NEW'
     ORDER BY queue1.id
        LIMIT 1
FOR UPDATE OF queue1 SKIP LOCKED;

UPDATE queue SET status = 'DONE' WHERE id = :id AND status = 'NEW';     (:id needs to be replace with id of row returned above)

commit;

Expected behaviour
Each id of queue would only be returned once, and each UPDATE will update exactly one row each.

Observed behaviour
Eventually two transactions will get the same row, and only one of them can update the row to 'DONE' (one update will update 0 rows)

If you change the WHERE clause to "WHERE queue1.status = 'NEW'" then everything will lock probably.

If you run each query in different transaction in psql, then you each will correctly get a unique row. So this only happens when executing the queries very fast after each other.

Attached script:
I have written a small php script, which demonstrate the bug. just replace the placeholder for dbname, username and password with appropriate values and run 2 instances with 
shell1> php test.php 

shell2> php test.php 

whenever either of the scripts write
"Error could not update row :id somebody else has done it for me"

then the other script has retrieved and updated that row.

mvh
Kim Carlsen
Hiper A/S
M: 71 99 42 00
Вложения

Re: SELECT ... FOR UPDATE OF

SKIP LOCKED returns can same row when table is filtered on different table than locked
От
Thomas Munro
Дата:
On Thu, Jul 7, 2016 at 12:59 AM, Kim Rose Carlsen <krc@hiper.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