5 ms·

It has a very high cost in terms of performance.

by Thaxll 3y ago

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.

It has a very high cost in terms of performance.

by Thaxll 3y ago

It has a very high cost in terms of performance.

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.