
Inventory management in MongoDB: A design philosophy I find baffling - douche
https://ayende.com/blog/178785/inventory-management-in-mongodb-a-design-philosophy-i-find-baffling?Key=e9d8f617-cd97-4328-a380-e30e30c4f450
======
ojosilva
Just to make it clear, the issue the OP is pointing out is with whoever wrote
this inexcusable piece of code in the book, not MongoDB itself. Even if the
authors later clarify there's the possibility of error, just publishing such
misleading and grotesque solution for creating transactions in a non ACID
database is very poor judgement.

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...](https://docs.mongodb.com/manual/tutorial/model-data-for-atomic-
operations/)

~~~
tyingq
In the case of an ecommerce site, where multiple products can go in a cart, I
don't see any real right way to do things with MongoDB. Atomic for one row
just doesn't help much, and you can't nest arbitrary cart product mixes.

As you say, bad idea in the first place.

~~~
electricEmu
I was part of a team that successfully did inventory in mongo. The cart makes
zero difference if Mongo is only storing the inventory. It's fine to
atomically change a single inventory row.

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.

~~~
tyingq
I am assuming a system where available inventory is debited when the CC
transaction completes.

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?

~~~
ryanworl
And what happens when that shovel gets stolen from the warehouse before it can
be shipped to the customer?

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.

~~~
tyingq
Yes, you have to handle overselling for other reasons, but I don't see why
that's a good reason to not protect against it when you can.

------
weddpros
Regarding "SQL is better suited to this use case because it has transactions"
comments:

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.

~~~
elcritch
Well put! Having worked on corporate cash accounting and inventory systems,
it's pretty clear that accounting is handled by banking systems _does_ not
rely on SQL-transactions. It's handled by auditing and transaction based
system (e.g. event systems) encoded in double entry accounting. ATM's for
example will generally dispense money up to a given limit (say $500) as the
cost of lost consistency is outweighed under those limits by providing high
availability. If the user double draws and goes over their limit, they are
held accountable at a later point (usually this is bounded by hard cash
transfer limits).

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.

~~~
calafrax
Transactions are never needed with a fully normalized model so if transactions
are needed it is probably because your model sucks.

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.

~~~
pjc50
Eh? If you don't have transactions of some sort you can't update multiple
tables simultaneously, which you need for a denormalised model?

~~~
calafrax
If your schema is fully normalized then you never have two instances of the
same data in more than one place therefore you never have to update two things
simultaneously.

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.

------
moxious
New families of DB technologies generally traded certain things off (like ACID
guarantees and transactions) in exchange for other things like scalability or
flexibility. When someone comes back, and in user space reimplements the
things that the DB intentionally traded off, you get the worst of all worlds.
It's quite a bit like flattening one end of a screwdriver so that you can make
it work to drive nails. Yes, you can make that work, and in some rare
circumstances where you're trapped on a desert island that might be your only
option.

The rest of us will just use a hammer.

------
nwatson
My prior startup tried to do a security product with backend in Mongo. It
really needed transactions and to avoid N+1 issues.

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.

~~~
united893
> DB team insisted on writing "DAOs" that ended up pulling 1GB+ of data

That DB team shouldn't be allowed near any database. Why on earth would they
go for such a moronic abstraction?

------
binocarlos
I remember writing documentation for a Perl system in 2001 - it was using
MySQL MyISAM tables and the main developer had a few hundred thousand lines of
Perl that acted as the same kind of client attempt at transactions. It was a
mess and huge amounts of money were spent on trying to get the thing to work.
A few months later InnoDB came along and made it apparent that trying to write
transaction logic in the client was a very bad idea, which seems to be the
point of this article.

~~~
tacostakohashi
That was a terrible time in history - lots of Perl web apps built using MySQL
or mSQL, both of which lacked transactions, right at a time when e-commerce
was taking off.

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.

~~~
gaius
_That was a terrible time in history - lots of Perl web apps built using MySQL
or mSQL_

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.

------
united893
Should have a disclaimer, founder is the founder of RavenDB and it's clear
he's cherry picking things and blaming it on the database vendor, instead of
whomever wrote that example.

~~~
icebraining
Where did he blame it on the database vendor?

------
valarauca1
TLDR:

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.

------
twothamendment
Thanks for bringing up nightmares. Inventory on the web is one thing, think
about how you'd do this for inventory in person at the store when a customer
has product A in their hand and cash in the other. The inventory system says
there aren't any in stock - should you sell them one? Of course.

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.

------
mindcrash
Particularly fitting comment:

"Mongodb, the ultimate Maybe monad. With a built in fromMaybe mempty call for
your convenience."

Per Hmemcpy and Michael Snoyman on Twitter.

~~~
PeCaN
Makes it great for building Snapchat clones though!

------
bsg75
Did I read that right? A document per item in inventory? This seems horribly
inefficient.

~~~
lilbobbytables
As someone not versed in Mongo or anything similar, what is the alternative? A
large array of docs that has to be loaded in to memory to work with?

~~~
pjungwir
From the article:

    
    
        > 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.
    

In other words there are 10 documents in Mongo, not 1 document with a
`"quantity": 10` attribute.

~~~
codedokode
Imagine that every of your items in a warehouse has a unique barcode to track
it. In this case you have to keep a record in a database for every item.

And with current amounts of RAM on servers there would be no problems even if
you have tens of millions of items.

~~~
icebraining
_Imagine that every of your items in a warehouse has a unique barcode to track
it. In this case you have to keep a record in a database for every item._

You could have a list inside a single document.

~~~
codedokode
And what about tracking item status and location? Another list?

~~~
icebraining
Lists can contain items with complex structures, not just strings.

~~~
codedokode
Then it would be difficult to add a reference to a specific items somewhere
else (or one would even have to duplicate the data). The benefits and
disadvantages of normalization vs denormalization are long known and this
(denormalization) is possible with classic SQL DBMS too.

------
codedokode
They should not try to emulate SQL databases, there are other ways to manage
inventory without transactions.

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.

~~~
elmigranto
> Then adding an item to a cart means updating those fields. There probably is
> a way to do several similar updates atomically.

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).

------
russdpale
MongoDB has purpose, but inventory management is _not_ one of those purposes.

~~~
camus2
> MongoDB has purpose, but inventory management is not one of those purposes.

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.

~~~
weddpros
"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.

~~~
tscs37
CockroachDB called, problem solved for SQL with ACID.

Replication and Sharding are easy.

~~~
jackweirdy
CockroachDB reached 1.0 less than 2 months ago. I'm not sure what your domain
is, but personally I would be very hesitant to go with a database which is so
new

~~~
elmigranto
MongoDB escaped being glorified /dev/null just last fall.

[https://jepsen.io/analyses/mongodb-3-4-0-rc3](https://jepsen.io/analyses/mongodb-3-4-0-rc3)

~~~
jackweirdy
Exactly, this is an argument in waiting for the real-world behaviour of the
database to be well understood

