Serializable only??

[jods4]

I was going through the docs on the website and read that EdgeDB uses isolation level SERIALIZABLE only.

I was quite surprised:

• Yes, SERIALIZABLE is the isolation that has the less read anomalies, so the most desirable one.
• But there's a reason why it's not the only one; and not even the default one. Its implementation comes with a big dip in performance and many more queries will fail and should be retried.

Given that EdgeDB also prides itself in performance / scalability, this struck me as an odd choice.
Thoughts?

[elprans]

Its implementation comes with a big dip in performance

Do you have concrete data to show it's actually true? To quote Kevin Grittner (Postgres core dev):

At various times I ran different benchmarks where the overhead ranged ranged from "lost in the noise" to about 5% for one variety of "worst case". Dan ran DBT-2, following the instructions on how to measure performance quite rigorously, and came up with a 2% hit versus repeatable read for that workload.

This was back when SSI was first implemented in Postgres, and the overhead has only been reduced since then.

Likewise, we did not observe a noticeable difference from repeatable read isolation in our tests.

many more queries will fail and should be retried

EdgeDB clients are designed to automatically retry transaction conflicts. No action on your part is usually needed.

Lastly, serializable isolation is actually required by EdgeDB to fix certain anomalies exhibited by complex mutations running concurrently, see #2875 and #2876.

[amacfie]

Is it possible for a transaction to go through the serialization anomaly retry loop infinitely? If so, did your tests suggest that this is unlikely in some sense?

[elprans]

The retry loop goes through a finite number of tries (configurable on the client) after which it gives up and re-raises the serialization error.

[elprans]

Example for the Python binding: https://www.edgedb.com/docs/clients/python/api/asyncio_client#edgedb.AsyncIOClient.with_retry_options

[jods4]

Do you have concrete data to show it's actually true?

I have not collected my own data on this; and neither am I an expert on Postgres.
I'm a lot more knowledgeable in SQL Server + Oracle. I would assume a lot of that knowledge transpose but glancing through the Postgres transaction docs it's clear that there are significant implementation differences.

The "common knowledge" (take that with a grain of salt) is that SERIALIZABLE requires a lot more locking than READ COMMITTED or SNAPSHOT.
The main issue is usually not the locks book-keeping work, which is prob. lost in the overall amount of work performed; but the effect it has on concurrent throughput.
More sessions will have to block and wait on one another and the overall performance will decrease. Although here the Postgres docs seem to indicate the implementation doesn't block, rather uses locks for "checking"... but this is on top of REPEATABLE READ (see below)... as I said: not a Postgres expert here and benchmarking is hard.

The main point of the previous paragraph: dip in performance here is supposed to mean "in concurrent sessions" not "single query execution time".

Dan ran DBT-2, following the instructions on how to measure performance quite rigorously, and came up with a 2% hit versus repeatable read for that workload.

Benchmarking this kind of stuff is super hard. I don't have hard number that prove the opposite but I also don't think this is a very conclusive benchmark either.

• This is versus REPEATABLE READ, which already locks a bunch. Default RDBMS isolation levels are READ COMMITTED or SNAPSHOT. The first locks writes only, the 2nd doesn't block readers at all (based on MVCC, same as Oracle and Postgres). So how does it perform against READ COMMITTED?
• I don't know how many concurrent threads were used but DBT-2 might not be the best test to exhibit the concurrent sessions impact anyway. Its queries are almost all id-driven (create one order, update the stock of one product), which has a low chance of collisions. Only low stock query is a sweeping count, but even there threads are randomly partitioned by warehouse and district with the explicit goal of avoiding overlaps.

What % of serializable failure did the benchmark get? If that number is low that might indicate it is not representative of the use cases that would be most impacted by SERIALIZABLE.

An anecdote: I was working on a kind of busy work dispatching system with Oracle. I had a few transactions that would call for SERIALIZABLE isolation to be correct. When testing, in this scenario transactions would fail with a really high failure rate and needed to be retried. So much that I ended up working around the need for SERIALIZABLE as much as possible and only kept the transactions that were just too annoying to do another way.

