6 ms·

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 shoppi

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.

electricEmu 9y ago
This is very close to how a team I was on solved this issue at Amazon. We took money, held inventory, had a shopping cart, and it worked out fine. 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.
gaius 9y ago
people would have designed a shopping cart use-case as a single SQL transaction that would last maybe 10 minutes 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.
- weddpros 9y ago
  I thought about CICS too, but I guess few even know what it is. SQL was the contender here...
pjc50 9y ago
You don't leave the transaction open while the user browses; you check for stock at the start, possibly moving items from "in stock" to "in cart" state. And then do the actual transaction for stock->sold at the time before you send off to the payment processor. If it's rejected, you return it to stock.
elcritch 9y ago
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 9y ago
  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 9y ago
    Eh? If you don't have transactions of some sort you can't update multiple tables simultaneously, which you need for a denormalised model?
mbell 9y ago
> You could easily db.products.update({_id: productId}, {$inc: {inStock: -5}, $addToSet: {pendingCarts: {cartId: cartId, quantity: 5, timestamp: new Date()}}}). 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.
- weddpros 9y ago
  Please do the math... Before this document reaches 16MB, you're bigger bigger than Amazon. If this solution scales up to Amazon scale, that's good enough for me.
fweespeech 9y ago
> 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. > 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.
codedokode 9y ago
The example in the book is very simple - it can be implemented in SQL database with 2 tables (products and carts). When you have more entities and relations it would become too complex to keep all of them consistent in a denormalized scheme in MongoDB. You will have to write cron jobs that would cleanup broken references and still get errors. So I wanted to say that denormalization and lack of foreign keys in MongoDB is much worse that lack of transactions.

electricEmu 9y ago

This is very close to how a team I was on solved this issue at Amazon. We took money, held inventory, had a shopping cart, and it worked out fine. 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.

gaius 9y ago

people would have designed a shopping cart use-case as a single SQL transaction that would last maybe 10 minutes 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.

weddpros 9y ago
I thought about CICS too, but I guess few even know what it is. SQL was the contender here...

pjc50 9y ago

You don't leave the transaction open while the user browses; you check for stock at the start, possibly moving items from "in stock" to "in cart" state. And then do the actual transaction for stock->sold at the time before you send off to the payment processor. If it's rejected, you return it to stock.

elcritch 9y ago

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 9y ago
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 9y ago
  Eh? If you don't have transactions of some sort you can't update multiple tables simultaneously, which you need for a denormalised model?

mbell 9y ago

> You could easily db.products.update({_id: productId}, {$inc: {inStock: -5}, $addToSet: {pendingCarts: {cartId: cartId, quantity: 5, timestamp: new Date()}}}). 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.

weddpros 9y ago
Please do the math... Before this document reaches 16MB, you're bigger bigger than Amazon. If this solution scales up to Amazon scale, that's good enough for me.

fweespeech 9y ago

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

codedokode 9y ago

The example in the book is very simple - it can be implemented in SQL database with 2 tables (products and carts). When you have more entities and relations it would become too complex to keep all of them consistent in a denormalized scheme in MongoDB. You will have to write cron jobs that would cleanup broken references and still get errors. So I wanted to say that denormalization and lack of foreign keys in MongoDB is much worse that lack of transactions.