December 25, 2004 at 11:32 am
The subject is so named because I need to implement something like what TM does on its web site. We are selling tickets, and have a specific number of tickets of each price/quality. It is NOT serialized inventory (i.e. we don't sell you specific seats, just seats of a given quality). Here is the basic scenario:
a) User #1 requests N tickets.
b) Software subtracts N from Available Inventory.
c) Start a 5-minute timer.
d) User either completes the transaction or the timer times out. In the latter case, add N tickets back to Available Inventory.
This sequence can occur for an indefinite number of users at once. Call that number P. So I need a method of setting up P timers. Lately I've been thinking that a trigger might be the way to go with this. Or a sproc that executes every minute. I haven't looked into the granularity of the schedule thing, dunno yet if you can go down as far as one minute. It certainly wouldn't hurt performance to run such a sproc once a minute, given that all it has to do is delete rows whose timestamp is older than Now - 5 minutes.
Any advice or strategies in approaching this are most welcome!
TIA,
Arthur Fuller
December 27, 2004 at 12:21 am
Lots of ways of doing this...
Off the top of my head, I'd probably create a "pending" transaction table something like:
req_id INT IDENTITY
,ticket_type --key info to tickets
,reserve_qty INT
,expire_date DATETIME
Then I'd make any request (for avail qty) take the available amount from the main table and subtract the sum of the reserved qtys from this table (where not expired).
Any preliminary (reservation) transaction adds a row to this table (and can also delete any expired entries).
The fulfillment process (order confirmation) can remove the entry (and any other expired) while updating the "sold_qty" on the main table.
This way, transactions should be tight enough not to interfere (depending on your volume tho-), and clean up is automatic and no periodic process is required.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply