> Always pisses me off. University teaches the principles of ACID and how hard databases work to adhere to these principles, and then so called "NoSQL" comes along and says "lol we have eventual consistency".
99% of the organisations that pride themselves on using "real databases" and ACID aren't actually using those guarantees or gaining anything out of them. Transactions are inherently useless in a web service, for example, because you can't open the transaction on the client, so the part of the system where the vast majority of consistency issues happen (the client <-> server communication) will always be outside the transaction boundary. ACID fanboys love to talk about how all the big name internet companies are built on RDBMSes, and fail to mention that most of them were built on MySQL 3 which never actually had working ACID in the first place.
If MongoDB had come first and SQL/ACID RDBMSes had come after, we'd recognise them for what they are: a grossly overengineered non-solution to the wrong problem.
> Transactions are inherently useless in a web service, for example, because you can’t open the transaction on the client
(1) There is no reason you couldn’t, in a stateful, connected web app (or a stateful web service) open backend DB transactions controlled by activity on the client. You probably don’t want to, because, “ew, stateful”, and would prefer to use compensation strategies to deal with business transactions that span beyond a database transactions, but you could.
(2) The conclusion “transactions are inherently useless in a web service” does not follow from the premise “you can’t open the transaction on the client”. They are just two completely unrelated things that you’ve glued together with “because”. I write web services. The backends very often need a consistent view of state and to be able to assure that a sequence of mutating operations against the DB are applied all or nothing; transactions do both of those things. The fact that transactions are opened from the backend doesn’t make them useless.
> the part of the system where the vast majority of consistency issues happen (the client <-> server communication) will always be outside the transaction boundary.
“Consistency issues” in the ACID sense do not (cannot, in fact, since “consistency” is a property of database state) happen anywhere other than inside the database. Client <-> server communications have all kinds of issues, but ACID;s C is not one of them.
> ACID fanboys love to talk about how all the big name internet companies are built on RDBMSes
Nah, actually, as an “ACID fanboy”, I’ll say that most big name internet companies were not built on ACID systems, and that if you are running a big internet company you have a much greater than usual chance of (1) having a case with a tradeoff that really does call for a non-ACID system, (2) having to invent revolutionary new technology if it turns out you actually do need an ACID system, because OTS RDBMS’s don’t scale the way you need.
But for everyone else, you probably aren’t Google.
> If MongoDB had come first and SQL/ACID RDBMSes had come after,
While they weren’t MongoDB, specifically, non-relational, non-ACID, key-value stores where the “value” could be arbitrary data did exist before RDBMS’s. OTOH, users of MongoDB and other similar NoSQL system have often discovered that, oh yeah, they do want the things RDBMS’s provide, which is why the pendulum swung somewhat back in the RDBMS direction after peak NoSQL hype.
NoSQL has its place, too, but the relational model and ACID guarantees actually do solve real problems.
> (1) There is no reason you couldn’t, in a stateful, connected web app (or a stateful web service) open backend DB transactions controlled by activity on the client. You probably don’t want to, because, “ew, stateful”, and would prefer to use compensation strategies to deal with business transactions that span beyond a database transactions, but you could.
You'd have to break the rules of how web systems are meant to work. HTTP requests are supposed to be independent (and may arrive out of order with each other etc.), holding a long-lived connection between server and client has limited support.
> The backends very often need a consistent view of state and to be able to assure that a sequence of mutating operations against the DB are applied all or nothing;
You never need that if these things are the result of a fixed client request; the set of things to be done can't change, so eventual consistency will serve the purpose. The case where you need ACID is where there's a complex back and forth to be had with the thing that's driving the change within the same transaction - and to the extent that "the thing that's driving the change" is solely on the backend, it's not really a web system.
> “Consistency issues” in the ACID sense do not (cannot, in fact, since “consistency” is a property of database state) happen anywhere other than inside the database. Client <-> server communications have all kinds of issues, but ACID;s C is not one of them.
Small-c consistency (actually I was thinking mainly in the CAP sense). Things like requesting to make a change based on stale data.
> You never need that if these things are the result of a fixed client request; the set of things to be done can't change, so eventual consistency will serve the purpose.
Yes, if I wanted to handle orders of magnitude more simultaneous requests than this app could conceivably ever need to, but with more app complexity and greater latency for substantive results even at it's existing scale, I could use an EC solution instead of an ACID one, but...I don’t.
What are you getting out of ACID, and what is it costing you? The main practical effects of using an ACID datastore are that a) your writes don't commit until they've finished updating all your indices (sometimes what you want, but often not) b) if you get your dataflow wrong you'll get deadlocks, occasionally, probably starting weeks after you made the mistake that caused them
> Transactions are inherently useless in a web service, for example, because you can't open the transaction on the client, so the part of the system where the vast majority of consistency issues happen (the client <-> server communication) will always be outside the transaction boundary.
I think I need more help understanding this. In terms of ACID, a consistency issue is something like, for example: "The client ordered an item, and the count of item availability was reduced by 1, but no actual order was saved in the database. An item is now 'lost' with no trace of where it went and how it disappeared."
There is even another consistency hazard right in this very example: "The count of item availability was supposed to be reduced by 2 because 2 items were ordered by different clients at a same time, but a race condition (the two transactions both individually reading the same old value before writing back the new value) led to the item count only reduced by 1. There is now an extra item 'available' that does not exist."
The canonical example is bank accounts and money transfers, but I figured I'd use something that was more likely in the domain of "99% of organizations" (though that also includes things like ERP, where a lot of other canonical examples apply).
Care to elaborate how transactions are "inherently useless" in a web service here, and how the consistency issue happens "between client and server" communication?
Presumably, in this example, the client/server communication triggering the transaction is the order of an item. There are not that many things that can realistically go wrong in that communication (with checksums on different layers, and nowadays even cryptography, guarding against most arbitrary corruption): The order was not received, or the order was received multiple times. In both those cases, the database remains internally consistent: Yes, the order happening 0 times or 2 times may have been wrong, but the database overall shows the state of what you would expect after 0 or 2 orders. No item has been going inexplicably "missing" or "created" until the next inventory, and the external issue can just be corrected by reissuing or canceling the orders, again with the prospect of an internally consistent database. And even a corrupted order would not change that.
> In terms of ACID, a consistency issue is something like, for example: "The client ordered an item, and the count of item availability was reduced by 1, but no actual order was saved in the database. An item is now 'lost' with no trace of where it went and how it disappeared."
> There is even another consistency hazard right in this very example: "The count of item availability was supposed to be reduced by 2 because 2 items were ordered by different clients at a same time, but a race condition (the two transactions both individually reading the same old value before writing back the new value) led to the item count only reduced by 1. There is now an extra item 'available' that does not exist."
You don't need ACID to solve that kind of problem though; event sourcing and eventual consistency handles it fine. What you need ACID for is synchronous transactions; for example you want to check that both item A and item B are available, order both of them, and have it either both orders go through or neither of them, even though those are separate orders to separate tables (of course if you have a concept of an "order" as a first-class entity then you can trivially solve this - but again, in that case you don't need ACID at all).
> Care to elaborate how transactions are "inherently useless" in a web service here, and how the consistency issue happens "between client and server" communication?
So the example above - the whole point of ACID is to let you do two separate things at once, e.g. you want to open the page for item A and for item B, check that both are available, and then order them both on their respective pages. With an old-fashioned synchronous client-server system with a dedicated fat client you could do that, but on a web system obviously it's not possible (other than by creating some kind of "batched request" or "combined order" concept for doing both orders in the same request - but again, once you've done that you don't need ACID at all) because there's no way for the client to do two page loads in the same transaction.
How can eventual consistency handle the inventory use case as well as an ACID system? You can't tell the user "your order is placed successfully" and then later tell them "actually no, it was out of stock".
That's a different problem from your previous examples, and it's the same problem whether it's ACID or not: there's always an opportunity for the user to see the item as being in stock, then request to order it, and discover it's out of stock. Your "recovery" from the case where there's nothing in stock doesn't need to happen in a transaction (and can't meaningfully do so); you just tell the user their order failed, and again you do that the same way whether it's ACID or not.
I see what you mean, but with ACID you can tell the user right away if the order was successfully placed. With eventual consistency you don't know how long it'll take so you either show them a loading spinner that takes an indeterminate amount of time, or what?
Not saying that the spinner solution is wrong (as it definitely scales better), but the immediate feedback is something you lose with eventual consistency, by definition, right?
> I see what you mean, but with ACID you can tell the user right away if the order was successfully placed. With eventual consistency you don't know how long it'll take so you either show them a loading spinner that takes an indeterminate amount of time, or what?
It's doing the same thing either way though; in either world you can even wait for the order to be processed, or not. In an event sourcing system you'll commit the event quickly and then wait potentially arbitrarily long for the result to appear downstream; in an ACID RDBMS you'll wait potentially arbitrarily long for your commit to execute (and maybe if you're lucky your database has got a deadlock detector, but what are you going to if it tells you you hit a deadlock? 100% of the time I've seen the answer is "backoff and retry").
> in an ACID RDBMS you'll wait potentially arbitrarily long for your commit to execute (and maybe if you're lucky your database has got a deadlock detector, but what are you going to if it tells you you hit a deadlock? 100% of the time I've seen the answer is "backoff and retry").
I don't agree with the conclusions from this. re: deadlocks, these can be prevented as they are only possible in certain situations, and have mitigations (keep transactions short, never acquire the same locks in a different order, ...). For something "simple" like atomically decrementing an inventory count and then inserting a new row into an orders table, deadlocks (or locking problems at all) are not possible. Of course as you make your system more complex it becomes more likely though, and that's a very fair argument of why ACID systems won't scale as well in general.
But I would still maintain that, even if you have a commit that times out, it does so in an atomic way. It takes at most your "statement timeout" (should be a few seconds probably), and then you can (in deterministic time) show the user an error message. This is still an improvement for the user experience over showing a "your order has been placed" message and then later cancelling it due to overconsumption of inventory.
I appreciate your replies by the way! I haven't been convinced yet that eventual consistency can provide an equally good experience as ACID for this use case, but you've made me think about things in a new way.
> re: deadlocks, these can be prevented as they are only possible in certain situations, and have mitigations (keep transactions short, never acquire the same locks in a different order, ...).
In principle yes, but this relies on human vigilance; as far as I know there's no automatic checker that can reliably tell you whether your queries have the possibility of deadlocking. Do you review every query before it gets run? And when you miss a case like acquiring locks in the wrong order, it can be weeks or months before it actually bites you.
> But I would still maintain that, even if you have a commit that times out, it does so in an atomic way. It takes at most your "statement timeout" (should be a few seconds probably), and then you can (in deterministic time) show the user an error message. This is still an improvement for the user experience over showing a "your order has been placed" message and then later cancelling it due to overconsumption of inventory.
One of the most fun ways I've seen an SQL system break: user navigates to a page, gets a timeout in their browser; 23 days later the database falls over.
(the page initiated a query for 2 years' worth of data, the database server chugged away through its indices for 23 days and then started trying to stream all the data back).
I agree that it's good to have that kind of fallback behaviour - in the system I currently work on we have something like that, where if a complex process doesn't get an event for over 1 second (most likely because the thing computing it broke, but it could also just be slow) then we have a simple consumer downstream that just emits a cancel event in that case (and passes everything else through otherwise) and we take that stream as canonical. And having something like that by default is a good thing, and one of the things that SQL databases do right is that they're a lot more request-response, whereas event sourcing things can be a bit "shouting into the void". I'd like a system with better support for that kind of case. That said, I think in a lot of cases the SQL defaults aren't great - I don't think I've ever used a database that had a good default timeout setting, and the way SQL databases treat any validation failure as "drop the data on the floor" is rarely what you want in practice.
There is a difference between theoretical worst case and practical experience. Whether the direct-update experience or the event sourcing experience on this point is better depends on a lot of factors (many of which are dimensions of scale.) Neither is categorically ideal, you've got to understand the particular application.
Of course, when using an ACID RDBMS, you can also very trivially use a CQRS/event-sourcing approach for some flows with append-only event log tables and a separate process applying them to query tables that other clients only read and direct update for other flows.
> when using an ACID RDBMS, you can also very trivially use a CQRS/event-sourcing approach for some flows with append-only event log tables and a separate process applying them to query tables that other clients only read and direct update for other flows.
Sort of. You can't opt out of transactionality (transaction isolation settings are often global) and you can't defer index updates except by making your indices "manual".
The first job I worked for essentially built an event sourcing system on top of an RDBMS. We still had the production system brought down by an analyst leaving a transaction open for weeks (we weren't big enough to have a separate analytics datastore at that point). They should've known to enable autocommit (even though their queries were read only!), but it's still a footgun that was only there because of the ACID RDBMS.
Im no expert but my recent conclusion from writing an ordering system is customers can instruct their banks to roll back many payments a month or even a year after the fact
So actual ACID transactional consistency requires a year. So after basic checks just say you'll give them what they want and accept payment -- since you'll need failure recovery anyway for lack of delivery, refunds, acts of God etc
The idea I've seen repeated here a lot is to aim for strong consistency internal to your system, even though the real world/things external to your system will be eventually consistent.
So the inventory <-> create order interactions are internal and can be strongly consistent, while the realities of the banking system are external and will be eventually consistent.
Just because you can't make everything strongly consistent doesn't mean you shouldn't bother at all; it can still eliminate entire classes of errors (in this case, errors around inventory being over-consumed leading to cancelled orders are completely avoided with ACID)
edit: I'm also no expert when it comes to distributed systems. Most of my experience is with transactional systems which I know quite well, and distributed is still a mystery to me. I am very open to new ideas around it and don't have a ton of confidence in my comments here.
A lot of things have improved in distributed databases as of late. There are distributed databases today (my co. provides one) that can have strict serializability (the highest data integrity level) for hyper scale applications. Tolerating "eventual consistency" is largely a choice, oftentimes forced because your DB application or implementation (NoSQL and traditional RDBMS, depending on their implementation) does not provide you strict transaction guarantees. There are third parties (https://jepsen.io/analyses is a great source) that can evaluate and validate such claims. A lot of the anecdotal information in this thread here is no longer accurate or applicable. Yes, there are databases (like Fauna) that don't compromise on the highest transactional levels, and yes, there are more widely used databases (MongoDB, DynamoDB, and many others) that either cannot make those guarantees, or their implementation by their service provider cannot demonstrate that guarantee. Happy to provide more info if interested.
> the part of the system where the vast majority of consistency issues happen
This is survivorship bias: the reason the vast majority of consistency issues happen between the client and the server is _because_ the server can eliminate issues on its side with transactions and similar.
No, you're inherently a lot more likely to get inconsistency over the higher-latency and less-reliable public internet that between the server application and database that are probably sitting in the same rack, if not on the same machine.
99% of the organisations that pride themselves on using "real databases" and ACID aren't actually using those guarantees or gaining anything out of them. Transactions are inherently useless in a web service, for example, because you can't open the transaction on the client, so the part of the system where the vast majority of consistency issues happen (the client <-> server communication) will always be outside the transaction boundary. ACID fanboys love to talk about how all the big name internet companies are built on RDBMSes, and fail to mention that most of them were built on MySQL 3 which never actually had working ACID in the first place.
If MongoDB had come first and SQL/ACID RDBMSes had come after, we'd recognise them for what they are: a grossly overengineered non-solution to the wrong problem.