Table row locking for SELECT and UPDATE

Question

I am writing a script that needs to reserve seats in a movie theater.

• The user requests 2 places.
• If 2 places are available, the system offers them to the client
• The client can either accept them or request 2 more places.
• When he finally accepts, the seats are marked as “sold”

Since there may be several users using the system at the same time, I need a way to “block” the lines offered to the current client until a certain time has passed or he has requested other places.

Currently, I place the proposed places as "blocked" with the client ID and use SELECT to return them to the client (this is for MySQL, but the 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 this: if only one place is available, it will still be marked as “locked” and I will need to immediately release the lock.

I'm also sure there is a smarter way to do this. What is the correct way to solve such a problem?

Answer 1

- , , , . , . " ", seat_id . , . , , , , .

, , , . self:

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 2

, , - . , , - .

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 3

SELECT ... FOR UPDATE, - , , , . , ROLLBACK . http://www.postgresql.org/docs/current/static/sql-select.html#SQL-FOR-UPDATE-SHARE

, , , SELECT ... FOR UPDATE, , -.

, . , , .. .

, :

• -, , . , , , , , .

• , , - , 2 .. , , .

Answer 4

, , :

(, , )

<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 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>

Amir

Answer 5

- , "" , , ?

( , ) -

- , , . , , , .

, , , , . , , , , - , , , , ; , , !

Answer 6

/ . , .