> The typical example for ACID DBs are bank transactions; imagine a bank accidentally undoing a transaction upon server crash
That's why they don't try to do it that way! But it's still an informative way to think about it.
Also, while we're discussing defaults, your ACID db is probably running at READ COMMITTED by default, meaning that your bank transactions are vanishing/creating money:
* You read accounts A and B($30) in order to move $5 between them. The new balance for B should be $35. Just before you write the $35, someone else's transaction sets B to $100. Your transaction will proceed and blindly set it to $35 anyway.
But to your overall point, I'm also frustrated that these systems aren't as safe as they look on the box.
> your ACID db is probably running at READ COMMITTED by default
You're probably refering to PostgreSQL. Yes, I am also frustrated that that doesn't default to SERIALIZABLE.
I do wish the top two open-source "ACID" DBs (Postgres and SQLite) used guaranteed-safe, zero-surprise defaults.
It isn’t worth it. Mostly financial transactions are done via append only ledgers, not updating; two phase auth and then capture; a settlement process to actually move money, and a reconciliation process to check all the accounts and totals. Even without DB corruptions they have enough problems (fraud and buggy code) with creating money and having to go back and ask people to more money or to give them more money so they have those systems in place any ways.
> Mostly financial transactions are done via append only ledgers, not updating;
Well, financial institutions will act as you describe, I presume, but lowly web shops will update 'shopping cart' and 'inventory' using the default settings of whatever DBMS the system came with.
Which is reasonable I guess, except that even with modern hardware, updating the same record in an ACID database has a surprising low capacity in terms of txn / second; if you have some popular item in your inventory (or popular merchant in your 2-sided shopping thing), you'll be forced to create multiple receiving accounts or other ugly stuff if you do the balance based transactionality.
yes. most folks don't seem to understand this. but, you can get something approaching such guarantees if you are able to limit yourself to something as (seemingly) simple as updating a ledger. this approach is used in a lot of places where high performance and strong consistency is needed (see e.g. LMAX disruptor for similar). https://tigerbeetle.com/
SERIALIZABLE isn't zero-surprise, since applications must be prepared to retry transactions under that isolation level. There is no silver bullet here.
A CRUD architecture with proper ACID is an OK contender against other possible architectures. Personally I always go for event-sourcing (a.k.a. WAL per the article's title).
But a CRUD that doesn't do ACID properly is crap. And since the people making these decisions don't understand event-sourcing or that they're not being protected by ACID, CRUD gets chosen every time.
The DB also won't be set to SERIALIZABLE because it's too slow.