Strategy for concurrent group bookings?

Consider a seat booking database. There is a list of n seats, and each one has an attribute is_booked. 0 means it isn't, 1 means it is. Any higher number and there is an overbooking.

What is the strategy for having multiple transactions (where each transaction will book a group of y seats concurrently) without allowing over bookings?

I would simply select all unbooked seats, select a randomly selected group of y of them, book them all, and check if that booking is correct (aka the number of is_booked is not over one, which would signify another transaction having booked the seat and committed), then commit. otherwise abort and try again.

This is run at isolation level Read Committed in Postgres.

Because you're not telling us much of what you need, I'll guess for everything, and we'll make it moderately complex to simplify some of the possible questions.

First thing about MVCC is that in a highly concurrent system you want to avoid table locking. As a general rule, you can't tell what does not exist without locking the table for the transaction. That leaves you one option: don't rely on INSERT.

I leave very little as an exercise for a real booking app here. We don't handle,

• Overbooking (as a feature)

• Or what to do if there are not x-remaining seats.

• Buildout to customer and transaction.

The key here is in the UPDATE. We lock only the rows for UPDATE before the transaction starts. We can do this because we've inserted all seat-tickets for sale in the table, event_venue_seats.

Create a basic schema

CREATE SCHEMA booking;
CREATE TABLE booking.venue (
  venueid    serial PRIMARY KEY,
  venue_name text   NOT NULL
  -- stuff
);
CREATE TABLE booking.seats (
  seatid        serial PRIMARY KEY,
  venueid       int    REFERENCES booking.venue,
  seatnum       int,
  special_notes text,
  UNIQUE (venueid, seatnum)
  --stuff
);
CREATE TABLE booking.event (
  eventid         serial     PRIMARY KEY,
  event_name      text,
  event_timestamp timestamp  NOT NULL
  --stuff
);
CREATE TABLE booking.event_venue_seats (
  eventid    int     REFERENCES booking.event,
  seatid     int     REFERENCES booking.seats,
  txnid      int,
  customerid int,
  PRIMARY KEY (eventid, seatid)
);

Test Data

INSERT INTO booking.venue (venue_name)
VALUES ('Madison Square Garden');

INSERT INTO booking.seats (venueid, seatnum)
SELECT venueid, s
FROM booking.venue
  CROSS JOIN generate_series(1,42) AS s;

INSERT INTO booking.event (event_name, event_timestamp)
VALUES ('Evan Birthday Bash', now());

-- INSERT all the possible seat permutations for the first event
INSERT INTO booking.event_venue_seats (eventid,seatid)
SELECT eventid, seatid
FROM booking.seats
INNER JOIN booking.venue
  USING (venueid)
INNER JOIN booking.event
  ON (eventid = 1);

And now for the Booking Transaction

Now we have the eventid hard coded to one, you should set this to whatever event you want, customerid and txnid essentially make the seat reserved and tell you who did it. The FOR UPDATE is key. Those rows are locked during the update.

UPDATE booking.event_venue_seats
SET customerid = 1,
  txnid = 1
FROM (
  SELECT eventid, seatid
  FROM booking.event_venue_seats
  JOIN booking.seats
    USING (seatid)
  INNER JOIN booking.venue
    USING (venueid)
  INNER JOIN booking.event
    USING (eventid)
  WHERE txnid IS NULL
    AND customerid IS NULL
    -- for which event
    AND eventid = 1
  OFFSET 0 ROWS
  -- how many seats do you want? (they're all locked)
  FETCH NEXT 7 ROWS ONLY
  FOR UPDATE
) AS t
WHERE
  event_venue_seats.seatid = t.seatid
  AND event_venue_seats.eventid = t.eventid;

Updates

For timed reservations


You would use a timed reservation. Like when you buy tickets for a concert, you have M minutes to confirm the booking, or someone else gets the chance – Neil McGuigan 19 mins ago

What you would do here is set the booking.event_venue_seats.txnid as

txnid int REFERENCES transactions ON DELETE SET NULL

The second the user reserves the seet, the UPDATE puts in the txnid. Your transaction table looks something like this.

CREATE TABLE transactions (
  txnid       serial PRIMARY KEY,
  txn_start   timestamp DEFAULT now(),
  txn_expire  timestamp DEFAULT now() + '5 minutes'
);

Then in every minute you run

DELETE FROM transactions
WHERE txn_expire < now()

You can prompt the user to extend the timer when nearing expiration. Or, just let it delete the txnid and cascade down freeing up the seats.

StackExchange Database Administrators Q#158316, answer score: 7