5 ms·

I understand why the default transaction isolation level of most DBMS is weaker than serializable (it's for benchmark purposes), but I'd argue the best default

I understand why the default transaction isolation level of most DBMS is weaker than serializable (it's for benchmark purposes), but I'd argue the best default is serializable. Most DBMS users don't even know there are many consistency models [1]. They expect transactions to "just work," i.e. to appear to have occurred in some total order, which is the definition of serializability [2]. And to some who know when to use a weaker isolation level for better performance can always set it per transaction [3]. --- [1] https://jepsen.io/consistency https://jepsen.io/consistency [2] https://jepsen.io/consistency/models/serializable https://jepsen.io/consistency/models/serializable [3] https://www.postgresql.org/docs/16/sql-set-transaction.html https://www.postgresql.org/docs/16/sql-set-transaction.html

Thaxll 3y ago
It has a very high cost in terms of performance.
- eatonphil 3y ago
  Is there any measurement of the impact you could point at? For example, I imagine that it depends on the workload. If the workload isn't contentious SERIALIZABLE might not make a big difference? Then again if the workload isn't contentious maybe it doesn't matter? Either way, I'd love to see numbers. Not because I don't believe anyone but I'm just curious what ballpark we're talking about. Edit: Also, SQLite and Cockroach only allow SERIALIZABLE transactions so the unviability of SERIALIZABLE seems questionable.
  - bennysaurus 3y ago
    You're right it is workload dependent. If you're low write but read heavy, you won't see huge differences in performance between RR and Serializable, so it can make sense to shift exclusively to that. The last benchmark here shows some of that with Postgres if you're looking for numbers (not an exhaustive test by any stretch): https://lchsk.com/benchmarking-concurrent-operations-in-postgresql https://lchsk.com/benchmarking-concurrent-operations-in-post... SQLite is single writer, so transaction isolation is easy, writes are linear by their very nature. Cockroach does some really funky stuff, but its serialization guarantees are only within certain conditions. Traditionally it has also had low write throughput compared to other systems, mainly due to its distributed nature. Jepsen touches on that here https://jepsen.io/analyses/cockroachdb-beta-20160829 https://jepsen.io/analyses/cockroachdb-beta-20160829 though things have vastly improved since then. To your earlier point, it may not even matter depending on the workload, or if you're aware of your database limitations. In cases where it does matter then being aware of the limitations of something like Repeatable Read makes the trade-off worth it.
  - lmm 3y ago
    > Edit: Also, SQLite and Cockroach only allow SERIALIZABLE transactions so the unviability of SERIALIZABLE seems questionable. SQLite is unviable in a lot of use cases. Also transactions are mostly a joke anyway, they were completely broken in MySQL for years and no-one cared, real systems don't actually use them much.
  - nvanbenschoten 3y ago
    > Cockroach only allow SERIALIZABLE transactions so the unviability of SERIALIZABLE seems questionable. We've actually been hard at work on adding Read Committed and Repeatable Read isolation into CockroachDB. The risks of weak isolation levels are real, but they do have a role in SQL databases. We did our best to avoid the pitfalls and inconsistencies of MySQL and even PostgreSQL by defining clear read snapshot scopes (statement vs. transaction). The preview release for both will be dropping in Jan. Some links if you're interested: - RFC: https://github.com/cockroachdb/cockroach/blob/master/docs/RFCS/20230122_read_committed_isolation.md https://github.com/cockroachdb/cockroach/blob/master/docs/RF... - Hermitage test: https://github.com/ept/hermitage/blob/master/cockroachdb.md https://github.com/ept/hermitage/blob/master/cockroachdb.md
- pkulak 3y ago
  And serializable transactions fail all the time. You have to always code so that re-running them is trivial and expected. 99% of the queries I write are fine at the lowest transaction level, and that saves me and the DB lots of time.
  - RedCrowbar 3y ago
    If your database client is any good, it should do the retries for you. EdgeDB uses serializable isolation (as the only option), and all our bindings are coded to retry on transaction serialization errors by default. Transaction deadlocks are another common issue that is triggered by concurrent transactions even at lower levels and should be retried also.
    - bcrosby95 3y ago
      I'm curious how you can handle transaction deadlocks at a low level - there might have been a lot of non-SQL processing code that determined those values and blindly re-playing the transactions could result in incorrect data. We handle this by passing our transaction a function to run - it will retry a few times if it gets a deadlock. But I don't consider this to be very low level.
      - ahoka 3y ago
        “We handle this by passing our transaction a function to run - it will retry a few times if it gets a deadlock. But I don't consider this to be very low level.” Oh neat, I was just thinking about something like this the other day.
  - baq 3y ago
    You should anyway... If you don't, you sooner or later get presented with unexpected 'transaction aborted due to deadlock' errors in prod. Better have someone who's already been through that then, at the very least.
- beltsazar 3y ago
  The performance hit is likely worth it—considering that the alternative is inconsistent data. In most cases correctness is more, if not much more, important than performance. As I said, those who know what they're doing can always use a weaker isolation in cases where performance is more important than correctness.
gigatexal 3y ago
Snapshot is good enough. And then do all your related table changes in a single atomic txn and you’re good
- aphyr 3y ago
  That depends! As the article discusses, snapshot allows anomalies--like write skew--which might violate application invariants. Depends on your workload.
  - gigatexal 3y ago
    Oh right OLTP stuff in that case I’m a bit rusty there. Most everything I do is on the OLAP side.
wolfgang42 3y ago
> can always set it per transaction [3] I just found out yesterday[1] that in Postgres “serializable” transactions can still have anomalies if other non-serializable transactions are running in parallel! So check your DBMS very carefully before trying this, I guess. [1] https://news.ycombinator.com/item?id=38685267 https://news.ycombinator.com/item?id=38685267
- deleted 3y ago
  [deleted]
continuational 3y ago
As soon as you let users edit data, you can't really benefit from serializable transactions. Partly because you don't really want arbitrary long transactions that span however long the user wants to be editing for. Partly because it's rather rude to roll back all the users edits with a "deadlock detected, please reload the form and fill it out again".
- aphyr 3y ago
  I, uh, do want to point out that the alternative here is not "everything is OK". If you don't abort when, say, two users update the same row concurrently, then you might cause (e.g.) silent data loss for one of them. Or you might end up with a record in an illegal state--say, one with two different fields that should never be in their particular states together. You have to look at your transaction structure, intended application invariants, and measured frequency of concurrency to figure out if using a relaxed isolation level is actually safe or not.
- mavelikara 3y ago
  IME in this model, the middleware-DB transaction were set to be serializable, but the web-user-edits were done under an optimistic concurrency model, using versions or timestamps. You’d run into edit conflicts, which for many applications is a reasonable compromise. The DB transactions would need to be kept open for user edits only if one were using a pessimistic model. Am I thinking about this correctly?
  - bcrosby95 3y ago
    Most systems I've worked on would just let users completely overwrite eachother and would neither hold open a transaction nor use versioning. For those that didn't behave this way, I think versioning is the sanest option (as long as requirements permit it).
bob1029 3y ago
Serializable by default doesn't seem feasible when you really dive into the concept. "Serializable" is a system property that describes how two or more transactions will take effect. In this context, I would define "transaction" as a business activity with a clear beginning, middle & end and exhibiting specific, predictable data dependencies. Without any knowledge of the transaction type(s) and their semantics per the business domain, it would be impossible to make assumptions about logical ordering of anything. SQLite is the closest thing to what you are asking for. All writes are serialized by default, but this is probably not what you really want. We can ensure multiple concurrent connections don't corrupt the data files, but we aren't achieving anything in business terms with this.
- beltsazar 3y ago
  > Serializable by default doesn't seem feasible when you really dive into the concept. I was expecting you'd argue for a weaker isolation level than serializable, but then you said: > Without any knowledge of the transaction type(s) and their semantics per the business domain, it would be impossible to make assumptions about logical ordering of anything. Serializable isolation level only guarantees some total order of transactions, and yes, it doesn't guarantee that the order will be exactly what you want (e.g. first come, first serve). So, are you now suggesting strict serializability [1], then? [1] https://jepsen.io/consistency/models/strict-serializable https://jepsen.io/consistency/models/strict-serializable
- dastbe 3y ago
  transaction in the way everyone else here is using it is referring to the primitive provided by the database which gives certain guarantees (depending on isolation level) wrt reads and writes. Even in the context of "transaction" the business activity, they are an extremely useful tool for building up exactly the kind of sequencing and dependency guarantees you refer to.
sgift 3y ago
I agree, and for the same reason that people should only use relaxed consistency models for atomics in their code if they really know what they are doing, there really is a need and they have appropriate testing. It's good that the option is available, but the headaches that you can get yourself if you don't know exactly what you are doing are real. Good luck debugging these types of Heisenbugs. You'll need it.
_a_a_a_ 3y ago
I kind of agree, but given the amount of locking[1] that would entail, the drop-off in performance would have those self-same users squalling about how slow it was . And then they had rewrite everything with a (NOLOCK) without understanding the implications are even worse ("hey guys, I put this hint everywhere and things run really fast now!"). I know this because I've seen it. IIRC Jim Gray said that Repeatable Read is 99% of Serialisable anyway, all serialisable does is hide phantoms. [1] speaking for MS SQL, which is mainly locking based.
deleted 3y ago
[deleted]

Thaxll 3y ago

It has a very high cost in terms of performance.

eatonphil 3y ago
Is there any measurement of the impact you could point at? For example, I imagine that it depends on the workload. If the workload isn't contentious SERIALIZABLE might not make a big difference? Then again if the workload isn't contentious maybe it doesn't matter? Either way, I'd love to see numbers. Not because I don't believe anyone but I'm just curious what ballpark we're talking about. Edit: Also, SQLite and Cockroach only allow SERIALIZABLE transactions so the unviability of SERIALIZABLE seems questionable.
- bennysaurus 3y ago
  You're right it is workload dependent. If you're low write but read heavy, you won't see huge differences in performance between RR and Serializable, so it can make sense to shift exclusively to that. The last benchmark here shows some of that with Postgres if you're looking for numbers (not an exhaustive test by any stretch): https://lchsk.com/benchmarking-concurrent-operations-in-postgresql https://lchsk.com/benchmarking-concurrent-operations-in-post... SQLite is single writer, so transaction isolation is easy, writes are linear by their very nature. Cockroach does some really funky stuff, but its serialization guarantees are only within certain conditions. Traditionally it has also had low write throughput compared to other systems, mainly due to its distributed nature. Jepsen touches on that here https://jepsen.io/analyses/cockroachdb-beta-20160829 https://jepsen.io/analyses/cockroachdb-beta-20160829 though things have vastly improved since then. To your earlier point, it may not even matter depending on the workload, or if you're aware of your database limitations. In cases where it does matter then being aware of the limitations of something like Repeatable Read makes the trade-off worth it.
- lmm 3y ago
  > Edit: Also, SQLite and Cockroach only allow SERIALIZABLE transactions so the unviability of SERIALIZABLE seems questionable. SQLite is unviable in a lot of use cases. Also transactions are mostly a joke anyway, they were completely broken in MySQL for years and no-one cared, real systems don't actually use them much.
- nvanbenschoten 3y ago
  > Cockroach only allow SERIALIZABLE transactions so the unviability of SERIALIZABLE seems questionable. We've actually been hard at work on adding Read Committed and Repeatable Read isolation into CockroachDB. The risks of weak isolation levels are real, but they do have a role in SQL databases. We did our best to avoid the pitfalls and inconsistencies of MySQL and even PostgreSQL by defining clear read snapshot scopes (statement vs. transaction). The preview release for both will be dropping in Jan. Some links if you're interested: - RFC: https://github.com/cockroachdb/cockroach/blob/master/docs/RFCS/20230122_read_committed_isolation.md https://github.com/cockroachdb/cockroach/blob/master/docs/RF... - Hermitage test: https://github.com/ept/hermitage/blob/master/cockroachdb.md https://github.com/ept/hermitage/blob/master/cockroachdb.md
pkulak 3y ago
And serializable transactions fail all the time. You have to always code so that re-running them is trivial and expected. 99% of the queries I write are fine at the lowest transaction level, and that saves me and the DB lots of time.
- RedCrowbar 3y ago
  If your database client is any good, it should do the retries for you. EdgeDB uses serializable isolation (as the only option), and all our bindings are coded to retry on transaction serialization errors by default. Transaction deadlocks are another common issue that is triggered by concurrent transactions even at lower levels and should be retried also.
  - bcrosby95 3y ago
    I'm curious how you can handle transaction deadlocks at a low level - there might have been a lot of non-SQL processing code that determined those values and blindly re-playing the transactions could result in incorrect data. We handle this by passing our transaction a function to run - it will retry a few times if it gets a deadlock. But I don't consider this to be very low level.
    - ahoka 3y ago
      “We handle this by passing our transaction a function to run - it will retry a few times if it gets a deadlock. But I don't consider this to be very low level.” Oh neat, I was just thinking about something like this the other day.
- baq 3y ago
  You should anyway... If you don't, you sooner or later get presented with unexpected 'transaction aborted due to deadlock' errors in prod. Better have someone who's already been through that then, at the very least.
beltsazar 3y ago
The performance hit is likely worth it—considering that the alternative is inconsistent data. In most cases correctness is more, if not much more, important than performance. As I said, those who know what they're doing can always use a weaker isolation in cases where performance is more important than correctness.

gigatexal 3y ago

Snapshot is good enough. And then do all your related table changes in a single atomic txn and you’re good

aphyr 3y ago
That depends! As the article discusses, snapshot allows anomalies--like write skew--which might violate application invariants. Depends on your workload.
- gigatexal 3y ago
  Oh right OLTP stuff in that case I’m a bit rusty there. Most everything I do is on the OLAP side.

wolfgang42 3y ago

> can always set it per transaction [3] I just found out yesterday[1] that in Postgres “serializable” transactions can still have anomalies if other non-serializable transactions are running in parallel! So check your DBMS very carefully before trying this, I guess. [1] https://news.ycombinator.com/item?id=38685267 https://news.ycombinator.com/item?id=38685267

deleted 3y ago
[deleted]

continuational 3y ago

As soon as you let users edit data, you can't really benefit from serializable transactions. Partly because you don't really want arbitrary long transactions that span however long the user wants to be editing for. Partly because it's rather rude to roll back all the users edits with a "deadlock detected, please reload the form and fill it out again".

aphyr 3y ago
I, uh, do want to point out that the alternative here is not "everything is OK". If you don't abort when, say, two users update the same row concurrently, then you might cause (e.g.) silent data loss for one of them. Or you might end up with a record in an illegal state--say, one with two different fields that should never be in their particular states together. You have to look at your transaction structure, intended application invariants, and measured frequency of concurrency to figure out if using a relaxed isolation level is actually safe or not.
mavelikara 3y ago
IME in this model, the middleware-DB transaction were set to be serializable, but the web-user-edits were done under an optimistic concurrency model, using versions or timestamps. You’d run into edit conflicts, which for many applications is a reasonable compromise. The DB transactions would need to be kept open for user edits only if one were using a pessimistic model. Am I thinking about this correctly?
- bcrosby95 3y ago
  Most systems I've worked on would just let users completely overwrite eachother and would neither hold open a transaction nor use versioning. For those that didn't behave this way, I think versioning is the sanest option (as long as requirements permit it).

bob1029 3y ago

Serializable by default doesn't seem feasible when you really dive into the concept. "Serializable" is a system property that describes how two or more transactions will take effect. In this context, I would define "transaction" as a business activity with a clear beginning, middle & end and exhibiting specific, predictable data dependencies. Without any knowledge of the transaction type(s) and their semantics per the business domain, it would be impossible to make assumptions about logical ordering of anything. SQLite is the closest thing to what you are asking for. All writes are serialized by default, but this is probably not what you really want. We can ensure multiple concurrent connections don't corrupt the data files, but we aren't achieving anything in business terms with this.

beltsazar 3y ago
> Serializable by default doesn't seem feasible when you really dive into the concept. I was expecting you'd argue for a weaker isolation level than serializable, but then you said: > Without any knowledge of the transaction type(s) and their semantics per the business domain, it would be impossible to make assumptions about logical ordering of anything. Serializable isolation level only guarantees some total order of transactions, and yes, it doesn't guarantee that the order will be exactly what you want (e.g. first come, first serve). So, are you now suggesting strict serializability [1], then? [1] https://jepsen.io/consistency/models/strict-serializable https://jepsen.io/consistency/models/strict-serializable
dastbe 3y ago
transaction in the way everyone else here is using it is referring to the primitive provided by the database which gives certain guarantees (depending on isolation level) wrt reads and writes. Even in the context of "transaction" the business activity, they are an extremely useful tool for building up exactly the kind of sequencing and dependency guarantees you refer to.

sgift 3y ago

I agree, and for the same reason that people should only use relaxed consistency models for atomics in their code if they really know what they are doing, there really is a need and they have appropriate testing. It's good that the option is available, but the headaches that you can get yourself if you don't know exactly what you are doing are real. Good luck debugging these types of Heisenbugs. You'll need it.

_a_a_a_ 3y ago

I kind of agree, but given the amount of locking[1] that would entail, the drop-off in performance would have those self-same users squalling about how slow it was . And then they had rewrite everything with a (NOLOCK) without understanding the implications are even worse ("hey guys, I put this hint everywhere and things run really fast now!"). I know this because I've seen it. IIRC Jim Gray said that Repeatable Read is 99% of Serialisable anyway, all serialisable does is hide phantoms. [1] speaking for MS SQL, which is mainly locking based.

deleted 3y ago

[deleted]