锁定表中的行以进行 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
    )

当有人进行预订时，您会在预订表中插入未来某个指定时间段的日期时间值。当您寻找可用座位时，您的查询如下所示：

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 表，现在该席位已被永久占用。

Answer 2

您可以使用 SELECT ... FOR UPDATE 来为您锁定这些行 - 然后您可以计算出您选择了多少行，如果有足够的行，您可以使用锁更新它们值和时间戳。如果您不再需要这些行，您可以ROLLBACK来释放锁。
http://www.postgresql.org /docs/current/static/sql-select.html#SQL-FOR-UPDATE-SHARE

但是这些仅在事务期间有效，如果事务丢失，这些锁将被释放，因此您可以不要使用 SELECT ... FOR UPDATE 锁来保持行打开，您需要以某种方式将它们标记为保留。

一般来说，锁定然后等待用户响应是一种不好的技术。如果用户离开去洗澡等怎么办...那么您就会留下很多锁定的行。

看来你有两个选择：

1. 不要锁定任何东西，如果用户尝试选择一个座位，但后来座位已售完，只需道歉并向他们展示其他一些可用座位。另外，统计这种情况发生的频率，如果您发现这种情况发生得太频繁，您可以考虑锁定方案。

2. 按照您在问题中描述的操作，并制定一些规则，即座位预订在 2 分钟后过期等...这样您就不必担心显式释放锁，您只需检查它们的时间戳即可已设置。

Answer 3

竞争条件 - 我认为这作为插入而不是更新会更好。两个更新可以同时运行，并且不会相互冲突。如果您有“锁定座位”表，那么您可以引用 Seat_id 并使其唯一。这样竞争条件就会失败。但是，无论如何，我将其写为更新，就像您在问题中所做的那样，尽管您可以将其更改为插入。

如果没有足够的可用座位，您似乎一开始就不希望能够锁定座位。使用自连接这很容易：

create temp table seats
(
    id          serial,
    event_id    integer,
    locked      boolean default false
);
insert into seats (event_id) values (1),(1),(1),(2);
-- this will not lock event_id = 2 since it will not have a high enough count
update seats
set locked = true
from
(
    -- get the counts so we can drop events without enough seats
    select count(*), event_id from seats group by event_id
) as sum,
(
    -- you can not put limits in update; need to self-join
    select id from seats limit 2
) as t
where sum.event_id = seats.event_id
and seats.id = t.id
and count >= 2

;

UPDATE 2
 id | event_id | locked 
----+----------+--------
  3 |        1 | f
  4 |        2 | f
  2 |        1 | t
  1 |        1 | t
(4 rows)

因此，这会为每个至少有两个座位的活动“锁定”两个座位:)

Answer 4

只是一种替代方案 - 如果您只是“让”下一个客户也购买座位并向第一个用户提供错误弹出窗口，以防他在第一个客户购买后选择这些座位，这意味着什么？

整个用例可以稍微改变（我正在考虑这里发生的票务预订） -

客户选择电影 - 放映时间 - 显示所有空座位列表，实时更新 - 带有颜色编码。然后根据客户选择的座位，这些座位将被支付。

您使用的锁定机制将始终显示比实际售完的座位更多的座位 - 这可能会不必要地导致销售损失。另一方面，如果您只是在用户实际购买座位时进行简单检查，以检查在检索这些座位和预订座位之间的时间内座位是否已出售给其他人，那么您总是可以显示错误消息。即便如此，顾客选择座位后直至付款，还需要锁定座位；但这样你就不会遇到系统选择座位的问题，而是顾客选择座位！

Answer 5

在事务中执行更新/选择语句。如果只返回一行，则回滚事务，锁将被恢复。

Answer 6

如果我正确理解了您的问题，我认为解决方案可能如下：

进行以下事务（当然是伪代码）

<lock seats table>

  result=SELECT count(*) FROM seats 
   WHERE status="unlocked"
   GROUP BY status
   HAVING count(*)>=2

 IF result EXISTS

    UPDATE seats SET status = "locked", lock_time = NOW(), lock_id = "lock1" 
    WHERE status="unlocked"LIMIT 2


 <unlock seats table>

您可以通过这种方式快速释放表锁。然后如果用户不想要他们的订单
可以通过简单的更新取消。此外，锁定索引不会阻止其他更新

另一个更合理的方法恕我直言是以下伪代码。我认为这是一个很好的方法，因为它是无状态的，并且您不会锁定记录（使用数据库锁）等待用户决定（应该不惜一切代价避免）

 result=SELECT * FROM seats 
         WHERE status="unlocked"
 <present the result to the user and let the user decide which n seats they want>
 array[] choices:=<get from the user>

 //note that we do not lock the table here and the available seats presented to the     
 //user might be taken while he is making his choices. But that's OK since we should
 //not keep the table locked while he is making his choices. 

 <lock seats table>
 //now since the user either wants all the seats together or none of them, all the
 //seats rows that they want should be unlocked at first. If any of them
 // is locked when the UPDATE command is updating the row, then we should rollback all 
 // the updates. Unfortunately there is no way to determine that by standard update      
 // command. Thus here I use the following select query before making the update to
 // make sure every choice is there.

 result= SELECT count(*)
         FROM seats
         WHERE status="unlocked" AND seat_id IN (choice[1],choice[2], ...,choice[n])

 IF result=length(choices)

    UPDATE seats SET status = "locked", lock_time = NOW(), lock_id = "lock1" 
     WHERE seat_id IN (choice[1],choice[2], ...,choice[n])

  <unlock seats table>

问候
阿米尔