Final food for thoughts on this topic:
Why do you think READ COMMITTED is the default isolation level in Postgres?
Without taking perf into consideration, there's zero benefit of using READ COMMITTED instead of REPEATABLE READ.

EdgeDB clients are designed to automatically retry transaction conflicts. No action on your part is usually needed.

This is a nice feature.

It's also a little error-prone because it's easy to miss when devs read the docs too quickly or not at all.
The docs point out that there should be no side-effects inside the tx for this reason (e.g. sending a mail) but there are others things to be cautious of, such as accessing non-local state (e.g. a variable outside the tx lambda).

Note that "no action required" is nice but the perf hit is there if those happen frequently in your workloads (see my anecdote above).

Lastly, serializable isolation is actually required by EdgeDB to fix certain anomalies exhibited by complex mutations running concurrently, see #2875 and #2876.

Absolutely, SERIALIZABLE exists because it's sometimes required.
Data is hard and traditionally RDBMS ask devs to enable SERIALIZABLE when they do something that requires it.
It's prone to errors -- devs not noticing/forgetting the requirement -- but somehow no RDBMS that I know of has made SERIALIZABLE the default.

Interestingly #2875 is a consequence of the chosen inheritance strategy and could be avoided by modeling inheritance differently.
It's an illustration that data is complex and full of choices, related to #3468

[elprans]

I would assume a lot of that knowledge transpose but glancing through the Postgres transaction docs it's clear that there are significant implementation differences.

Yes. PostgreSQL's implementation is very different from Oracle's and is state-of-the-art (at least at the time of implementation). If you're interested in this sort of thing, I recommend reading the original whitepaper.

but there are others things to be cautious of, such as accessing non-local state

Doing side effects before a transaction is committed is a bug regardless of the isolation level.

Why do you think READ COMMITTED is the default isolation level in Postgres?

Historical reasons (and compatibility with other databases). Newer SQL implementations choose stronger isolation levels by default (e.g. Amazon Redshift, and CockroachDB have SERIALIZABLE isolation only).

Without taking perf into consideration, there's zero benefit of using READ COMMITTED instead of REPEATABLE READ.

Read/write anomalies lead to issues that are very hard to detect and debug, sometimes with catastrophic results. Modern workloads are much more concurrent than before and so these anomalies are a lot more likely. Furthermore, the notion that higher isolation levels are somehow much slower are simply not true anymore, especially in Postgres.

More sessions will have to block and wait on one another and the overall performance will decrease

Postgres uses MVCC, so no reader locks, and write locks are row-level.

[jods4]

Thanks, that's interesting infos.
I've glanced at the whitepaper and I'll be sure to read it more carefully when I have the time to.
Postgres sadly is not gonna be my main DB any time soon, but I'm still interested in these kinds of topics.

Doing side effects before a transaction is committed is a bug regardless of the isolation level.

I was talking simply of the code logic.
It won't be immediately obvious to an average developer that the lambda in a transaction can actually run twice.
Yet this is an important fact, consider:

let outsideVar = 0;
await client.transaction(tx => {
  await tx.execute(`insert User {name := 'Don', rank := ${outsideVar++} }`);
});

Don't look much into what the code is actually doing.

What I want to say is that it can fairly easily be the case that code inside the transaction accesses a variable outside (maybe because the variable is used again after the transaction.

If that variable is written to in any way (think complex transaction logic), then it can obviously lead to bugs.

This is something devs need to be aware of and its (1) not obvious; (2) very hard to catch in unit test because tx will never fail and replay in that context.

[elprans]

Sure, but this is still a better and a more visible trade-off (and totally lint-able too), than having to deal with concurrency anomalies. You can learn to not to put outside state mutation in a transaction block, but you can't do anything about wrong data being read or written because your database uses weak isolation (and for no good reason other than historical).

Also, EdgeQL is really good at doing a bunch of things at once in a single statement, so the need for explicit transaction blocks is greatly diminished (but you still get the retry goodness).

[jods4]

Yes, I agree with that.
When you use serializable you should be prepared for retries and having that feature for free like that is nice.