How to change cluster default transaction isolation?

I want to change cluster default trx isolation from SSI to SI instead of set isolation with each session.

how to do so?

Hi @louis,

At the moment, it’s possible to change the transaction isolation level for a specific transaction or for a session. I don’t know of a way to do so for an entire cluster.

@bdarnell, do you know of a cluster-wide setting for transaction isolation level?

There’s no cluster-wide setting that you can change.
May I ask why you want to do this? We’re feeling a bit down on snapshot isolation as of late and I’d be curious to know if you get a proven benefit out of it.

SSI is a little slow for my situation cause there are some contention, such as WR conflict, read have to wait for older write.
I think SI performance is better than SSI, so i want to change it.
like pg and mysql, both of all can change their trx isolation dynamic for the whole instance instead of a single session.

How much faster is it for your application? How did you determine that it is safe for your application?

We have doubts about snapshot isolation because it only improves performance when certain kinds of conflicts occur, and those conflicts are exactly when the write skew anomaly occurs. We’re not sure when, if ever, switching from serializable to snapshot isolation is both safe and a performance improvement.

Even though other databases offer global settings for isolation levels, this has always seemed crazy to me. You must study an application to know how it will be affected by anomalies like write skew, so shouldn’t this be set at the application level (or maybe even per-transaction, although the interactions of transactions with different isolation levels are poorly understood) instead of cluster-wide?

oracle and postgresql default isolation is read commit, mysql default isolation is repeatable read, all of these isolation have write skew problem, so i think write skew is not so big a problem

If write skew is not a problem, it’s probably because you don’t have the kind of read/write conflicts in which there is a performance difference between SI and SSI. Have you measured a performance improvement in your application?

On the /#/cluster/all/distributed page of the cockroach web UI, there is a graph for “KV Transaction Restarts”. The “Forwarded Timestamp (iso=serializable)” line shows transaction retries that would have been avoided in snapshot isolation. This can give you an indication of how much you may save by changing the isolation. What fraction of your transactions experience this kind of restart?

BTW, “repeatable read” does not have write skew. It has phantom reads instead.

1 Like

MySQL innodb repeatable read have write skew, you can test that.

FYI:

I do not start the test, for now, i just design the test, and i will run the test later, then give you more information

That’s because when you ask mysql for “repeatable read”, it actually gives you a slightly weaker isolation level, “monotonic atomic views”.

1 Like

Thanks for the link~