Jim Lawrence
accessd at shaw.ca
Sun Dec 26 01:22:30 CST 2004
Hi Arthur: On an application, I was involved with, that required a specific time, the purchase was recorded down to the hundredth of a second. The time was recorded from the moment that the individual started the actual purchase event but the first person who completed the payment transaction was the individual that was recorded. The system was setup to simply roll-back any process that did complete on time, against a particular product and display some message like 'The transaction could not completed, please try again.' In the couple of years that followed the roll-out, I never heard of a transaction that had to be aborted part way through, by the system. The time between the start of the session and the completion could be 10 plus minutes... My thought on the subject is that the first person who's card transaction completes get the tickets ... 'you snooze you loose'. Jim -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller Sent: Saturday, December 25, 2004 10:06 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] TicketMaster-like Timer In this particular case, it is not serialized inventory. In fact, we often don't know the particular seat numbers when we go on sale. Rather, what we know is that we are guaranteed possession of say 200 GA tickets, 200 Silver and 200 Gold. And also that they are in the best half of said blocks. For example, our U2 stuff goes up for sale on Jan. 6, which is when the tour will be officially announced. You might be buying tickets to a concert in July. We won't actually get the tickets in our hands until sometime in June, but since we're partnering with U2 on this tour we know we'll get the tickets. The customer receives a voucher etc. and depending on the package she buys, perhaps also a hotel voucher and maybe a limo voucher etc. So what we are concerned about is the number of tickets of each quality that is available. When you're dealing with serialized inventory, particularly reserved seat tickets, there are lots of issues that are pretty difficult for software to handle, but rather simple for a human. For example, you have to be very careful about selling single tickets, because you most definitely don't want to sell one seat from a group of 4 adjacent seats, although you will sell one on either end of a group of 5 adjacent seats. The general rule is that only losers go to concerts alone, so devote your efforts to the groups of tickets, first by even numbers and then by odd numbers. Another cute wrinkle in this situation is that you may want 4 tickets and I have no groups of 4 left, but I do have two "vertically" adjacent pairs (row 6, seats 7 and 8, row 7, seats 7 and 8). On its face, that requirement is not all that tough to meet with software, but then you have to factor in the fact that there are several different numbering systems in use at the various venues, a couple of which seem to have been designed by people in serious need of professional help. Anyway, those are not my concerns with this app. I'm concerned only with the timer-issue. So the specific problem is: 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. Arthur P.S. It's Christmas day and you can see that I have no life. John W. Colby wrote: >Arthur, > >The thing here is that you are selling SPECIFIC inventory items, i.e. >specific seat numbers in specific locations, not a generic telephone or >laptop. Thus you have specific inventory records, and can reserve those >with a date stamp field in that specific record and some field that says >what "user" owns the seat. Set the date stamp, then clear it and the >"owner" X minutes later. > >John W. Colby >www.ColbyConsulting.com > >Contribute your unused CPU cycles to a good cause: >http://folding.stanford.edu/ > >-----Original Message----- >From: dba-sqlserver-bounces at databaseadvisors.com >[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur >Fuller >Sent: Saturday, December 25, 2004 12:06 AM >To: dba-sqlserver at databaseadvisors.com >Subject: Re: [dba-SQLServer] TicketMaster-like Timer > > >Regardless of the particular inventory stocked, assume that it moves >quickly and that there are a finite number of objects available. Looking >at the TicketMaster layout, you have 5 minutes to comple an order or >it's cancelled. Which means, I think, that some magic happens in the >back end that subtracts the number of tickets requested from the >available inventory, counts ticks, and if you don't press Submit quickly >enough your order is toast. This is the part that is giving me >conceptual problems. Assume 49 people are logged on and there are 100 >inventory objects available. How do I set up 49 timers and roll them >back after exactly N minutes? > >A. > >Mark Breen wrote: > > > >>Hello Andy, >> >>I hear what you are saying but.... >> >>1) If you delay long enough on an Airline site, do you believe that you >>will retain the seat >>2) If you delay long enough on a Dell site, do you really think that >>the PC is being 'held' for you. >> >>The reality is that you either disappoint many customers by having the >>stock all tied up with enquiries or people that do not have the ability >>to pay for it, versus, once in a blue moon, you have to display a >>message that "this product has just gone out of stock, but we would now >>like to offer you this as an alternative", depending on the scenario, >>you could even offer it as a free upgrade. >> >>I accept we are only talking in general terms here, we do not know >>whether Authur is talking about Blood donations or Candy bars in a >>store. But my point is just to consider the implications of tying up >>stock with mere prospects rather than customers. >> >>I did not get as far as suggesting, but Authur could also consider more >>sophisticated means of pooling the enquiries and limiting them to >>10-20% of the stock, so that all enquiries are seeing the same pool. >> >>All this is difficult without knowing the business in detail (which is >>not the intention here), but I still say that it is be be considered. >> >>Perhaps it is a B2B model within one company and in that case, it is OK >>to hold the stock in advance. >> >>Cheers and have a good Christmas, >> >>Mark >> >> >> >> >> >>On Mon, 20 Dec 2004 11:26:26 +0100, Andy Lacey >><andy at minstersystems.co.uk> wrote: >> >> >> >> >>>As a customer of these kind of sites (and I am) I'd soon stop using it >>>if it told me there were tickets, I said I'll take 1, went to the >>>trouble of putting in my details, then my cc details, then it told me >>>my tickets had been sold to someone else. It may sound like business >>>sense not to hold that stock, but the longer term good business is not >>>to aggravate your customers. >>> >>>-- >>>Andy Lacey >>>http://www.minstersystems.co.uk >>> >>> >>> >>> >>> >>> >>_______________________________________________ >>dba-SQLServer mailing list >>dba-SQLServer at databaseadvisors.com >>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >>http://www.databaseadvisors.com >> >> >> >> >> >> >> > > > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.298 / Virus Database: 265.6.4 - Release Date: 12/22/2004 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com