decrement stock when placing orders

[afif]

I have three requirements

1. I want to sell stuff online
2. I don't want to oversell
3. I don't want to rely on optimistic concurrency

Imagine a very simple scenario, you have 10 in stock, 4 users are attempting to place an order for 3 each at the same point in time. When you query it will show available to all of these users, but when placing the order, how do you ensure the last one gets out of stock, without relying on optimistic concurrency? I am interested in two things primarily

1. what solutions exist that do not involve putting a lock over the code that does the decrement on the stock
2. is it just as simple as using a lock and not caring about it? does this approach have any not so obvious pitfalls? obvious one being at how many concurrent users will this approach start to hurt?

My usual approach has always been to use persistent queues that are single threaded by SKU, but now I am interested in knowing is there a more simpler solution? I personally haven't seen any one do any thing different other than relying on optimistic concurrency most of the time. But I am hoping barring that, there are useful tried and tested patterns that can address this.

Thanks in advance.

[Oren Eini (Ayende Rahien)]

You can try sending a patch request with an output on the result. 

That would let you know if this worked or not.

However, what happens if you have the following scenario:

- You run the patch, which decrements the stock from 1 to 0.

- Your code accept the "there is still enough stock" for that request

- You crash...

You just effectively lost stock.

Oren Eini

CEO

Hibernating Rhinos

Cellular: +972-52-548-6969

Office:    +972-4-674-7811

Fax:       +972-153-4622-7811

--
You received this message because you are subscribed to the Google Groups "RavenDB - 2nd generation document database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ravendb+u...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[afif]

I just had an idea. But it sounds stupidly simple. Hopefully there's a catch. So here it goes. 

When assigning stock, you create an immutable document, so the stock count value is not allowed to be touched. When adding more stock you create another document. An advanced start loading with will give us the total sum of all stock allocated for a SKU. When placing orders for this sku, you create a document for each item ordered with id as 'skuorders/skus/1/'. The trailing / will ensure raven assigns it an auto incrementing number within that sku. So when you order two of a sku, you create two of such documents. 

Now when a user wishes to buy skus/1, you do Session.Advanced.LoadStartingWith<Stock>('stocks/skus/1/').Sum(x=>x.Qty) to get total allocated. Say its 150. And say the user wants to buy 10. You do 

Session.Advanced.LoadStartingWith<SkuOrder>('skuorders/skus/1/').Skip(140).Take(1). If this returns not null, it means already 141 orders have been placed for this sku, hence the user can't get 10. But say if this was null, then we proceed with creating 10 records, get the ids back, then check if the last id (the int after the trailing slash) of our inserted sku order was less then 151, if so, then we got the stock. but if not, we load the 150th document and check if its null, if it is, we still got the stock, if not, then if the id of this document is greater than our max id we still got the stock, otherwise no. Also if the user at a later stage cancels the order or payment declined, we simply delete the sku orders created for this user.

Now what am I missing?

[Khalid Abuhakmeh]

I would just solve the out of stock problem with a people process. If you sold out accidentally and you told the person it was in stock, just email them back and apologize and offer a discount or.... just get the items in stock and tell them it will be a few days until you get it to them.

It's always better to take the sale and apologize than to not take the sale. I wouldn't complicate your checkout process, just roll with the punches.

[Neil Barnwell]

You're making a slight domain modelling error here. Your database is not your book of record - your warehouse is. Even assuming you solved this problem, what happens when one day you go to pick an item and it's damaged, or was booked-in as the wrong product type. All along your db was wrong. Remember - YOUR DATABASE ONLY WHAT YOU *THINK* YOU KNOW.

You're better off embracing the eventual consistency concept entirely:

• If you think there's enough stock (as of a few seconds ago, at least) take the order.
  
• If the picker picks the item, charge the credit card.
• If there was a problem picking the item (lost, damaged, incorrect SKU etc) inform the customer.

Often it helps to imagine what would the users do if your system went offline (power cut, broadband failure). The warehouse would continue with various document-passing processes (pick-sheets, advice notes etc), and those are inherently eventually consistent - send a message to the picker asking him to pick stuff, and wait for a response that should come back in a reasonable amount of time (SLA - picking performance etc) and tell you how well that message was handled - deal with the outcome.

Neil.

[Mircea Chirea]

Khalid and Neil are correct. This is not something you can easily do in a transactional way; not even banks operate like this - the balance is calculated from past events, such as withdrawals and deposits. If it ends up negative (overdraft) a fee is imposed, or if it was a fraudulent withdrawal, other business processes take in (insurance for example).

You can use the same idea; in your case, if you have orders for more items than available in stock, contact the customer; they will probably be happy to wait a little more while the stock is replenished, if not, they may purchase another item, or simply cancel the purchase. The real world is inherently evetually consistent, you can't wrap that into an atomic transaction just because you are using computers.

Further reading on this subject:

http://ayende.com/blog/155969/your-atm-doesnt-use-transactions

http://ayende.com/blog/162788/from-the-mailing-list-concurrency-checks-for-related-unchanged-documents

[Kijana Woodard]

I agree with you guys, but I've had this conversation with Afif before. His situation, iirc, is a bit different.

The goods are unique and can't be replaced. OOS is forever. It may be that they are digital and not capable of being damaged, etc making the database the warehouse. Think tickets to an event.

@afif
Why is optimistic concurrency so bad?

I was going to suggest something similar whereby you simply record the intent to buy, but before telling the user you check that their intent is within the stock count. This gives you the benefit that if someone else's order cancels for whatever reason, you can notify the other person that their order can proceed.

--

[Neil Barnwell]

I don't really see how the uniqueness of the products has any bearing on it. If the products are digital, then it's not really a "warehouse" at all (copies could be made, for example) and if the Queen's Crown Jewels were managed by a warehouse system, no matter how unique or irreplacable they are, the fact is when you go to the actual vault to get them they might've been stolen.

As you say, the only thing I can think of where this is critical would be a seat booking system, but even then - a user puts in a request for seats and is told when their request is processed whether they were successful or not. Perhaps the seats are "reserved" for a short time while the user keys in their payment details, but the initial reservation is still eventually consistent with all the other users who may be online trying to reserve seats at the same time. When the successful reservation is placed, a message is sent to the "future self" of the system to remove the reservation if it wasn't paid for in time.

Neil Barnwell.
Mobile: 07900 221457
Twitter: @neilbarnwell
e-mail: mai...@neilbarnwell.co.uk
web: http://www.neilbarnwell.co.uk

--
You received this message because you are subscribed to a topic in the Google Groups "RavenDB - 2nd generation document database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/ravendb/PiM2p5wHTrQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to ravendb+u...@googlegroups.com.

[Kijana Woodard]

I certainly agree that eventual consistency is still a reality - payment failure, buyer's remorse, etc.

By warehouse, I meant that the db is authoritative.

I think there are twists in the domain such as this being b2b instead of b2c. For me, that makes a stronger case for ec, but I'd need to interview stakeholders to truly understand.

In the meantime, what's wrong with optimistic concurrency + retries again?

It all sounds like more worry than reality merits.

[Chris Marisic]

Agreed. Reality is eventually consistent.

[afif]

In principal the reason why I don't prefer optimistic concurrency (for transactional data) is because its not functional. If a counter is used to decide on what's available for purchase, then most likely there's an append only log some where that we fall back on to see how the counter came to its current value. I am looking for a solution where the append only model can be used to determine what's available so I give away with the counter all together and not have logic that is fighting for updates on a value.

In practice the reason why I shy away from optimistic concurrency with RavenDB, is owing to the 2% percent scenarios I have had in production where optimistic concurrency hasn't protected the value from a bad read/write. I had a fall back because I had orders coming in via a queue, so I could stop the queue, get a total on the orders and update the counter to be correct and then start to pick of the queue again. In other scenarios I may not have this benefit of a queue.

In essence, I agree a lot more goes into modelling stock updates and stock flow in its entirety. But regardless of whether the system is eventually consistent, or whether its b2b or b2c, there will always be a component that is responsible for handling database contention over a value that is being used to determine whether stuff is available before we collect payments. What happens after we collect payment, is a different flow independent to the responsibility of the above component. 

I am interested PURELY in what patterns to employ to make this component functional. I prefer a database model where there are no updates on transactional data, its append only.

[Lee]

Sorry, this is not entirely related to your question but in your last post are you saying that raven sometimes fails to adhere to optimistic concurrency?

Can you go into a bit more detail?

[Kijana Woodard]

I get that. Why not ES?

What you described is mostly that:

session.Store(order)

session.SaveChanges()

newSession.LoadStartingWith("orders/{sku}/")

newSession.LoadStartingWith("stock/{sku}/")

Calc whether there is any stock left when the order was inserted. Tell user.

Off your topic:

Now, if a previous order cancels, you can't delete/modify that order and still be "append only".

Also, you'd have to be good about recognizing "before customer A's order cancelled, customer C's order wouldn't have gotten fulfilled, but now it would so notify them".

Also consider partial fulfillment. There are 3 left, but I order 5. The person after me wants 3. What to do?

Can you do a quorum where you can keep taking prospective orders until all the stock is 100% for certain committed? That might fit your technical model more easily.

