[ACCEPTED]-Select unlocked row in Postgresql-locking

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

No No NOOO :-)

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

So 37 every process needs to download a set of 36 rows. This set of rows cannot be downloaded 35 by the other process because it will make 34 a lot of mess (some people would receive 33 couple messages when they should receive 32 only one). That is why we need to lock the 31 rows. The query to download a set of messages 30 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 29 is started every 0.5 sec. So this will result 28 in the next query waiting for the first 27 lock to unlock the rows. This approach creates 26 enormous delays. Even when we use NOWAIT 25 the query will result in a Exception which 24 we don't want because there might be new 23 messages in the table that have to be sent. If 22 use simply FOR SHARE the query will execute 21 properly but still it will take a lot of 20 time creating huge delays.

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

1. changing the 18 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 'is_locked(msg_id)' looks 17 like this:

   CREATE OR REPLACE FUNCTION is_locked(integer) RETURNS BOOLEAN AS $$
   DECLARE
       id integer;
       checkout_id integer;
       is_it boolean;
   BEGIN
       checkout_id := $1;
       is_it := FALSE;
   
       BEGIN
           -- we use FOR UPDATE to attempt a lock and NOWAIT to get the error immediately 
           id := msg_id FROM public.messages WHERE msg_id = checkout_id FOR UPDATE NOWAIT;
           EXCEPTION
               WHEN lock_not_available THEN
                   is_it := TRUE;
       END;
   
       RETURN is_it;
   
   END;
   $$ LANGUAGE 'plpgsql' VOLATILE COST 100;
   

Of course we can customize this 16 function to work on any table you have in 15 your database. In my opinion it is better 14 to create one check function for one table. Adding 13 more things to this function can make it 12 only slower. I takes longer to check this 11 clause anyways so there is no need to make 10 it even slower. For me this the complete 9 solution and it works perfectly.

Now when 8 i have my 50 processes running in parallel 7 every process has a unique set of fresh 6 messages to send. Once the are sent i just 5 update the row with sent = TRUE and never 4 go back to it again.

I hope this solution 3 will also work for you (author). If you 2 have any question just let me know :-)

Oh, and 1 let me know if this worked for you as-well.

I use something like this:

select  *
into l_sms
from sms
where prefix_id = l_prefix_id
    and invoice_id is null
    and pg_try_advisory_lock(sms_id)
order by suffix
limit 1;

and don't forget 1 to call pg_advisory_unlock

If you are trying to implement a queue, take 2 a look at PGQ, which has solved this and 1 other problems already. http://wiki.postgresql.org/wiki/PGQ_Tutorial

It appears that you are trying to do something 18 like grab the highest priority item in a 17 queue that is not already being taken care 16 of by another process.

A likely solution 15 is to add a where clause limiting it to 14 unhandled requests:

select * from queue where flag=0 order by id desc for update;
update queue set flag=1 where id=:id;
--if you really want the lock:
select * from queue where id=:id for update;
...

Hopefully, the second 13 transaction will block while the update 12 to the flag happens, then it will be able 11 to continue, but the flag will limit it 10 to the next in line.

It is also likely that 9 using the serializable isolation level, you 8 can get the result you want without all 7 of this insanity.

Depending on the nature 6 of your application, there may be better 5 ways of implementing this than in the database, such 4 as a FIFO or LIFO pipe. Additionally, it 3 may be possible to reverse the order that 2 you need them in, and use a sequence to 1 ensure that they are processed sequentially.

This can be accomplished by SELECT ... NOWAIT; an 1 example is here.

My solution is to use the UPDATE statement 6 with the RETURNING clause.

Users

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

Instead of SELECT .. FOR UPDATE use

BEGIN; 

UPDATE "Users"
SET ...
WHERE ...;
RETURNING ( column list );

COMMIT;

Because 5 the UPDATE statement obtains a ROW EXCLUSIVE 4 lock on the table its updating you get serialized 3 updates. Reads are still allowed, but they 2 only see data before the start of the UPDATE 1 transaction.

Reference: Concurrency Control Chapter of Pg docs.

Looks like you're looking for a SELECT FOR 28 SHARE.

http://www.postgresql.org/docs/8.3/interactive/sql-select.html#SQL-FOR-UPDATE-SHARE

FOR SHARE behaves similarly, except 27 that it acquires a shared rather than exclusive 26 lock on each retrieved row. A shared lock 25 blocks other transactions from performing 24 UPDATE, DELETE, or SELECT FOR UPDATE on 23 these rows, but it does not prevent them 22 from performing SELECT FOR SHARE.

If specific 21 tables are named in FOR UPDATE or FOR SHARE, then 20 only rows coming from those tables are locked; any 19 other tables used in the SELECT are simply 18 read as usual. A FOR UPDATE or FOR SHARE 17 clause without a table list affects all 16 tables used in the command. If FOR UPDATE 15 or FOR SHARE is applied to a view or sub-query, it 14 affects all tables used in the view or sub-query.

Multiple 13 FOR UPDATE and FOR SHARE clauses can be 12 written if it is necessary to specify different 11 locking behavior for different tables. If 10 the same table is mentioned (or implicitly 9 affected) by both FOR UPDATE and FOR SHARE 8 clauses, then it is processed as FOR UPDATE. Similarly, a 7 table is processed as NOWAIT if that is 6 specified in any of the clauses affecting 5 it.

FOR UPDATE and FOR SHARE cannot be used 4 in contexts where returned rows cannot be 3 clearly identified with individual table 2 rows; for example they cannot be used with 1 aggregation.

What are you trying to accomplish? Can 9 you better explain why neither unlocked 8 row updates nor full transactions will do 7 what you want?

Better yet, can you prevent 6 contention and simply have each thread use 5 a different offset? This won't work well 4 if the relevant portion of the table is 3 being updated frequently; you'll still have 2 collisions but only during heavy insert 1 load.

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

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

How about the following? It might be treated 6 more atomically than the other examples 5 but should still be tested to make sure my assumptions 4 aren't wrong.

UPDATE users SET flags = 1 WHERE id = ( SELECT id FROM users WHERE flags = 0 ORDER BY id DESC LIMIT 1 ) RETURNING ...;

You'll probably still be stuck 3 with whatever locking scheme postgres uses 2 internally to supply consistent SELECT results 1 in the face of a simultaneous UPDATEs.

I faced the same problem in our application 5 and came up with a solution that is very 4 similar to Grant Johnson's approach. A FIFO 3 or LIFO pipe was not an option because we 2 have a cluster of application servers accessing 1 one DB. What we do is a

SELECT ... WHERE FLAG=0 ... FOR UPDATE

UPDATE ... SET FLAG=1 WHERE ID=:id

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

Let's say your table is like 11 that:

id | name | surname | status

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

Every 10 new record gets inserted with status pending(1)

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

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

Otherside 1 it updates to the other statuses.

Used in multi-thread and cluster?
How about 1 this?

START TRANSACTION;

// All thread retrive same task list
// If result count is very big, using cursor 
//    or callback interface provied by ORM frameworks.
var ids = SELECT id FROM tableName WHERE k1=v1;

// Each thread get an unlocked recored to process.
for ( id in ids ) {
   var rec = SELECT ... FROM tableName WHERE id =#id# FOR UPDATE NOWAIT;
   if ( rec != null ) {
    ... // do something
   }
}

COMMIT;