locking - Select unlocked row in Postgresql

Question

Is there a way to select rows in Postgresql that aren't locked? I have a multi-threaded app that will do:

Select... order by id desc limit 1 for update

on a table.

If multiple threads run this query, they both try to pull back the same row.

One gets the row lock, the other blocks and then fails after the first one updates the row. What I'd really like is for the second thread to get the first row that matches the WHERE clause and isn't already locked.

To clarify, I want each thread to immediately update the first available row after doing the select.

So if there are rows with ID: 1,2,3,4 , the first thread would come in, select the row with ID=4 and immediately update it.

If during that transaction a second thread comes it, I'd like it to get row with ID=3 and immediately update that row.

For Share won't accomplish this nor with nowait as the WHERE clause will match the locked row (ID=4 in my example). Basically what I'd like is something like "AND NOT LOCKED" in the WHERE clause.

Users

-----------------------------------------
ID        | Name       |      flags
-----------------------------------------
1         |  bob       |        0
2         |  fred      |        1
3         |  tom       |        0
4         |  ed        |        0

If the query is "Select ID from users where flags = 0 order by ID desc limit 1" and when a row is returned the next thing is "Update Users set flags = 1 where ID = 0" then I'd like the first thread in to grab the row with ID 4 and the next one in to grab the row with ID 3.

If I append "For Update" to the select then the first thread gets the row, the second one blocks and then returns nothing because once the first transaction commits the WHERE clause is no longer satisfied.

If I don't use "For Update" then I need to add a WHERE clause on the subsequent update (WHERE flags = 0) so only one thread can update the row.

The second thread will select the same row as the first but the second thread's update will fail.

Either way the second thread fails to get a row and update because I can't get the database to give row 4 to the first thread and row 3 to the second thread the the transactions overlap.

Answer 1

This feature, SELECT ... SKIP LOCKED is being implemented in Postgres 9.5. http://www.depesz.com/2014/10/10/waiting-for-9-5-implement-skip-locked-for-row-level-locks/