7 ms·

SQLite Begin Concurrent

168 points by fauigerzigerk 3y ago

pstuart 3y ago
I believe this functionality only exists on a non-main branch, so it's not available in the standard releases.
- stickupkid 3y ago
  Correct, see the github mirror[1]. I don't know how well supported that feature is compared to main branch. If it was completely stable, then it would have already landed in the main stable branch. Clarity about the roadmap of that branch would be nice. Edit: maybe it's still being actively developed? 1. https://github.com/sqlite/sqlite/tree/begin-concurrent https://github.com/sqlite/sqlite/tree/begin-concurrent
  - scottlamb 3y ago
    It may not be just about stability (in the sense of being free of defects) but also about whether they've decided to commit to maintaining it forever and supporting it in combination with whatever other features they intend to add to the main branch.
    - detourdog 3y ago
      Is is the same cast of characters with a secondary branch?
      - anacrolix 3y ago
        uhh, yes, fwiw.
- crawshaw 3y ago
  I believe you are right, here is a recent forum comment stating there are no plans to move this into the main branch: https://sqlite.org/forum/forumpost/fccd3d8ccf9e45b9ae29f2e77483131ac526eb9832caf55b9a8d82c7ac385388 https://sqlite.org/forum/forumpost/fccd3d8ccf9e45b9ae29f2e77...
dgllghr 3y ago
I don't know how I feel about this implementation. On the one hand, I love its simplicity and the fact that you still have Serializable transactions. On the other hand, having conflicts at the page level means that the occurrence of conflicts may be surprising because it's based on this fuzzy notion of having values "close together". Close together changes based on your page size configuration and, as the article points out, is not necessarily correlated with a natural key in ROWID tables. I would be very interested to see this system in use in a few real applications to get an sense of how much this really matters.
- ketralnis 3y ago
  I'd guess using uuids for the keys instead of ROWIDs might help. I'd also guess that UNIQUE constraints must also trigger a conflict and that "close together" in index btrees also does. If that's true then using a uuid primary key is defeated by the indices that you'd use to work around it.
  - mholt 3y ago
    Using unordered UUIDs for keys will also slow down inserts since the b-tree needs rebalancing so often.
  - jgraettinger1 3y ago
    A uuid key would crush performance vs rowid, though, especially for ordered scans, and you’d still have conflicts if parallel r/w accesses are temporally correlated (you wrote X and Y at the ~same time, and tend to read them at the ~same time)
- formerly_proven 3y ago
  Just to clarify, these are not serializable transactions, they're snapshot isolation. The serialization this document talks about refers to BEGIN CONCURRENT allowing multiple writing transactions to co-exist, but COMMITs do not happen in parallel; at any given point in time only one COMMIT can be in-progress. Hence, serialized.
kijin 3y ago
So, higher concurrency in exchange for a higher chance of COMMIT failing. Not a big problem IMO if you handle failures properly (either abort or retry as needed), which you should if you're deliberately using this feature. In fact, one should always assume that a transaction might fail, since other databases aren't immune to conflicts and deadlocks, either. The only difference with SQLite is that it is very clear and explicit about what it is doing.
tracker1 3y ago
Not to start a flamewar, or discount SQLite, I love SQLite... but if you really need this kind of feature, wouldn't something like Firebird DB be a better option at that point? I know the licensing and embedding is different, just curious what others think.
- zitterbewegung 3y ago
  SQLite is getting to the point where small websites it can work as the default . Wordpress basic has SQLite in the default install configuration
  - tracker1 3y ago
    Of course, SQLite is great for a lot of things, including small websites, or nearly anything mostly read without a ton of writes. It's not much at all over the top of modern disk io... nvme drives are crazy fast. My only thinking is in terms of additional concurrency, or for that matter, multiple shared access since FB can grow from embedded to dedicated server... I often think of it as a few steps between SQLite and Postgres for some scenarios.
  - klabb3 3y ago
    What people tend to overlook is: - SQLite is typically used on same-host without any networking… - Meaning that latency is much, much lower… - Meaning that you will have much fewer overlapping transactions at any given point in time… - Which results in less memory usage and… - An SQLite instance will remain “low-latency” in wall-clock time even under pressure… - And you have more leeway for ping-ponging (the N+1 problem) which can reduce query complexity, amount of indices, etc In short, the performance you can squeeze out of a SQLite system is is unexpectedly high, as long as you can fit app+db on the same machine. Instead, I believe you’d choose a single-master networked db often for other (legitimate, but typcially not perf) reasons. As a meager indie dev, this is a very appealing trade off. I’m not a db expert, and looking at the total complexity and churn in the field, I really don’t want to become one. SQLite has like three knobs. And as a bonus, I can run the same system on client machines.
