sql - 为 SELECT 和 UPDATE 锁定表中的行

Question

我正在编写一个需要在电影院预订座位的脚本。

1. 用户要求 2 个席位
2. 如果有 2 个可用的座位，系统将它们提供给客户
3. 客户可以接受它们或请求另外 2 个席位。
4. 当他最终接受时，座位被标记为“已售出”

由于可以有多个用户同时使用系统，我需要一种方法来“锁定”提供给当前客户的行，直到特定时间过去，或者他请求另一个座位。

目前我用客户端 ID 将提供的席位标记为“锁定”，并使用 SELECT 将它们返回给客户端(这是针对 MySQL，但目标数据库是 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)

这有一个问题:如果只有 1 个座位可用，它仍将被标记为“已锁定”，我必须立即释放锁定。

我也很确定有一种更聪明的方法可以做到这一点。处理此类任务的正确方法是什么？

Answer 1

你说的是预约系统。我构建此类系统的方法是有一张预订表和一张座位表。

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
    )

当有人进行预订时，您会在 Reservations 表中插入一个带有 future 某个指定时间段的日期时间值的数据。当您寻找可用座位时，您的查询如下所示:

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
                    )

这允许某人在需要时临时保留座位。如果他们想购买座位，您可以在未来的某个时期插入另一条预订记录。事实上，我设计的系统在未来 10 分钟的购买过程的每一步都插入了一个新的预订，只是为了帮助用户在预订过期之前完成购买过程。一旦他们完成购买，您就可以使用他们的信息更新 EventSeats 表，现在这个座位将被永久占用。