sql - Locking rows in a table for SELECT and UPDATE -

i writing script needs book seats in cinema.

1. user asks 2 seats
2. if there 2 seats available, system offers them client
3. client can either accept them or request 2 seats.
4. when accepts, seats marked "sold"

since there can multiple users using system simultaneously, need way "lock" rows offered current client until time passes, or requests seats.

currently marking offered seats "locked" client id, , use select return them client (this mysql, target database postgres)

update seats set status = "locked", lock_time = now(), lock_id = "lock1" limit 2 select * seats lock_id = "lock1" , lock_time > date_sub(now(), interval 2 minute) 

there problem that: if there's 1 seat available, still marked "locked" , have release lock right away.

i pretty sure there smarter way of doing that. correct way of dealing task that?

race condition - think better insert rather update. 2 updates can run @ same time , not conflict each other. if had 'locked seats' table reference seat_id , make unique. way race conditions fail. but, in case, wrote update have in question although change insert.

it seems not want able lock seats in first place if there not enough available. easy self joins:

create temp table seats (     id          serial,     event_id    integer,     locked      boolean default false ); insert seats (event_id) values (1),(1),(1),(2); -- not lock event_id = 2 since not have high enough count update seats set locked = true (     -- counts can drop events without enough seats     select count(*), event_id seats group event_id ) sum, (     -- can not put limits in update; need self-join     select id seats limit 2 ) t sum.event_id = seats.event_id , seats.id = t.id , count >= 2 

;

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

so 'locks' 2 seats every event has @ least 2 seats :)