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.