Never overselling and never underselling seems like a fairly tough nut to crack without putting other constraints in place: single threaded queue per sku (as you mentioned), no buyer's remorse allowed (you turn someone away and tomorrow someone calls the boss and weasels out, now what?).

[Kijana Woodard]

I'm sure Oren would like a repro which shouldn't be too hard if it happens 1 in 50.

On Thu, Jan 9, 2014 at 3:47 PM, Lee <safe...@hotmail.com> wrote:

Sorry, this is not entirely related to your question but in your last post are you saying that raven sometimes fails to adhere to optimistic concurrency?

Can you go into a bit more detail?

[afif]

To be fair, DTC had a large part to play with that happening. And from what I hear in this forum, the DTC beast hasn't been slayed. 

[Lee]

I was consistently having intermittent DTC related failures but since the fix I haven't seen a single failure.

[afif]

Kijana,

Yes in principal its exactly what ES solves, but the reason its not ES, is because I am not across the ground workings of ES comfortable enough to develop an app for production. I have read about it, agree with it, would love to use it. Its like some one who reads all these shiny posts about SOA, if they don't know enough on what it takes to implement it, they are in for a few surprises. With SOA I got saved by Udi's ADD course, unfortunately there are no such ES courses here down under. 

You have a valid point that my model is not true append only, since I will delete the skuorder record if the payment is declined or if the user decides not to submit payment. Since a SkuOrder document is an intent to buy, I am happy to delete it if they bail out. Its the closest I can come to ES, and not using updates on transactional data.

As for the off your topic part of your post, allow me to go over it a few times so I can grok what you mean. I'll reply then. :)

[afif]

Kijana,

Your quorum approach is what I had with Sagas by SKU. This way its possible to solve both over selling and under selling. All the scenarios you have listed were addressed. But without the luxury of a persistent queue, and a process that manages a thread by SKU, I currently don't have that requirement. I can turn people away if I can't fulfil their entire order. If an order ahead of them gets cancelled or declined, refreshing the page will show the SKU is available. But eventually yes, if required I would look to implement something similar where some one is put in a waiting list and they are notified first when some one else cancels. 

Thank you, and to everyone else on this thread who took the effort to post ideas/thoughts on a topic that is not that simple. 

Cheers,

Afif

On Friday, 10 January 2014 08:48:18 UTC+11, Kijana Woodard wrote:

[Oren Eini (Ayende Rahien)]

From a business perspective, what is the sense in turning people away _ever_ ?

Oren Eini

CEO

Hibernating Rhinos

Cellular: +972-52-548-6969

Office:    +972-4-674-7811

Fax:       +972-153-4622-7811

[afif]

Oren,
Three words - time to market. :)

[Justin A]

I feel that people are getting to hung up in the .. um .. (not sure if this is the right terminology) - higher domain of the question. 

Afif has asked a question about how to solve a problem with respect to RavenDB. Lets all try and ignore the higher issue about warehouses and getting a sale, etc .. because the question is pretty kewl and i'm really curious also to see if it can be solved using RavenDb (or more to the point, how). Sure those points about having negative stock and using humans to email etc are very valid ... but that's opinionated.

So, given his question (regardless of wether another valid scenario could be used instead) ... can his current one, be solved?

/me goes back to silently watching this thread.

[Kijana Woodard]

I think afif and I both outlined that.

To augment, if instead of deleting a cancelled order, you append an "order cancelled" event/doc, you can achieve append only. Now the state that needs to be tracked is: have we already told this customer their order can/will be fulfilled. The "wait list" is baked in, just left fold+zip the orders and stock.

--

[afif]

Not sure I follow you Kijana. But I would like to point out a gap in my original algorithm (gee, if I can call it that). Also before I get into the technicalities, I would like to point out, an opinionated approach on my part in solving this available for purchase contention issue, is to not use indexes. Because the moment you involve an index in asking that question there is a very real chance that answer is stale, hence you could collect payment from some one when in reality there is not stock. Like others have pointed there are other ways to mitigate that, and possibly also narrow it down to a select few, the prospect of a solution using no indexes and having guarantees on this sort of decision making presents a very interesting challenge. So my intent is not so much to come up with a pure append only model, but its more to completely avoid updates that involve contention, avoid indexes and answer all these tough questions that a user asks of the system.

Note: You'll will still have an index for the landing pages where you display many many products by different criteria and indicate which one is sold out and which one is not. This is OK, as long as you do an actual check using a load before a user submits payment.

Below are some conventions on id generation strategies so I can bring everyone on the same page.

A sku is skus/1

An order item for skus/1 is orderitems/skus/1/

The trailing / ensures 10 of the above would give us orderitems/skus/1/1 to orderitems/skus/1/10

