4 ms·

> OrioleDB tables don't support SERIALIZABLE isolation level. This is an unfortunate limitation to be aware of when evaluating https://www.orioledb.com/docs/u

> OrioleDB tables don't support SERIALIZABLE isolation level. This is an unfortunate limitation to be aware of when evaluating https://www.orioledb.com/docs/usage/getting-started#current-limitations https://www.orioledb.com/docs/usage/getting-started#current-...

btown 1y ago
For what it's worth, this does appear to be just a temporary situation, as mentioned in that linked document and in code comments e.g. https://github.com/orioledb/orioledb/blob/7f3b3a9a8e195ba31f6d5c643c2350bce0ae57a6/src/transam/oxid.c#L1392 https://github.com/orioledb/orioledb/blob/7f3b3a9a8e195ba31f...
- MrHamdulay 1y ago
  that message was put there 2 years ago. soon may not be coming.
thayne 1y ago
Eh. If you care about performance enough to use OrioleDB you probably also want to avoid SERIALIZABLE.
akorotkov 1y ago
We will eventually add the SERIALIZABLE isolation level to OrioleDB, but right now that's not our highest priority. Let me explain why. At first, SSI (serializable snapshot isolation) in PostgreSQL comes with significant shortcomings, including. 1) Overhead. SSI implies a heavyweight lock on any involved index page or heap tuple (even for reads). The overhead of SSI was initially measured at ~10%, but nowadays, scalability has gone much farther. These many HW-locks could slow down in multiple times a typical workload on a multicore machine. 2) SSI needs the user to be able to repeat any transaction due to serialization failure. Even a read-only transaction needs to be DEFERRABLE or might be aborted due to serialization failure (it might "saw impossible things" and needs a retry). In contrast, typically it's not hard to resolve the concurrency problems of writing transactions using explicit row-level and advisory locks, while REPEATABLE READ is enough for reporting. Frankly speaking, during my whole career, I didn't see a single case where SERIALIZABLE isolation level was justified.
- tux3 1y ago
  I had a case just recently where we needed to enforce a no-overlap constraint too complicated to express in an index (recurring time ranges). Any time you have to check constraints manually, you can't just do it before the write, or after the write, because two REPEATABLE READ write transactions will not see each other's INSERT. You need something like a lock, a two-phase commit, or SERIALIZABLE isolation for writes. Advisory locks have sharp edges, and 2PC is not so simple either, there is a lot that can go wrong. In the case of SERIALIZABLE you do need to retry in case of conflict, but usually the serialization anomalies can be limited to a reasonably fine level. And an explicit retry feels safer than risking a livelock situation when there is contention.
  - akorotkov 1y ago
    Exclusion constraints should work well for this case. On any transaction isolation level. https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION https://www.postgresql.org/docs/current/ddl-constraints.html...
    - tux3 1y ago
      That's indeed the first thing you would want to try. In this case these time ranges can repeat according to a formula, like a cron job for example. An exclusion constraint needs concrete values to compare, but here we can't pre-compute and index every future value (there are infinitely many) We solve a diophantine equation for this check (if there is a solution to Ax - By = 0, then formulas A and B can conflict at some point)
      - btown 1y ago
        This seems like a fascinating problem domain! I'm incredibly curious what product you're working on - if you're taking this much attention to detail on recurring events, it seems like it's a step beyond most scheduling systems I've seen. Heck, https://www.sciencedirect.com/science/article/pii/S1474667015301646 https://www.sciencedirect.com/science/article/pii/S147466701... and https://www.amazon.com/Declarative-Models-Concurrent-Cyclic-Processes/dp/303140551X https://www.amazon.com/Declarative-Models-Concurrent-Cyclic-... seem to indicate this is still an area of active research. And, to your point, almost certainly too complex to try to encode into a Postgres index - that would be a paper-worthy project unto itself! (I've had to implement the naive approach here, which nested-loops over dates and rules and finds ones that might conflict. Definitely not meant to scale, and would be a nightmare if not at a date-level granularity!) No pressure to share, but would love to learn more!

btown 1y ago

For what it's worth, this does appear to be just a temporary situation, as mentioned in that linked document and in code comments e.g. https://github.com/orioledb/orioledb/blob/7f3b3a9a8e195ba31f6d5c643c2350bce0ae57a6/src/transam/oxid.c#L1392 https://github.com/orioledb/orioledb/blob/7f3b3a9a8e195ba31f...

MrHamdulay 1y ago
that message was put there 2 years ago. soon may not be coming.

thayne 1y ago

Eh. If you care about performance enough to use OrioleDB you probably also want to avoid SERIALIZABLE.

akorotkov 1y ago

We will eventually add the SERIALIZABLE isolation level to OrioleDB, but right now that's not our highest priority. Let me explain why. At first, SSI (serializable snapshot isolation) in PostgreSQL comes with significant shortcomings, including. 1) Overhead. SSI implies a heavyweight lock on any involved index page or heap tuple (even for reads). The overhead of SSI was initially measured at ~10%, but nowadays, scalability has gone much farther. These many HW-locks could slow down in multiple times a typical workload on a multicore machine. 2) SSI needs the user to be able to repeat any transaction due to serialization failure. Even a read-only transaction needs to be DEFERRABLE or might be aborted due to serialization failure (it might "saw impossible things" and needs a retry). In contrast, typically it's not hard to resolve the concurrency problems of writing transactions using explicit row-level and advisory locks, while REPEATABLE READ is enough for reporting. Frankly speaking, during my whole career, I didn't see a single case where SERIALIZABLE isolation level was justified.

tux3 1y ago
I had a case just recently where we needed to enforce a no-overlap constraint too complicated to express in an index (recurring time ranges). Any time you have to check constraints manually, you can't just do it before the write, or after the write, because two REPEATABLE READ write transactions will not see each other's INSERT. You need something like a lock, a two-phase commit, or SERIALIZABLE isolation for writes. Advisory locks have sharp edges, and 2PC is not so simple either, there is a lot that can go wrong. In the case of SERIALIZABLE you do need to retry in case of conflict, but usually the serialization anomalies can be limited to a reasonably fine level. And an explicit retry feels safer than risking a livelock situation when there is contention.
- akorotkov 1y ago
  Exclusion constraints should work well for this case. On any transaction isolation level. https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION https://www.postgresql.org/docs/current/ddl-constraints.html...
  - tux3 1y ago
    That's indeed the first thing you would want to try. In this case these time ranges can repeat according to a formula, like a cron job for example. An exclusion constraint needs concrete values to compare, but here we can't pre-compute and index every future value (there are infinitely many) We solve a diophantine equation for this check (if there is a solution to Ax - By = 0, then formulas A and B can conflict at some point)
    - btown 1y ago
      This seems like a fascinating problem domain! I'm incredibly curious what product you're working on - if you're taking this much attention to detail on recurring events, it seems like it's a step beyond most scheduling systems I've seen. Heck, https://www.sciencedirect.com/science/article/pii/S1474667015301646 https://www.sciencedirect.com/science/article/pii/S147466701... and https://www.amazon.com/Declarative-Models-Concurrent-Cyclic-Processes/dp/303140551X https://www.amazon.com/Declarative-Models-Concurrent-Cyclic-... seem to indicate this is still an area of active research. And, to your point, almost certainly too complex to try to encode into a Postgres index - that would be a paper-worthy project unto itself! (I've had to implement the naive approach here, which nested-loops over dates and rules and finds ones that might conflict. Definitely not meant to scale, and would be a nightmare if not at a date-level granularity!) No pressure to share, but would love to learn more!