- bshipp 3y ago
Groxx 3y ago
Hooray, non-repeatable transaction reads in billions of databases where previously that was not an issue! I really hope browsers/apps/etc don't enable this... but I'm sure many will. A fully sequential database is trivially safe in a way that is absurdly difficult to replicate with concurrency in the mix, and people consistently come to rely on that safety and not realize it. --- I get the desire, and honestly putting it in sqlite is probably the right choice. With enough care, this seems like a very reasonable set of tradeoffs, and there will almost certainly be good, impactful uses of it. I'm just lamenting the inevitable human decisions this will lead to if it hits the standard feature set, because obviously concurrent is better and faster.
- daveguy 3y ago
  Do you think possibly they could make it optional and not turned on by default? Like maybe by requiring completely different keywords for concurrent vs non-concurrent?
  - Groxx 3y ago
    I get the sarcasm, but I also run into many, many, many product-team-management and company-wide-infra-decision-making types that mandate concurrency when it is available, and slam it through regardless of the cost. With teams dominated by freshly-graduated-from-college engs. And loads of times that I've seen existing systems hit a performance bottleneck, and the solution was to relax consistency because it speeds up and the existing tests pass. Putting a loaded gun within reach of millions of people is less safe than not doing that.
    - valenterry 3y ago
      See it positive! I've known too many cases where NoSQL databases where forced upon developers because of HA and concurrency/performance. Now I can at least say "wait, there is concurrency in SQLite if we need it. No problem".
      - Groxx 3y ago
alberth 3y ago
What’s the news here? This capability has existed for years. It’s just not in -main. https://www.sqlite.org/src/timeline?r=begin-concurrent https://www.sqlite.org/src/timeline?r=begin-concurrent What’s of more interest is the ‘bedrock’ branch that includes both WAL2 + BEGIN CONCURRENT https://www.sqlite.org/src/timeline?r=bedrock https://www.sqlite.org/src/timeline?r=bedrock
spiffytech 3y ago
SQLite is also working on a high-performance HC-tree backend that locks rows, instead of pages or databases. In their preliminary (extremely proof-of-concept!) benchmarks, it significantly outperforms BEGIN CONCURRENT + WAL2. Write performance scales linearly until the memory bus is saturated. https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html Discussion: https://news.ycombinator.com/item?id=34434025 https://news.ycombinator.com/item?id=34434025
- anyfoo 3y ago
  Neat. I wonder if we'll somehow reach the point where sqlite isn't really "lite" anymore, but I'm actually not complaining. If it keeps the stability and self-containment (in multiple ways, e.g. you don't need any sqlite components outside your application, where sqlite is just linked in) up, I continue to be happy. And if that works even with fast concurrency and fine-granular locking, great...
  - kjeetgill 3y ago
    Don't quote me, but I think the name isn't meant to be SQL-lite but SQL-ite with the suffix meaning rock/stone.
aprao 3y ago
Is the idea here for SQLite to move from DB level locks to page locks and finally to row level locks?

168 points by fauigerzigerk 3y ago

pstuart 3y ago

I believe this functionality only exists on a non-main branch, so it's not available in the standard releases.

stickupkid 3y ago
Correct, see the github mirror[1]. I don't know how well supported that feature is compared to main branch. If it was completely stable, then it would have already landed in the main stable branch. Clarity about the roadmap of that branch would be nice. Edit: maybe it's still being actively developed? 1. https://github.com/sqlite/sqlite/tree/begin-concurrent https://github.com/sqlite/sqlite/tree/begin-concurrent
- scottlamb 3y ago
  It may not be just about stability (in the sense of being free of defects) but also about whether they've decided to commit to maintaining it forever and supporting it in combination with whatever other features they intend to add to the main branch.
  - detourdog 3y ago
    Is is the same cast of characters with a secondary branch?
    - anacrolix 3y ago
      uhh, yes, fwiw.
crawshaw 3y ago
I believe you are right, here is a recent forum comment stating there are no plans to move this into the main branch: https://sqlite.org/forum/forumpost/fccd3d8ccf9e45b9ae29f2e77483131ac526eb9832caf55b9a8d82c7ac385388 https://sqlite.org/forum/forumpost/fccd3d8ccf9e45b9ae29f2e77...

dgllghr 3y ago

I don't know how I feel about this implementation. On the one hand, I love its simplicity and the fact that you still have Serializable transactions. On the other hand, having conflicts at the page level means that the occurrence of conflicts may be surprising because it's based on this fuzzy notion of having values "close together". Close together changes based on your page size configuration and, as the article points out, is not necessarily correlated with a natural key in ROWID tables. I would be very interested to see this system in use in a few real applications to get an sense of how much this really matters.

ketralnis 3y ago
I'd guess using uuids for the keys instead of ROWIDs might help. I'd also guess that UNIQUE constraints must also trigger a conflict and that "close together" in index btrees also does. If that's true then using a uuid primary key is defeated by the indices that you'd use to work around it.
- mholt 3y ago
  Using unordered UUIDs for keys will also slow down inserts since the b-tree needs rebalancing so often.
- jgraettinger1 3y ago
  A uuid key would crush performance vs rowid, though, especially for ordered scans, and you’d still have conflicts if parallel r/w accesses are temporally correlated (you wrote X and Y at the ~same time, and tend to read them at the ~same time)
