U200eRhL

1Locking rows in a table for SELECT and UPDATE
2UPDATE seats SET status = "locked", lock_time = NOW(), lock_id = "lock1" LIMIT 2
3SELECT * FROM seats WHERE lock_id = "lock1" AND lock_time > DATE_SUB(NOW(), INTERVAL 2 MINUTE)
4	
5create temp table seats
6(
7    id          serial,
8    event_id    integer,
9    locked      boolean default false
10);
11insert into seats (event_id) values (1),(1),(1),(2);
12-- this will not lock event_id = 2 since it will not have a high enough count
13update seats
14set locked = true
15from
16(
17    -- get the counts so we can drop events without enough seats
18    select count(*), event_id from seats group by event_id
19) as sum,
20(
21    -- you can not put limits in update; need to self-join
22    select id from seats limit 2
23) as t
24where sum.event_id = seats.event_id
25and seats.id = t.id
26and count >= 2
27	
28UPDATE 2
29 id | event_id | locked 
30----+----------+--------
31  3 |        1 | f
32  4 |        2 | f
33  2 |        1 | t
34  1 |        1 | t
35(4 rows)
36	
37Create Table Reservations
38    (
39    EventId ... not null References Events ( Id )
40    , SeatNumber varchar(10) not null
41    , Expiration datetime not null
42    , CustomerId ... not null References Customers( Id )
43    , Constraint FK_Reservations_Seats
44        Foreign Key( EventId, SeatNumber )
45        References EventSeats( EventId, SeatNumber )
46    )
47
48Create Table EventSeats
49    (
50    EventId ... References Events ( Id )
51    , SeatNumber varchar(10) not null
52    , CustomerId ... null References Customers( Id )
53    , PurchaseDate datetime not null
54    )
55	
56Select S.EventId, S.SeatNumber
57From EventSeats As S
58Where S.EventId = ...
59    And S.CustomerId Is Null
60    And Not Exists  (
61                    Select 1
62                    From Reservations As R
63                    Where R.EventId = S.EventId
64                        And R.SeatNumber = S.SeatNumber
65                        And R.Expiration > CURRENT_TIMESTAMP
66                    )
67	
68<lock seats table>
69
70  result=SELECT count(*) FROM seats 
71   WHERE status="unlocked"
72   GROUP BY status
73   HAVING count(*)>=2
74
75 IF result EXISTS
76
77    UPDATE seats SET status = "locked", lock_time = NOW(), lock_id = "lock1" 
78    WHERE status="unlocked"LIMIT 2
79
80
81 <unlock seats table>
82	
83result=SELECT * FROM seats 
84         WHERE status="unlocked"
85 <present the result to the user and let the user decide which n seats they want>
86 array[] choices:=<get from the user>
87
88 //note that we do not lock the table here and the available seats presented to the     
89 //user might be taken while he is making his choices. But that's OK since we should
90 //not keep the table locked while he is making his choices. 
91
92 <lock seats table>
93 //now since the user either wants all the seats together or none of them, all the
94 //seats rows that they want should be unlocked at first. If any of them
95 // is locked when the UPDATE command is updating the row, then we should rollback all 
96 // the updates. Unfortunately there is no way to determine that by standard update      
97 // command. Thus here I use the following select query before making the update to
98 // make sure every choice is there.
99
100 result= SELECT count(*)
101         FROM seats
102         WHERE status="unlocked" AND seat_id IN (choice[1],choice[2], ...,choice[n])
103
104 IF result=length(choices)
105
106    UPDATE seats SET status = "locked", lock_time = NOW(), lock_id = "lock1" 
107     WHERE seat_id IN (choice[1],choice[2], ...,choice[n])
108
109  <unlock seats table>