Tom Kyte wrote about "write consistency" in his "Expert Database Architecture" book. In Tom's book, transaction is restarted when it finds changed query results after concurrent transaction completes. Granted, Tom wasn't writing about PostgreSQL compatible databases as he used to be a VP at Oracle Corp. but I imagine that PgSQL has similar concurrency mechanisms. It would be very interesting to see how would that operate in a DSQL environment.
Yes, write consistency is tricky in Multi-Version Concurrency Control databases. Tom Kyte had an awesome thread starting with "oh geez -- i'm almost afraid to publish this one" on AskTOM where he explained how he discovered this, with emails being sent twice (from trigger, using non-transactional external procedure)
Oracle Database can restart a statement (not a transaction) when a non-repeatable read is encountered (when the write state conflicts with the read state). It can do that in Read-Committed because RC allows different MVCC read times for each statement in a transaction. To avoid too many restarts, Oracle can acquire more locks on restart (to wait rather than fail).
PostgreSQL doesn't have this (because it would require implicit savepoints for each statement, and savepoints are expensive in PostgreSQL) and restarts only the reading of the row. This results in inconsistent snapshots (results with rows from two states), but it still fits the SQL standard definition of read committed (which requires reading only the committed changes, ignoring that in MVCC databases, they can come from different commit times).
YugabyteDB does statement restarts like Oracle (using implicit savepoints for each statement in a read-committed transaction—they are scalable in YugabyteDB). It is better than PostgreSQL as the result is always time-consistent. It's still PG-compatible but compatible with good case behavior. YugabyteDB can do that in Read Committed or at higher levels if it is the first statement of the transaction, as long as no result was sent to the application (because the database doesn't know if the application did something non-transactional with those results, so it cannot transparently rollback and restart). YugabyteDB doesn't re-raise the triggers in this case and provides an easy way to troubleshoot, so it's the best implementation I know. However, there are always trade-offs in distributed systems, and this requires more Raft consensus synchronization (one per statement) when in Read Committed mode.
Aurora DSQL doesn't transparently restart. It has no Read Committed, no savepoints, and detects conflicts only at commit where it's too late to do anything else that rollback the transaction and raises an error. Aurora DSQL is based on Optimistic Concurrency Control, and the trade-off is that applications must avoid conflicts and be ready to retry transactions until they can commit. Other PostgreSQL-compatible databases are available when this is not the case.
Thanks for correcting me. Yes, it is a statement restart, not a transaction restart. Eggnog was apparently quite potent this year. The main problem with the save points in PgSQL is its lack of SCN and rowid values. CTID, xmin and xmax cannot be used for that purpose. Does YB have anything like SCN and rowid?
Yes. YugabyteDB equivalent of SCN is the timestamp from the Hybrid Logical Clock (NTP time + Lamport correction to get it monotonically increasing). So no wraparound problems 😃
YugabyteDB equivalent of rowid is an encoded version of the primary key (ybctid) as rows are stored in their primary key LSM tree rather than heap tables.
Tom Kyte wrote about "write consistency" in his "Expert Database Architecture" book. In Tom's book, transaction is restarted when it finds changed query results after concurrent transaction completes. Granted, Tom wasn't writing about PostgreSQL compatible databases as he used to be a VP at Oracle Corp. but I imagine that PgSQL has similar concurrency mechanisms. It would be very interesting to see how would that operate in a DSQL environment.
Yes, write consistency is tricky in Multi-Version Concurrency Control databases. Tom Kyte had an awesome thread starting with "oh geez -- i'm almost afraid to publish this one" on AskTOM where he explained how he discovered this, with emails being sent twice (from trigger, using non-transactional external procedure)
Thanks for correcting me. Yes, it is a statement restart, not a transaction restart. Eggnog was apparently quite potent this year. The main problem with the save points in PgSQL is its lack of SCN and rowid values. CTID, xmin and xmax cannot be used for that purpose. Does YB have anything like SCN and rowid?
Yes. YugabyteDB equivalent of SCN is the timestamp from the Hybrid Logical Clock (NTP time + Lamport correction to get it monotonically increasing). So no wraparound problems 😃
YugabyteDB equivalent of rowid is an encoded version of the primary key (ybctid) as rows are stored in their primary key LSM tree rather than heap tables.
Thanks Franck! Merry Christmas and a happy New Year to both you and your family.