Postgresql – the best solution to prevent picking the same value (by multiple requests)

postgresqlselecttransactionupdate

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?

Best Answer

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

Retry:
    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.

Related Solutions

Mysql – Multiple update of records in the same table

Philᵀᴹ: Insert all of the data pairs into a temporary staging table, then run a single update. Will be lots quicker.

Kondybas: Create table pairs(id,name) from collected data and then:

UPDATE table 
JOIN pairs ON table.id=pairs.id
SET table.name=pairs.name

_{Wiki answer generated from comments on the question.}

PostgreSQL Replication – Best Solutions for Offline DB Syncing

Bucardo is the standard tool for this sort of job. It's well tested to run when the two servers are disconnected from each other for a while. You could use any master/master replication approach instead: Londiste, Slony, or the early release of BDR from 2ndQuadrant.

With the network setup you've described, you will be hard pressed to avoid race conditions here in all conditions no matter what you do. The only way to keep someone from spending money in both locations here is to setup a two-phase commit transactions or do some form of synchronous replication, basically only putting the record on one machine if it's coordinated on both. One nice thing about Bucardo is that it's designed expecting this will happen. You can setup a conflict resolution mechanism, to recognize and potentially resolve conflicts on nodes when things are changed on both. The other multi-master solutions are not aiming as directly at the disconnected network / conflicting change problems.

Best Answer

Related Solutions

Mysql – Multiple update of records in the same table

PostgreSQL Replication – Best Solutions for Offline DB Syncing

Related Question