Note an order item is created for every sku item sold, so if a user requests for two of those, we create two documents.

Stock for skus/1 is stocks/skus/1/

The trailing slash at the end caters for creating a new stock document for every edit made to the stock, be it add or remove.

When someone requests to buy skus/1, you find total stock for skus/1

var stock = session.Advanced.LoadStartingWith<Stock>("stocks/skus/1/").Sum(x => x.Qty);

First Question: Be sure there is enough stock before collecting payment

Now to find if its sold out, say if we had 100 in stock, and say the user asks for 2, we need to find out if we have 99 or more order items, so you skip 98 and see if the 99th item exists.

session.Advanced.LoadStartingWith<OrderItem>("orderitems/skus/1/", start: stock-1-2, pageSize:1).Any();

With two loads I can 100 percent guarantee if the stock the user has requested is available or not before taking payment. And given the stock documents themselves are not created every other second, I can be smart about not loading them every time, that gives me almost only one load to get this answer.

Here in lies my first assumption. I have to purge OrderItem documents if the user chooses to quit, or payment is not successful. Otherwise the skip logic to calculate what is sold doesn't work. 

Next Question: Given we have enough stock, when a user requests to reserve some, be sure they got it ahead of some one else who could also be contesting for the same quantity.

Once we know there are two items left, we create two order item documents for our user. Now we need to ensure the user made it before everybody else. We take the id (only the integer part after the last /) of the last OrderItem created for the user and check if its less than or equal to the stock value. If so they got the stock. Move on take payment.

Otherwise next we check, if we have over sold, if there are 100 in stock check if we have more than 100 order items.

var overcommitted = session.Advanced.LoadStartingWith<OrderItem>("orderitems/skus/1/", start: stock -1).Count();

If this value is 0, the user got it. and we can take payment. Note I am only taking the count, not the actual items. This was the flaw with my original assumption. Since the ids are in lexical order, skipping 99, and getting the 100th item, only guarantees we have 100 items, but does not guarantee we got the 100th item.

So with the above if we got say 8 back, we know we over committed by 8. Now we need to ensure we can find atleast 8 more items who's ids are greater than the id of the last order item we created for the user. This is where it gets tricky.

[Lee]

Out of interest, how many concurrent users are you expecting? How often does new stock enter the system?

[afif]

Lee, I hate to say depends on the price and popularity of the product. If you do a big marketing push, and stock up 10 iPads to sell for $50 each, expect the entire internet to turn up for that sale, even before it starts. But I am more designing this for the usual, from I have a bunch of iPhone cables going for cheap, to that torn jeans is worth 500 kinda store. So perhaps 20 concurrent users for a given product, if at all.

As far as adding stock, I don't think it will be every other minute, but I don't think I can say for sure it will be once a day or once a week. I know it will happen rare, so I can rely on a map reduce to tell me how much they have stocked, and unless the disk is playing bad, or the index has gone corrupt, I should find an accurate answer pretty much 100% of the time.

[Lee]

If I am understanding correctly, isn't there going to be contention for the creating of order items? If two people order an item at the same time, one is going to get denied due to an order item document with that id already existing?

[afif]

Nope, because when creating an order item for say skus/1 the id I send down to the server looks like

orderitems/skus/1/

Raven will do the hard lifting and append a unique number at the end of that trailing slash. A number that is incrementing automatically for items in that Sku.

[Lee]

So when you say purge OrderItem documents, I assume you mean to simply delete the document?

Say you have 10 in stock and have created order items 1-10 and order item 5 gets cancelled.  When the next user comes to order 1, won't the next order item have the id of 11?  This is not less than or equal to 10 so would not get fulfilled even though there is 1 in stock?

[afif]

That's only the first check. If you read towards the end of my post, I describe what I do next once the first check doesn't pass. I basically check if more than 10 items exist. And if not, then next rule.

[afif]

Lee, read towards the end of this post.

[Lee]

I guess I would look to simplify it.

Calculate your stock in the same way.

Let stock count be S.
Let order items document count be X.
Let new order quantity desired = Q.

if (S < X + Q) then
   not enough stock to fulfill order
else
   Create order items pre-populating id so (orderitems/skus/1/X+1..orderitems/skus/1/X+Q)

When creating, there will obviously be contention so you could wrap it in a simple retry loop.  If it is successful, then you know that the quantity was available and allocated.  If someone cancels an order then don't delete the order but just add a stock document containing the cancelled quantity.

Obviously this semantically changes the 'stock count' in this instance as it will include all of the cancelled quantities too but it still serves the purpose of constraining order item creation where necessary.

Disclaimer - This is off the top of my head so it could contain glaring omissions :)