The problem of lying in databases

I (isolation) in ACID:

If multiple transactions are running at the same time, how do they see each other’s data?

Even during transactions, the database can lie to you, due to these edge cases:

• Phantom reads: Seeing new rows added in range queries between two reads.
• Dirty reads: Reading uncommitted data.
• Non-Repeatable reads: Repeated reads do not have the same data.
• Write Skew: Two concurrent transaction working on the same set of data, and upon write leaves the data in an inconsistent state.

To tackle it, database transactions can be run in these isolation levels:

1. Read uncommitted
2. Read committed (postgres default)
3. Repeatable read
4. Serializeable (strongest of all, also the slowest)

On the order of strong consistency (weakest to the strongest, on default configurations):

Redis < Cassandra < Oracle | MongoDB | Postgres | MySQL << CockroachDB | AWS Aurora | Google Spanner | SQL Server (mostly enabled) | Materialize (article author)

Note: Postgres & AWS Aurora (also built over postgres) supports true serializeability - but aws has a performance patches. CockroachDB uses postgres too, but they copy data extensively across nodes

site: https://materialize.com/blog/engineering/keeping-track-lies/
discussion: https://news.ycombinator.com/item?id=40602603