Realistically your DB of choice will probably try and tackle ACID half-heartedly because it's what devs expect, and that will lull you into the same false sense of security.
Many "eventually consistent" DBs will still have some concept that uses the word "transaction" that works like one until it doesn't (see DynamoDB and multi-region gotchas)
If you are new to the subject there are extensive reports available from Jepsten, one of the leading names in distributed system and ACID anomaly testing and research.
Which is not the default one.
> There is no isolation, your transaction will fail and be rolled back.
How so? Isn't it more likely you'll silently corrupt your data?
Even if it does fail noisily, this is a condition you're unlikely to hit in testing, so you'll probably be dealing with it in production.