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?