Locking rows in a table for SELECT and UPDATE

Question

I am writing a script that needs to book seats in the cinema.

1. User asks for 2 seats
2. If there are 2 seats available, system offers them to client
3. Client can either accept them or request another 2 seats.
4. When he finally accepts, seats are marked as "sold"

Since there can be multiple users using system simultaneously, I need a way to "lock" the rows offered to current client until certain time passes, or he requests another seats.

Currently I am marking offered seats as "locked" with a client id, and use SELECT to return them to the client (this is for MySQL, but target database is Postgres)

UPDATE seats SET status = "locked", lock_time = NOW(), lock_id = "lock1" LIMIT 2
SELECT * FROM seats WHERE lock_id = "lock1" AND lock_time > DATE_SUB(NOW(), INTERVAL 2 MINUTE)

There is a problem with that: if there's only 1 seat available, it will still be marked as "locked" and I will have to release lock right away.

I am also pretty sure that there is a smarter way of doing that. What is the correct way of dealing with a task like that?

Answer 1

What you are discussing is a reservation system. The way I've built such systems is to have a table of reservations and table of seats.

Create Table Reservations
    (
    EventId ... not null References Events ( Id )
    , SeatNumber varchar(10) not null
    , Expiration datetime not null
    , CustomerId ... not null References Customers( Id )
    , Constraint FK_Reservations_Seats
        Foreign Key( EventId, SeatNumber )
        References EventSeats( EventId, SeatNumber )
    )

Create Table EventSeats
    (
    EventId ... References Events ( Id )
    , SeatNumber varchar(10) not null
    , CustomerId ... null References Customers( Id )
    , PurchaseDate datetime not null
    )

When someone makes a reservation you do an insert into the Reservations table with a datetime value some specified period of time in the future. When you are looking for available seats, your query looks like:

Select S.EventId, S.SeatNumber
From EventSeats As S
Where S.EventId = ...
    And S.CustomerId Is Null
    And Not Exists  (
                    Select 1
                    From Reservations As R
                    Where R.EventId = S.EventId
                        And R.SeatNumber = S.SeatNumber
                        And R.Expiration > CURRENT_TIMESTAMP
                    )

This allows someone to put a temporary hold on a seat if they want. If they want to purchase the seats, you insert another reservation record for some period into the future. In fact, the system I designed inserted a new reservation on each step of the purchase process that was 10 minutes into the future just to help the user finish the purchase process before the reservation expired. Once they complete the purchase you update the EventSeats table with their information and now this seat is permanently taken.