As a capability, compare-and-swap has an infinite consensus number [1], meaning it's sufficient to implement wait-free consensus algorithms with an arbitrary number of participants. That makes it a perfect fit for managing a scalable pool of workers that need to coordinate on consuming from a queue!
[0] https://en.wikipedia.org/wiki/Compare-and-swap
[1] https://en.wikipedia.org/wiki/Consensus_(computer_science)#C...
Connection 1
LISTEN 'job-updates';
BEGIN;
INSERT INTO jobs ('a-uuid', …);
SELECT PG_NOTIFY('job-update', 'json blob containing uuid and state change info');
COMMIT;
BEGIN;
SELECT id, … FROM jobs WHERE id = 'a-uuid' FOR UPDATE SKIP LOCKED;
UPDATE 'jobs' SET state = 'step1_completed' WHERE is = 'a-uuid';
SELECT PG_NOTIFY('job-update', 'json blob containing uuid and state change info');
-- do the thing here: computation, calling external API, etc. If it fails then rollback.
COMMIT;
The only downsides I immediately think of are you will have every worker contending to lock that row, and you’ll need to write periodic jobs to cleanup/retry failures.
Saying this as someone who has been on both sides of that interview question, and also evaluated performance in it as a hiring manager.
Thing is, because purchasing is often shared across third-parties, even then you can’t guarantee that you’ll have the reservation until it’s paid and entered into the shared booking backend… unless the third party backend has a locking mechanism, at which point you’re probably not using your own Postgres to do this, but their mainframe instead.
They allow overbooking the flight but you can't book the same seat twice.
Overbooking can make financial sense -- even if you need to hand a little compensation from time to time it's still better than flying with empty seats. Of course it sucks big time for the travelers especially if there are no volunteers. IDB is miserable. https://www.transportation.gov/individuals/aviation-consumer...
ok, but how do you overbook a flight without booking at least one of the seats twice?
Sometimes it doesn't work out and that's when airlines bleed. I was flying home one day from some US city (was it Denver? the years and cities blend together) when a Drupal conference and some major sports thing ended the same day and the airport was a total gong show. United first offered a paltry 120 USD but when they raised to 400 USD to go home the next day instead my hand went up -- and I was such a greenhorn I didn't know they will pay for the hotel, too. A buddy pocketed no less than 1200 USD from Delta.