| Author |
Topic |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2010-04-29 : 10:36:28
|
| Hi there,I'm working on a shop that has a limited number of tickets to sell and they will sell very quickly. Crude stock control seems to work on the principle that one can pay for something or add it to their cart if the current total items available is greater than zero. The problem with this follows...Total Available: 1Thread 1: Checks available > 0.Thread 1: Adds item to cart.Thread 2: Checks available > 0.Thread 1: Reduces stock by one.Thread 2: Add item to cart.Thread 2: Reduces stock by one.So now the stock is -1!What I'm thinking of doing instead is to have a table of product instance locks where there is one row for each ticket available. Then when a user adds an item to their cart, their cart references one of the product instance lock rows. The databases' referential integrity feature or maybe a unique constraint will then stop any other user from getting a lock on the same ticket. Of course there will have to be a database job which regularly looks for abandoned carts (say, ten minutes since the item was added to the cart) and de-references the tickets so that someone else can attempt to buy them.Any ideas?Cheers, XF. |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2010-04-29 : 12:21:42
|
| I've had another idea.I have a table of products so I put the stock count for each product along with the other product data. Then when a user wants to add the product to their cart, the code reads the stock count for the product with an exclusive lock. It does the check and then if the stock is greater than zero, it reduces the count by one. If the product doesn't ultimately get purchased then the database job will delete the user's shopping cart and add one back onto the stock count.I can see a couple of issues here... a) all users will block on the product row whilst its being read/updated with an exclusive lock. I'm not sure whether this is any different than what would happen with my previous idea because there, if one SELECTs an unreferenced ticket, its also going to have to be with an exclusive lock to stop any other users from getting assigned the same ticket. So won't there be the same degree of blocking there too? b) its possible that carts could get deleted and the stock count is not updated. I suppose I could avoid this with a trigger that guarantees the update is done. |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-04-29 : 12:31:02
|
| lateral thinking....(not fully thought through, but it may work...and it may overcome locking (but that's not something i can standover, but I've done something similar years ago and it was an improvement) instead of maintaining a stock count and reducing/increasing same on each trans...why not allocate them a "specific" instance of the product with a timeout factor, so now instead of a product table with an "o/s count", you have a "product_item" table with multiple items "reserved/not reserved" and a "reserved at/bought at" time. the custoemr still buys/reserves a product, but under the bonnet you implement same as "product item #x reserved (or multiple product_items reserved)" |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2010-04-29 : 20:39:30
|
| Hi Andrew, isn't this the same idea that I proposed in my initial post?Anyhow, I think your idea still requires locking because one needs to be able to SELECT an un-reserved ticket and then it needs to be updated to make it reserved. If two concurrent users SELECT the same unreserved ticket there's going to be a conflict because they're both going to be trying to reserve it. So I think an un-reserved ticket is going to need to be read with an exclusive lock and all other users are going to block on it until its been reserved and released and then their SELECTs will continue on to look for unreserved rows. |
|
|
|
dportas
Yak Posting Veteran
53 Posts |
Posted - 2010-04-30 : 11:40:25
|
| Reduce the stock as soon as you add it to the cart. Then have a garbage collection task that periodically clears up unwanted carts and adds them back to stock.That way you don't need to hold lengthy transactions open. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-04-30 : 11:50:48
|
| "SELECT an un-reserved ticket "...others looking for such an item could look for any random one available "order by NEWID()"....they all don't have to look for the next one in sequence. the randomness may help mitigate locking. |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2010-04-30 : 13:28:07
|
quote:
That way you don't need to hold lengthy transactions open.
The transaction isn't being held open any longer than needed to read the row and update it.quote:
the randomness may help mitigate locking
This seems to work...CREATE TABLE [dbo].[tbl_product_reservation]( [ProductReservationID] [int] IDENTITY(1,1) NOT NULL, [Reserved] [bit] NOT NULL, CONSTRAINT [PK_tbl_product_lock] PRIMARY KEY CLUSTERED ( [ProductReservationID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOINSERT tbl_product_reservation ( Reserved ) VALUES ( 0 )INSERT tbl_product_reservation ( Reserved ) VALUES ( 0 )INSERT tbl_product_reservation ( Reserved ) VALUES ( 0 )GOBEGIN TRANSACTIONDECLARE @reservationID intSELECT TOP 1 @reservationID = ProductReservationID from tbl_product_reservation WITH (UPDLOCK READPAST) WHERE reserved = 0PRINT 'ID: ' + Cast(@reservationID AS varchar(50))UPDATE tbl_product_reservation SET reserved = 1 WHERE ProductReservationID = @reservationIDCOMMIT TRANSACTION Give it a go with multiple connections!I got some help from here... [url]http://www.mssqltips.com/tip.asp?tip=1257[/url] . This idea is clearly better for concurrency because everyone won't be blocking on a single product row or a single product reservation row (The latter thanks to the READPAST hint). |
|
|
|
dportas
Yak Posting Veteran
53 Posts |
Posted - 2010-04-30 : 14:08:24
|
| I don't see the purpose of having one row per item. I thought only the quantity was significant. Are there differences between each of these products? |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2010-04-30 : 14:42:15
|
| Hi dportas,No there are no differences between the products...If one has the following table...tbl_product===========ProductID intName varchar(50)Stock int... then before a user can add the product to their cart, the system needs to check if the stock value for the product is greater than zero. This means SELECT-ing the value, checking its greater than zero and then UPDATE-ing the product with the stock value minus one.If multiple users are trying to add the product to the cart at once then the product row needs to be locked whilst its being read and updated (see my initial post for what happens if there's no locking). This means that each user has to wait their turn to read the stock value so the database access has been serialized which is bad for concurrency.So the point of the product reservations table is that each concurrent user's transaction can leap in and grab the first available product instance without having to wait in a queue. If no product reservation row is found then the product is out of stock. Check out the article I referenced as it demonstrates step by step a similar situation.Let me know if this makes sense! |
|
|
|
dportas
Yak Posting Veteran
53 Posts |
Posted - 2010-05-01 : 04:22:28
|
| You can do it in one UPDATE statement. No need to query and hold a lock.UPDATE Stock SET Qty = Qty - @OrderQtyWHERE Qty >= @OrderQtyAND ProductID = @ProductID;IF @@ROWCOUNT=1 /* Success! /*ELSE /* Failed */This is the way it is done for many sites and generally it should scale OK unless you are expecting 1000s of transactions per minute. If you really do need that kind of scale then I suggest you look into purchasing an enterprise ecommerce platform rather than trying to build your own. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-01 : 04:39:46
|
I think e-commerce is a nightmare in this regard. In a bricks&mortar shop people put the last physical item into a basket, and they don't take 24 hours to get to the checkout!When I buy theatre tickets the site tells me that putting it in my "basket" is good for, say, 30 minutes. (I am buying specific seats in this example, so putting a time-limited reserve on them)I think that is a good analogy, but you could tell the users that its "first to checkout wins" if you want to encourage people not to dither  I wouldn't do a garbage collection as you will be showing "out of stock" a lot of time if you let people freely put items in their basket and not incentivise them to checkout.If you have non-zero physical stock, but zero stock after in-basket allocations, you might offer additional customers a "wait list", so if anything in people's baskets times-out, or is deleted-from-basket, then the people waiting get a chance to buy (dunno if you want them to hang around online, or if you will email them for a "second chance" type sale; at least that way Marketing get people's email addresses so they can spam them ... )So ... my approach would be:Step 1: the action of putting an item in the basket creates a stock allocation record (with an expiry date/time), and it increments the Allocated Stock for the product (but does not decrement the product's physical stock at that time).At checkout (final step after credit card clears etc) the Allocation record is deleted, the Allocated Stock is decremented and the Physical Stock is decremented.The action of putting an item in the basket will first have to initiate a Stock Level Check.I think this could be responsible for doing the garbage collection - if there are allocation records that are past-expiry then delete them, and decrement the corresponding allocated stock (and if you implement the waiting list feature then check if anyone is waiting and if so create an allocation record for them for the stock amount just released, plus decrement the amount of stock they are waiting for [in case Qty=2 have been released but the first person in the wait queue wants Qty=3 or more], or if the qty released exceeds the requirement of the first person in the queue then loop and allocate to the next person in the queue).If the Stock Level Check finds that there is free stock available then do the the allocation procedure, as per step 1  The Waiting Queue would also allow you to hold stock back for promotional material (VIPs, Mates [like me!!], etc) and then release that to the Wait List; it can also be used for any Product Returns etc. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-01 : 04:46:18
|
| P.S. Make sure you use READ_COMMITTED_SNAPSHOT as in high concurrency systems that will prevent Reads blocking Writes |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2010-05-01 : 13:02:58
|
quote:
Originally posted by dportas This is the way it is done for many sites and generally it should scale OK unless you are expecting 1000s of transactions per minute.
The difference between the two solutions at hand is the ability to deal with concurrency. I think you're right though that only a really big site would need a separate table of product reservations and I don't think the site I'm working on is that big. So I will go for a single product row with a StockCount column. Your UPDATE still needs to be within a multi-statement transaction though because somewhere the time that the allocation was made needs to be recorded so that the garbage collector knows whether to return the items back to the stock.But yes, you're right... in the code I posted only an UPDATE is required. I think I had it in mind that I was going to need to return the id of the SELECT-ed rows as well as UPDATE them.quote:
Originally posted by Kristen I wouldn't do a garbage collection as you will be showing "out of stock" a lot of time if you let people freely put items in their basket and not incentivise them to checkout.
Why does your approach have garbage collection then? If one is going to lock the stock then one needs garbage collection. The question is can one get away without locking the stock? Even if the stock is checked just after the shopper has pressed the final submit button, another user might complete payment for the last available item just before the first user's session sends payment off to the bank. I suppose technically you could always refund a user their money if when you finally come to write into the database that the user has bought something you find that the item is no longer available but my hunch is that this is dodgy.quote:
I think that is a good analogy, but you could tell the users that its "first to checkout wins" if you want to encourage people not to dither
This could be good especially given that the tickets being sold here are indistinguishable. I think it would minimise 'out of stock' situations if the product lock was only added when the user presses the final submit button. Regular abandoned carts wouldn't detract from the stock - in fact stock would only get 'lost' if the trip to the bank didn't return. But me and the guy who run the shop agree that its better for the user to feel that they have 10 minutes or so to buy a product that they've selected rather than have them fill in their payment cart details and press submit only to be told that the item is out of stock.quote:
The action of putting an item in the basket will first have to initiate a Stock Level Check. I think this could be responsible for doing the garbage collection
Surely its cleaner to have an independent process doing this? I'll probably do this how you suggest anyhow because of shared server restrictions. Maybe by having the page with the 'add to cart' button on it do the garbage collection when its first requested and only after one minute since the last check.quote:
The Waiting Queue
I was thinking of just a message saying something like all the items are in carts so try again later. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-02 : 03:42:59
|
"Why does your approach have garbage collection then?"I was referring to what dportas said about having scheduled garbage collection"Then have a garbage collection task that periodically clears up unwanted carts and adds them back to stock"which would leave the product out of stock until the next scheduled garbage collection. Scheduled garbage collection will also run whether there is work to do, or not. What I propose releases stock "just-in-time" at the time that it needs to be reallocated (someone wants some, all free stock is allocated, but you won't show out of stock if there is stock that can be re-allocated)." Even if the stock is checked just after the shopper has pressed the final submit button, another user might complete payment for the last available item just before the first user's session sends payment off to the bank."No, the customer has allocated stock, its theirs! and its not available for anyone else . However, they must complete checkout before the allocation expires (otherwise they lose their allocation), and the allocation does not need to be converted to a change to physical stock until payment is completed.I would change allocations to a different status that indicates "Checkout started" when processing the credit card, (and have the reassign process provide some leeway before reassigning that stock allocation record) to avoid the allocation being re-assigned during credit-card processing, but there is always the possibility that COMMs is playing up and credit card transactions are slow, or the user is slow doing 3D-Secure [or is forced to do a first-time registration for 3D-Secure  ], or somesuch, so you still need a refund process (unless there is physical, unallocated, stock you can reassign at the point where a customer payment completes but they no longer have a stock allocation record!)"I was thinking of just a message saying something like all the items are in carts so try again later."Yeah, depends how much complexity you want. But "Please try later" is a race - people may just sit on the retry button - which may give your server a headache! and it also depends whether you want to harvest EMail addresses for marketing campaigns in the future etc. - most of our clients see all that sort of thing as essential to business Note re 3D Secure: I don't know what part of the world you are in, but in the UK "3D Secure" is a process by which the customer visits a 3rd party that does a separate "pass-phrase" verification of the customer to have grater confidence in the customer's identity. The first time a customer is confronted with 3D Secure check they have to register, give their date of birth and other "personal validation" data, and then a 3D Secure account is created for them. This obviously takes more than a couple of seconds, and extends the "Credit Card Check" elapsed time considerably. |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2010-05-02 : 10:01:36
|
quote:
No, the customer has allocated stock, its theirs! and its not available for anyone else
Yes I was trying to show that there is a need for allocated stock and thus a need for some form of garbage collection.quote:
I would change allocations to a different status that indicates "Checkout started" when processing the credit card
Yes I agree and I also think that changing the status of the allocation or 'locking the lock' is needed in case the same user presses their submit button more than once or tries to submit the same order or update the cart via a different browser window.So upon an order submit, the system attempts to update the status of the allocation. If its already in the 'Checkout started' state or it doesn't exist (because its been reallocated or deleted) or its for a quantity of items that is different to the number of items in the cart then the check out doesn't proceed.quote:
(unless there is physical, unallocated, stock you can reassign at the point where a customer payment completes but they no longer have a stock allocation record!)
Yes like a reserve. Before we had some dummy tickets available which would have kept the system working smoothly until we could figure out what to do. One could also just require an admin to free up allocation records if the bank processing never returns - its not as if it happens that often.quote:
But "Please try later" is a race - people may just sit on the retry button - which may give your server a headache!
Good point. I will look into doing this.Thanks for the feedback! And I'm in the UK by the way. |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2010-05-02 : 20:39:27
|
quote:
Originally posted by dportas
You can do it in one UPDATE statement. No need to query and hold a lock.UPDATE Stock SET Qty = Qty - @OrderQtyWHERE Qty >= @OrderQtyAND ProductID = @ProductID;IF @@ROWCOUNT=1 /* Success! /*ELSE /* Failed */This is the way it is done for many sites and generally it should scale OK unless you are expecting 1000s of transactions per minute. If you really do need that kind of scale then I suggest you look into purchasing an enterprise ecommerce platform rather than trying to build your own.
I've discovered an issue with this. Lets say there are two items available and I ask for three. This query will give a failed when in fact the shop could still sell two items! If one reads the data before updating it then one can work out how much of the original request can be satisfied. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-03 : 05:12:46
|
| We would just deny the request at this point and let the customer re-choose what they want. (We show the Qty available on the Product page, so if the customer puts in more [or some have sold in the meantime] we just say "Sorry, only X available").I think the customer would be confused if they ask for 3 and you put 2 in their basket ... even if you tell them that's what you have done! |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2010-05-03 : 18:41:55
|
| Maybe I'm trying to do too much in a single query - perhaps I'm trying to make exception handling part of the normal flow of the app. But I'm confident that a user can comprehend being given less than they asked for especially if there's a message to explain it. In my case, its not good to reveal how many tickets are left and if the full number of items requested is denied then the user will probably use trial and error to get as many of what is left as they can. I think they'd appreciate having this done for them. Holding update locks on rows whilst they're being examined will of course reduce concurrency. |
|
|
|
dportas
Yak Posting Veteran
53 Posts |
Posted - 2010-05-04 : 02:15:00
|
Try this:UPDATE StockSET @ActualOrderQty = CASE WHEN Qty > @OrderQty THEN @OrderQty ELSE Qty END, Qty = Qty - @ActualOrderQtyWHERE ProductID = @ProductID; |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-04 : 02:55:32
|
| Ebay's way to not reveal exact amounts of stock help (and the way some of our clients want too) is to say "More than 10"; once stock level falls below that point then exact stock level is shown - which possibly has the benefit of getting people to grab the last remaining few . Just a thought, but its up to you how you want to handle the situation of course.Another way would be if they ask for 3 and you only have 2 is to create a Stock Reservation for the 2, and then say to the customer "Would you like the 2 we do have?". If they say No you can immediately release the Stock Allocation record, if they say Yes then put it in their basket (you'll need to have some means of knowing that there is already a stock allocation record ...), and if they do nothing then if they try to buy 2 later on (before stock allocation record expires) they can still have them." perhaps I'm trying to make exception handling part of the normal flow of the app"No, I don't think so. Our SProcs that "save" user-supplied data have the ability to return an explanatory message when they find something goofy; we try to provide as many of the data validation messages as we can client-side before they press SAVE, but those that are only established server-side are communicated back to the customer via a message from the "Record Save SProc"." Holding update locks on rows whilst they're being examined will of course reduce concurrency."I've missed a bit because I don't see what causes you to need to hold a lock? (and which I agree you need to avoid!!) |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2010-05-04 : 10:53:15
|
quote:
once stock level falls below that point then exact stock level is shown - which possibly has the benefit of getting people to grab the last remaining few
Yes that might be helpful. Once the stock falls below the max number of items per order then its easy for the shopper to find out how many are left anyway.quote:
Originally posted by Kristen I've missed a bit because I don't see what causes you to need to hold a lock? (and which I agree you need to avoid!!)
Well if there's insufficient stock to satisfy a request then if you want to assign what's left of the stock then you'd need to seperately look up what the remaining stock actually is.But perhaps not with dportas's latest query. (That's really cool that its possible to retrieve values when doing an UPDATE.) Do you have another solution? |
|
|
|
Next Page
|
Strict Stock Control