Alex Rivera | Logout

Select unlocked row in Postgresql

Asked 2008-12-23T17:34:33.793
54

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

postgresql locking
Edit
Report

3 Answers

10

No No NOOO :-)

I know what the author means. I have a similar situation and i came up with a nice solution. First i will start from describing my situation. I have a table i which i store messages that have to be sent at a specific time. PG doesn't support timing execution of functions so we have to use daemons (or cron). I use a custom written script that opens several parallel processes. Every process selects a set of messages that have to be sent with the precision of +1 sec / -1 sec. The table itself is dynamically updated with new messages.

So every process needs to download a set of rows. This set of rows cannot be downloaded by the other process because it will make a lot of mess (some people would receive couple messages when they should receive only one). That is why we need to lock the rows. The query to download a set of messages with the lock:

FOR messages in select * from public.messages where sendTime >= CURRENT_TIMESTAMP - '1 SECOND'::INTERVAL AND sendTime <= CURRENT_TIMESTAMP + '1 SECOND'::INTERVAL AND sent is FALSE FOR UPDATE LOOP
-- DO SMTH
END LOOP;

a process with this query is started every 0.5 sec. So this will result in the next query waiting for the first lock to unlock the rows. This approach creates enormous delays. Even when we use NOWAIT the query will result in a Exception which we don't want because there might be new messages in the table that have to be sent. If use simply FOR SHARE the query will execute properly but still it will take a lot of time creating huge delays.

In order to make it work we do a little magic:

  1. changing the query:

    FOR messages in select * from public.messages where sendTime >= CURRENT_TIMESTAMP - '1 SECOND'::INTERVAL AND sendTime <= CURRENT_TIMESTAMP + '1 SECOND'::INTERVAL AND sent is FALSE AND is_locked(msg_id) IS FALSE FOR SHARE LOOP
-- DO SMTH
END LOOP;

  2. the mysterious function 'i

answered 2010-07-14T02:52:32.287
0

Since I haven't found a better answer yet, I've decided to use locking within my app to synchronize access to the code that does this query.

answered 2008-12-23T20:02:25.213
0

^^ that works. consider having an "immediate" status of "locked".

Let's say your table is like that:

id | name | surname | status

And possible statuses for example are: 1=pending, 2=locked, 3=processed, 4=fail, 5=rejected

Every new record gets inserted with status pending(1)

Your program does: "update mytable set status = 2 where id = (select id from mytable where name like '%John%' and status = 1 limit 1) returning id, name, surname"

Then your program does its thing and if it cames up with the conclusion that this thread shouldn't had processed that row at all, it does: "update mytable set status = 1 where id = ?"

Otherside it updates to the other statuses.

answered 2009-12-07T12:40:33.897

Your Answer