Tickets locking

[mowebdev]

I haven't tried this sort of thing before, so I'd appreciate some pointers in
the right direction. I'm trying to create a low-key kind of event tickets
database. It occurs to me that it is possible for someone to be contemplating
a set of seats while another person goes in and signs up for them. What ways
would be good to temporarily lock a seat for a minute or so to give someone
time to request it without another person swooping in?

[paross1]

Much of this depends on the particular database and how you handle
transactions. For instance, Oracle provides for a SELECT ... FOR UPDATE and it
looks like SQL Server 2K has something similar in the FOR BROWSE clause. Using
a FOR UPDATE in a SELECT will "reserve" the selected rows as if you were doing
an UPDATE until you COMMIT or ROLLBACK. Using the FOR UPDATE NOWAIT keyword
will tell the database not to wait if the table has been locked by another
process, which will return control immediately to your program (stored
procedure) where you can handle the exception, etc. such as notifying the
calling process that the data is already selected, etc.

Doing this kind of thing with a web based application gets kind of tricky,
since you have to be able to tell when a particular "session" has ended, plus
you need to guard against somebody locking data indefinitely and going to
lunch, etc. You will need to research state management carefully to get this
right.

Phil

[Dan Bracuk]

Store "thinking about it" data in another table, and exclude this data from the
query that looks for seats.

You might want to add a scheduled job that deletes records from this table
that have been there too long.

[mowebdev]

I've compressed suggestions into this: add an "event lock date and time" and
"event lock user" to the event record. Check for an event lock current time LT
event lock date time or for event lock username. If those check out, update
the event record with a date/time two minutes into the future and current
username. Seems to work so far.