Situation: there is a table "Tours" which contains a lot of generated records with different properties (Color, Priority, PickedUp). The goal is to allow external application (over REST API) to pick up next tour with highest priority for the given color.

This is easy, but the challenge is to completely prevent picking up the same tour multiple times, which can surely happen in live system.

Current approach is:

  • SELECT tour_id based on (color, priority, pickedUp = false)
  • UPDATE tour set PickedUp=true
  • Return tour_id to the calling application

Obviously, since we have SELECT and UPDATE as separate requests, another request can come at this time and pick up the same tour.

I know it's possible to have a single UPDATE + SELECT query, but: I still need to return the picked up tour_id to the caller, how would I do this? Is it possible with SQL to execute SELECT after UPDATE + SELECT in one query?

  • 3
    update tour set picked_up = true where ... returning tour_id? – a_horse_with_no_name Sep 10 '18 at 12:25
  • 1
    Have a look at this and this and this – a_horse_with_no_name Sep 10 '18 at 12:29
  • I've got this example SQL working from DB admin: " update tours set status = 'to reserve', reservation_id = 'second_tour' where tour_id = (select tour_id from tours t where t.reservation_id = 'first_tour' OR t.tour_type like '%KW%' and t.status is null order by t.priority desc limit 1 for update) returning *" but: calling this from Java/JPA (HQL) does not work because this HQL does not support the limit 1 (nor top 1) syntax!!!!) – Arnie Schwarzvogel Sep 10 '18 at 20:25

There are several approaches, but a simple one in pseudo code is:

    SELECT tour_id based on (color, priority, pickedUp = false)
    if none selected, then no vacancy error
    UPDATE tour set PickedUp=true where tour_id=:tour_id and PickedUp=false
    If none updated, then retry
Do something with :tour_id

But pickedUp should probably be more than a boolean, recording who picked up the tour. Otherwise if the "Do something" fails then the tour will appear booked in the database, but no one will know why.

  • Shouldn't the select at least do an for update? But I think it is not needed to begin with. The update will be enough – a_horse_with_no_name Sep 10 '18 at 14:50
  • @a_horse_with_no_name There is sadly no LIMIT clause supported on UPDATE. I assume he has a LIMIT 1 on his (unshown) select statement and wants to update at most one row. The FOR UPDATE is not necessary if you have a retry loop, and it can interact with with ORDER BY and LIMIT in unexpected ways, so I would avoid them unless you have serious contention problem. – jjanes Sep 10 '18 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.