In case it wasn’t clear, if the two tables have an FK relationship, then the “causal reverse” can’t happen in the first place – the second insert is forced to check the value from the first, at which point the transaction interact and thus the “first” actually happens first. In general, that is the best way to avoid “causal reverse” situations – ensuring that writes re-read whatever it is they’re related to, either by explicitly selecting in the same txn or implicitly via an FK.
Without something like that, these anomalies can happen to any backup, dump and even a SELECT that just happens at an unlucky timestamp (or is assigned one via AS OF SYSTEM TIME
). That said, in normal application traffic, where SELECTs just always run at current time, the window to observe such an anomaly would close quickly as current time moves beyond both writes, while a BACKUP or time travel query that runs in that window will continue to see it.
It sounds like what you’re suggesting is briefly, prior to a backup, temporarily bumping the whole system from serializable to linearizable so such anomalies are impossible just long enough to get timestamp that we can pick for BACKUP? We don’t currently have any sort of support dynamically making such a change, but it is an interesting idea! Unfortunately it is probably also pretty complex to implement, so I’m not sure it would be something we’d be able to do in the near term. For now, if causal reverses are a deal breaker, I think the best bet is to avoid them in the first place (e.g. with FKs) as described above.
Does that make sense?