> 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.
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?
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
So accept and plan for failure
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.
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").
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.
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.
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.
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.
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.