On: Serializability vs. Strict Serializability: The Di...

[tirumaraiselvan]

Except in that case, the user already received confirmation that T1 was success before T2 was even executed. So not sure how that is even possible.

[ht85 → tirumaraiselvan]

The confirmation is coming from one of the database nodes but T2 might be executed on a different node, where T1 hasn't been replicated yet.

[tirumaraiselvan → ht85]

That seems like a problem of not having sync acknowledgements while allowing multi-instance writes at the same time. I am not sure if that is a strict serializability issue anymore.

[freels → tirumaraiselvan]

The constraint that all observers see T1 before T2 because T1 is acknowledged before T2 begins is the “in real time” component of a strict serializability guarantee. Mere serializability does not guarantee this, as unintuitive as that sounds.

Jepsen.io has a good explainer: https://jepsen.io/consistency/models/serializable

[slaymaker1907 → freels]

ANSI SQL serializable is not the same as serializability in a mathematical sense. The standard defined serializability in terms of anomalies since they came up with the standard without considering things like snapshot isolation or MVCC.

The classic example, related to mine, which shows the difference is with the following two queries:

update balls set color=0 where color=1; update balls set color=1 where color=0;

Under (mathematical) serializability, all the balls must be the the same color. However, under snapshot isolation (serializable according to the SQL standard), it is also possible to just have the colors swapped in case both queries read the same version.

You can verify it yourself, but this is not any one of the typical anomalies since both transactions neither read nor write to the same rows. A phantom read does not occur since each transaction only does a single read operation.