Inventory management in MongoDB: A design philosophy I find baffling
ayende.com
ayende.com
MongoDB should not be used for an online ordering system, period. But if the programmer had no better alternative than Mongo, then please use Mongo's atomic operations [1] and nested documents to make sure nasty Bad Things don't happen.
[1] https://docs.mongodb.com/manual/tutorial/model-data-for-atom...
As you say, bad idea in the first place.
Operations that span multiple rows can be safely performed with a bus in front of it.
You can't judge the idea. I don't think you've quite grasped it. I agree the book example isn't great, but that's not the technology's fault.
How do you do that with single row only atomic transactions? And without charging $100 for what the customer thought was qty 3 $25 rakes and qty 1 $25 shovel...but is now something less than that due to concurrent purchases from other customers?
You have to handle over-selling no matter what. The "source-of-truth" of you inventory system is whatever is actually, physically sitting in a warehouse somewhere. Not what your database says.
In this scheme, you should theoretically be able to implement transactional protocols (two phase commit and friends, see, e.g., http://dl.acm.org/citation.cfm?id=3027893) that ensure you do not actually sell two people the same thing, but may return an error at buy time that the inventory disappeared while you tried to buy it.
You pay a sometimes-very-high cost in throughput.
Of course, talking about mongodb, it's probably broken in a myriad of ways no matter what you do :)
I'd like two of this product? Increment reserved in Mongo atomically, fire a bus message that inventory was reserved. I'd now like just one? Again atomically remove one from reserved and fire another message. Simplified of course.
Two users can't reserve the same inventory, reserving the inventory is atomic, and everyone else occurs during reconciliation. Typical distributed systems.
So you can't have exactly once semantics. When you say 'bus' I assume you mean a message broker which at best gives you at least once semantics. What happens when the message is delivered multiple times?
To answer that question more succinctly, nothing special happens when a message is delivered more than once.
Those were fun days.
Yes, sure, let's go all get PHDs in electronics, networking protocols, IPC and database architecture, so we can write reliable transactions for our webshop/CRM. Or I don't know, maybe use mature system multiple decades in production.
Before we had 3-tier architectures, people would have designed a shopping cart use-case as a single SQL transaction that would last maybe 10 minutes. The DB would make sure everything stays consistent until the final commit. The GUI would keep an open connection to the DB the whole time.
In the web age, you want stateless services and HA. It means a transaction can't last more than a single web page. It becomes more challenging to design a shopping cart, because the DB can't handle a long-running transaction anymore.
Writing a correct system that reserves the items you put in a shopping cart and doesn't leak items and doesn't sell the same item twice is not easy. A transaction Rollback will not do the cleanup for you, because there's no long running transaction anymore.
So SQL transactions can't help as much as you think.
Mongodb doesn't have transactions, but updates are atomic, which allows CAS and optimistic locking use cases. I agree it's less than ideal when you need to provide ACID behavior, but don't believe it's easy with SQL transactions. It's not.
The author regrets the book's suggestion of putting each object in stock in its own document, and I agree it's probably a recipe for disaster. Atomic updates make this design absurd.
You could easily db.products.update({_id: productId}, {$inc: {inStock: -5}, $addToSet: {pendingCarts: {cartId: cartId, quantity: 5, timestamp: new Date()}}}). This has the exact same atomic behavior as a SQL transaction to remove 5 from the stock and add a new "shopping cart entry" in another table.
(you still need to expire cancelled shopping carts, and you may need a transactional way of completing the order: it's also manageable if designed as an idempotent operation)
Anyway don't over-simplify this use case and believe "a single big SQL ACID transaction would handle the problem". That's just not true.
This problem was solved in 1965 by CICS for the use case of "you're on the phone to a travel agent and they're finding you a ticket on their terminal". No "10 minute single transactions" anywhere...
In the web age, you want stateless services and HA
Those who forget history are doomed to repeated it.
A service bus was necessary, but the actual atomic transactions in MongoDB didn't fail us. We didn't lose data. While the nay-sayers discounted Mongo, we were raking in cash on top of it.
In a warehouse inventory setting, when you _do_ have inconsistencies (e.g. lost items, misplaced orders, etc), a system which strictly enforces "inventory limits" will as often prevent employees from doing their job and shipping an item which could be sitting right in front on them but is not counted for in the system. Auditing combined with optimistic locking resolves this and allows both accountability, tracking, and flexibility.
Those are two real world examples which underly the idea that ACID guarantees and locking / transactions are two separate intents. CouchDB & Couchbase both provide ACID guarantees per document making it straightforward to implement multi-service applications using event base systems. It's equivalent to MongoDB's CAS operations. Really all that you need is to ensure that your changes are atomic and generally ACID compliance at a key/document level enables you to do this readily.
Personally, I find that SQL-style transactions just cause lots of issues with performance and locking contention while enabling developers to skimp on thinking deeply about how to appropriately design their data flow. Sometimes that's the right call for a team, but sometimes it's not.
Or it is because you denormalized your model because your db engine's performance sucks in which case the transactions will probably just make it worse.
Good schema design and lock-free/wait-free (transaction-free) algorithms are not "reimplementing transactions in the client."
OPs example is garbage but his proposed transaction solution is garbage too.
Anytime you need a transaction it is because you have the same data, or some calculated derivative of the same data, stored in different places, which is why they both have to be updated at the same time to maintain consistency.
Re: pjc50, yes that's what calafrax means.
"you denormalized your model because your db engine's performance sucks": you're most likely to denormalize because joins are costly no matter what DB you're using.
"the transactions will probably just make it worse": denormalization is most often used to reduce the number of operations (so transactions are unlikely to make things worse).
I agree about the amount of garbage in the article though.
"Designing data flow" would mean having to spend more time and money for the same task?
That is unlikely to work well at much scale. At least last I knew, Mongo docs are limited to 16MB and the entire doc is read then written in cases like this, very slow on large docs. Given the amount of data that may be attached to a `product`, it's not hard to hit these limits.
> In the web age, you want stateless services and HA. It means a transaction can't last more than a single web page. It becomes more challenging to design a shopping cart, because the DB can't handle a long-running transaction anymore.
Or you could cheat and not update the inventory until the purchase is made. ;)
The problem with the "adjust inventory on cart" in low inventory situations is you'll have 80% of your carts holding items that won't convert until a cart expiration. You only need the actual purchase to be atomic. Then, once the queued credit card transaction completes you adjust the order to refund the inventory [declined] or ship the order [completed].
That pattern absolves you of needing complex logic and allows you to distribute the activity relatively trivially as a set of two independent idempotent operations. And if the analog portion of the process fails, the picker hits a button and the order gets queued for a refund. Once the order is cancelled, another service contacts the customer.
Cart expiration, etc. makes the system unnaturally brittle by adding non-critical steps to the process.
So I wanted to say that denormalization and lack of foreign keys in MongoDB is much worse that lack of transactions.
The rest of us will just use a hammer.
DB team insisted on writing "DAOs" that ended up pulling 1GB+ of data back from Mongo to merge in EACH of 100+ data points from a scanned machine. Similar issues in UI presentation. With multiple threads doing each of these things simultaneously there were many out of memory dumps. I analyzed these multiple times and told the DK VP Engineering what the problems were, and they didn't follow up for 6 months. He was gone soon after.
That DB team shouldn't be allowed near any database. Why on earth would they go for such a moronic abstraction?
Although Oracle, Sybase, SQL server and friends all transactions at that time, somehow the the mindset was that it was a complicated enterprise marketing gimmick, MySQL/mSQL are faster and simpler, and we can work around it in the client side. Seems like not much has changed.
It was made worse by the MySQL team actively advocating against features they didn't have "you don't need transactions, do it in your application", "you don't need foreign keys, do it in your application" blah blah.
20+ years later they're still struggling to shoehorn it in.
Using a database that doesn't offer ACID, in a manner that requires ACID has non-trivial associated costs. This may also leave you open to a number of strange situations, where inventory quantities are unknown or incorrect.
Are you tracking individual lots of inventory and the costs you paid for them? Against which lot did you sell this one? You don't have any - so how do you calculate the margin for this item you sold but don't know how much it cost or where it came from? If it is returned, do you restock that inventory?
Mongo, SQL - they have there differences, but doing inventory management is tricky no matter what technology you use.
"Mongodb, the ultimate Maybe monad. With a built in fromMaybe mempty call for your convenience."
Per Hmemcpy and Michael Snoyman on Twitter.
> The example is that if you have 10 rakes in the stores,
> you can only sell 10 rakes. The approach that is taken
> is quite nice, by simulating the notion of having a
> document per each of the rakes in the store and allowing
> users to place them in their cart.
And with current amounts of RAM on servers there would be no problems even if you have tens of millions of items.
You could have a list inside a single document.
One would subtract product from amout field in products table to set new inventory level. A new order document gets created with all information needed to describe an order. Fields like total, subtotal, date would sit at the root level and line items with product descriptions and prices would be embedded as an array of objects. Then a user object with user_id, name address would be embedded.
Dealing with documents is a different but being able to contain the entire dataset with some relations is nice.
One way is to add a field to an item that show its status: whether it is in a warehouse, in someone's cart, ordered or sold. Then adding an item to a cart means updating those fields. There probably is a way to do several similar updates atomically.
Another way is to use append-only collection, that keeps a list of events, like "Item X added to cart Y", "Item X sent to delivery".
But I guess when there are more entities and relations this would become too complex to manage. While SQL databases have no problems with hundreds of tables and thousands of columns.
Atomicity is document level, not collection level. So you can't update multiple documents atomically. Or do you plan on having `{status: 'in-cart', cartOwner: 'customer-id' | null}` and single document for every stocked item (like 1k copies of the same book would be 1k db documents and you also have all those sold from before)?
> Another way is to use append-only collection
How does it help with overselling? To decide if it's okay to append, you have to know if current number of items is greater than 0 (don't forget to lock other clients out of appending this whole process, so they wait for you to finish).
The truth is that MongoDB is well suited to a fairly narrow set of use-cases, but ends up being used for all sorts of stuff in practice. Hence the weird contortions which the author observes in the book they're reading.
What purpose does it have frankly? The only one I see might be GridFS for what it is worth, though I don't believe one second the performances are that great, but when it comes to document oriented DB, Postgres can store both JSON and XML and query them and also do partial atomic changes. Scaling? easier maybe... Now competition is good and I'm sure NoSQL db success kind of forced traditional players to innovate. But I see no reason to use MongoDB in 2017.
I would reply: replica sets and sharding and multi-threaded architecture and the absence of impedance mismatch, all in a single product that was designed for these features.
I did and it is a complete disaster in MongoDB.
MongoDB also has some very large customers using it so clearly it isn't a complete disaster. I would chose to use it over PostgreSQL any day of the week (not that there is a clear option regardless).
If you don't care about data integrity, transactions and efficiency, then by all means. But in that case, a distributed file system is as efficient.
> multi-threaded architecture
I fail to see how a multi-threaded architecture is a problem with Postgres.
> the absence of impedance mismatch
Postgres supports JSON type(and XML), so no.
> I fail to see how a multi-threaded architecture is a problem with Postgres.
While it's not a fundamental issue, it does have some considerable drawbacks. A lot of the work required to make query processing use multiple cores (9.6, and the upcoming 10 release), would've been a lot easier if there were a shared memory space. There's no portable way to guarantee that shared memory that's allocated after process start can be mapped to the same addresses in different processes, so you've to deal with a relative addressing etc, which both complicates and slows down.
There's also upsides, don't get me wrong. Primarily around robustness in case of failures, but also some around resource isolation.
Replication and Sharding are easy.
MongoDB is a document database. If you use CockroachDB you lose this capability.