Paul GOERGLER wrote: > I have a lot of tickets, i need to take a batch of tickets and process them. > So the process is : > SELECT ONLY 100 tickets > PROCESS ticket > MARK THEM AS « done » > > I’m selecting the tickets with : > > WITH t0 AS ( > SELECT t.id, > RANDOM() AS rank, > EXTRACT(EPOCH FROM NOW() - b.created_at) as elapsed > FROM tickets AS t > LEFT JOIN batch as b ON b.id = t.batch_id > WHERE ( > t.status = 'waiting' OR > (t.status = 'processing' AND t.locked_until IS NOT NULL AND t.locked_until <= NOW()) > ) AND t.send_at < NOW() > AND (t.send_before IS NULL OR t.send_before > NOW()) > ORDER BY > t.priority DESC, > rank ASC > LIMIT 100 > FOR UPDATE OF t > ) > UPDATE tickets AS t1 > SET status = 'processing', > locked_until = NOW() + '1 HOUR’, > extra = t1.extra || hstore('elapsed', t0.elapsed || '') || hstore('rank', rank || '') > FROM t0 > WHERE t1.id = t0.id > RETURNING t1.*; > > > I wonder if this query is thread safe, Can a ticket be updated between the SELECT part (t0) and the > UPDATE part ? > If this query is not « thread safe » how can i do this ?
There is no race condition in your query because you used SELECT ... FOR UPDATE.
That causes the rows found in the WITH clause to be locked against concurrent modification.