formerly_proven 3y ago
Just to clarify, these are not serializable transactions, they're snapshot isolation. The serialization this document talks about refers to BEGIN CONCURRENT allowing multiple writing transactions to co-exist, but COMMITs do not happen in parallel; at any given point in time only one COMMIT can be in-progress. Hence, serialized.

kijin 3y ago

So, higher concurrency in exchange for a higher chance of COMMIT failing. Not a big problem IMO if you handle failures properly (either abort or retry as needed), which you should if you're deliberately using this feature. In fact, one should always assume that a transaction might fail, since other databases aren't immune to conflicts and deadlocks, either. The only difference with SQLite is that it is very clear and explicit about what it is doing.

tracker1 3y ago

Not to start a flamewar, or discount SQLite, I love SQLite... but if you really need this kind of feature, wouldn't something like Firebird DB be a better option at that point? I know the licensing and embedding is different, just curious what others think.

zitterbewegung 3y ago
SQLite is getting to the point where small websites it can work as the default . Wordpress basic has SQLite in the default install configuration
- tracker1 3y ago
  Of course, SQLite is great for a lot of things, including small websites, or nearly anything mostly read without a ton of writes. It's not much at all over the top of modern disk io... nvme drives are crazy fast. My only thinking is in terms of additional concurrency, or for that matter, multiple shared access since FB can grow from embedded to dedicated server... I often think of it as a few steps between SQLite and Postgres for some scenarios.
- klabb3 3y ago
  What people tend to overlook is: - SQLite is typically used on same-host without any networking… - Meaning that latency is much, much lower… - Meaning that you will have much fewer overlapping transactions at any given point in time… - Which results in less memory usage and… - An SQLite instance will remain “low-latency” in wall-clock time even under pressure… - And you have more leeway for ping-ponging (the N+1 problem) which can reduce query complexity, amount of indices, etc In short, the performance you can squeeze out of a SQLite system is is unexpectedly high, as long as you can fit app+db on the same machine. Instead, I believe you’d choose a single-master networked db often for other (legitimate, but typcially not perf) reasons. As a meager indie dev, this is a very appealing trade off. I’m not a db expert, and looking at the total complexity and churn in the field, I really don’t want to become one. SQLite has like three knobs. And as a bonus, I can run the same system on client machines.
bshipp 3y ago

Groxx 3y ago

Hooray, non-repeatable transaction reads in billions of databases where previously that was not an issue! I really hope browsers/apps/etc don't enable this... but I'm sure many will. A fully sequential database is trivially safe in a way that is absurdly difficult to replicate with concurrency in the mix, and people consistently come to rely on that safety and not realize it. --- I get the desire, and honestly putting it in sqlite is probably the right choice. With enough care, this seems like a very reasonable set of tradeoffs, and there will almost certainly be good, impactful uses of it. I'm just lamenting the inevitable human decisions this will lead to if it hits the standard feature set, because obviously concurrent is better and faster.

daveguy 3y ago
Do you think possibly they could make it optional and not turned on by default? Like maybe by requiring completely different keywords for concurrent vs non-concurrent?
- Groxx 3y ago
  I get the sarcasm, but I also run into many, many, many product-team-management and company-wide-infra-decision-making types that mandate concurrency when it is available, and slam it through regardless of the cost. With teams dominated by freshly-graduated-from-college engs. And loads of times that I've seen existing systems hit a performance bottleneck, and the solution was to relax consistency because it speeds up and the existing tests pass. Putting a loaded gun within reach of millions of people is less safe than not doing that.
  - valenterry 3y ago
    See it positive! I've known too many cases where NoSQL databases where forced upon developers because of HA and concurrency/performance. Now I can at least say "wait, there is concurrency in SQLite if we need it. No problem".
    - Groxx 3y ago

alberth 3y ago

What’s the news here? This capability has existed for years. It’s just not in -main. https://www.sqlite.org/src/timeline?r=begin-concurrent https://www.sqlite.org/src/timeline?r=begin-concurrent What’s of more interest is the ‘bedrock’ branch that includes both WAL2 + BEGIN CONCURRENT https://www.sqlite.org/src/timeline?r=bedrock https://www.sqlite.org/src/timeline?r=bedrock

spiffytech 3y ago

SQLite is also working on a high-performance HC-tree backend that locks rows, instead of pages or databases. In their preliminary (extremely proof-of-concept!) benchmarks, it significantly outperforms BEGIN CONCURRENT + WAL2. Write performance scales linearly until the memory bus is saturated. https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html Discussion: https://news.ycombinator.com/item?id=34434025 https://news.ycombinator.com/item?id=34434025

anyfoo 3y ago
Neat. I wonder if we'll somehow reach the point where sqlite isn't really "lite" anymore, but I'm actually not complaining. If it keeps the stability and self-containment (in multiple ways, e.g. you don't need any sqlite components outside your application, where sqlite is just linked in) up, I continue to be happy. And if that works even with fast concurrency and fine-granular locking, great...
- kjeetgill 3y ago
  Don't quote me, but I think the name isn't meant to be SQL-lite but SQL-ite with the suffix meaning rock/stone.

aprao 3y ago

Is the idea here for SQLite to move from DB level locks to page locks and finally to row level locks?