How does eBay end their auctions and mark the winner? Assume there is an auction end date in the database, once that time has passed the current time, the auction needs to be closed, mark the winner, etc. How is something like this handled in sql 2005? Do they query the db every second to find the expired auctions? Obviously they need to mark it as closed as soon as the auction ends. No way they are creating sql jobs for every single auction, or are they? Any ideas? I have an integration that follows a similar thought process and need help.
+1
A:
Obviously they need to mark it as closed as soon as the auction ends.
Yes, but not necessarily real time.
How is something like this handled in sql 2005?
Not at all. A DB is a DB. For real time pricing / auction calculation youwould use a program. Basically, update the database, but dont run the logic from the database.
Do they query the db every second to find the expired auctions?
One way. Another is to have a list ofauctions sorted by expiration in memory and just check there which expire.
No way they are creating sql jobs for every single auction, or are they?
Likely not.
What I would do is keep a list of auctions in memory. Scalability by having X auctions per server.
TomTom
2010-10-21 01:39:26
What if the end date is changing, for example the penny auctions that change the end date on every bid. The in memory queue may not be in sync unless you update it every couple of seconds.
2010-10-21 01:44:56
Ah, the in memory queue is the master, not the database. Changes are written out to the database for persisntence, but the in momery copy is the authentic master. There is no need to read the db unless on program start.
TomTom
2010-10-21 01:52:31
Hmm do you have any links to examples of this? I am using asp.net 4, never used an in memory queue because my previous apps were not that intense.
2010-10-21 01:55:40
Well, the problem is - they will not use a web front ent technology for this, but application servers. Examples? Hm, I have code here (financial market simulator), but I can not share it.
TomTom
2010-10-21 02:17:03
It looks like I can run a hybrid with sqlite? Have you used that before? Recommend others?
2010-10-21 02:39:12
The real question to ask is what volume of closing items are you expecting? How many per minute? If it's under fifty or so, then you can use two separate sql jobs to handle it as I described below. My auction engine, www.go-dove.com, handles about 300 a minute with no problem.
rboarman
2010-10-27 21:54:57
Ok, closing items - very little, all in "bulk" during maintenance windows (at least me). OTOH Iam running about 1.5 million updates *per second* in full mode. At least that is what we test again. Full financial feeds multiple exchanges and we track all changes.
TomTom
2010-10-28 06:34:59
A:
Depending on the scale of your project you may get away with a single SQL job that performs mass closing of the auctions. Run this every minute.
Update [Auctions] SET AuctionStatus = 'CLOSED' WHERE CloseDate <= CURRENT_TIMESTAMP;
Have your application logic only post bids to auctions where this same where clause is met.
You can then process auction winners with another scheduled job and runs against closed auctions that are not yet processed.
JRam
2010-10-27 21:46:42
You can't wait a minute for an auction to end. I used this method when I wrote an auction system but would not do so again. However, I first had a job that ran every ten seconds to set the closed items to "processing" and had another job that run every other minute to actually close them. The time it took to process was fairly lengthy due to the number of database records to create and update and a number of emails that needed to be generated.
rboarman
2010-10-27 21:51:14
Our biggest auction was Enron and yes we had load problems. Enron load was 20,000 simultaneous users. In general, with normal load, which was about 2,000 people, we had no issues.
rboarman
2010-10-27 23:04:10
A:
First thing that pops into my head is a javascript function that is called when the auction countdown timer reaches a certain number, in this case I would imagine 0. Have the function make a ajax call to a script, which would verify that the auction has indeed expired, then update the database.
David
2010-10-27 21:57:36
This won't work as you would have to have the item open in a browser in order for it to close. Not practical.
rboarman
2010-10-29 23:58:18
+1
A:
I would consider the state of the auction a result of the end-date of the auction and the current time. You don't have to make a change to the database in order for the state of the auction to change, the current time passing the auction end-date will do that for you.
A user viewing the auction will only view the state on a request basis, so the state can be determined at request-time. Any physical actions (sending email to winner) do not have to be true real-time. Just the closing of the bidding has to be; and the status can be determined on a per-bid basis
ontrack
2010-10-27 22:02:49
+1
A:
I had imagined that an Auction record has an attribute like EndDate. No bids would be accepted beyond that point. When bidding, the client application would compare Now to EndDate. As well, the DB would check Auction.EndDate against Now() when attempting to log that bid.
I'm not sure that clients should be updating the auction.status="Closed" at all. That's a dependency on a client, where perhaps the DB shouldn't have to rely on a client to 'close' an auction. Suggest that should be predetermined, and not require intervention.
p.campbell
2010-10-27 22:03:04