Re: Is "WITH () UPDATE" Thread Safe ?

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.

So you should be fine.

Yours,
Laurenz Albe