Conversation

I trust the author, so this probably isn't an April 1 post. But the claim is with Oracle: 1) trx1 writes a row, calls commit 2) trx2 reads that row, sees trx1 3) primary crashes & recovers, trx1 undone 4) trx2 re-reads that row, doesn't see trx1

Quote Tweet

Franck Pachot

@FranckPachot

Oct 21

A new blog post: Dirty Reads in @OracleDatabase (is Oracle ACID across failure?) dev.to/yugabyte/dirty

Mark Callaghan

@MarkCallaghanDB

Oct 21

The reason is that the commit is made visible before it is made durable in redo. Am I missing something? Otherwise, this isn't a feature and it is funny that mighty Oracle would do this. You can configure MySQL to not force redo/WAL for InnoDB/MyRocks on commit to boost ...

Peter Geoghegan

@petervgeoghegan

Replying to

@MarkCallaghanDB

I don't think that PostgreSQL has this problem (if it does

@FranckPachot

should report it as a bug). Separately, I don't see why the blog post links the problem to distributed consensus. Is no single node database capable of providing atomicity these days?

5:11 PM · Oct 21, 2022Twitter Web App

Replying to

and

I would be surprised if PG had this "feature". The real feature that PG has is the option to set synchronous_commit=off, in which case you are explicitly allowing for commits to disappear assuming a unexpected server restart.

postgresql.org

20.5. Write Ahead Log

20.5. Write Ahead Log 20.5.1. Settings 20.5.2. Checkpoints 20.5.3. Archiving 20.5.4. Recovery 20.5.5. Archive Recovery 20.5.6. Recovery Target For additional information …

Replying to

and

If true, the window for this occurring with Oracle is much larger (DMBS process crash/recovery) then the window for losing commits with sync_commit=off (kernel panic or power fail).

Replying to

and

OK will think about it. How to provide atomicity between update in memory, local disk write, remote write, in a scalable way?

Replying to

and

Are you talking about the database, or the client and the database as an atomic unit? If so, then I don't see the connection to the Oracle bug. That is essentially a problem with durability guarantees not being met. Very much not like issue from aphyr.com/posts/282-jeps.

Replying to

and

Right PG doesn't have this because it writes WAL before marking visible. But the standby commits before the primary and can show a change that will be rolled back in case of primary crash (without failover).

Replying to

and

Standby commit occurs after the primary local one: firstly primary locally commits then walsender sends commit record to standby. In case of sync replication - yes, standby might show change earlier (after its commit applying) but primary anyway recovers this change after crash.

Show replies