SQLite Begin Concurrent

https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md

by fauigerzigerk • 3 years ago

168 47 3 years ago

pstuart• 3 years ago

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

stickupkid• 3 years 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

scottlamb3• 3 years 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.

crawshaw• 3 years 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/fccd3d8ccf9e45b9ae29f2e77...

dgllghr2• 3 years 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• 3 years 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• 3 years ago

Using unordered UUIDs for keys will also slow down inserts since the b-tree needs rebalancing so often.

jgraettinger12• 3 years 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• 3 years 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.

dgllghr2• 3 years ago

Thank you for clarifying!

paulddraper3• 3 years ago

Correct. The existing way is (trivially) serializable transactions.

scottlamb3• 3 years ago

Yeah, I agree. It seems like the implementation simplicity comes at the cost of making it unreliable in the sense that depending on your schema design / data patterns, you could end up with much worse performance instead of much better performance. Also, if you do achieve better performance by really spreading out what pages you hit, you're decreasing your SSD lifespan through write amplification. [1]

On the bright side, the interface is simple enough, and maybe they could just swap out the implementation underneath for something more sophisticated later.

[1] Or on HDD, you really end up with lots of seeks for reading as well as writing. But that's probably unfair to consider: if you're using HDD, probably wanting more concurrency isn't your biggest opportunity to improve.

paulddraper3• 3 years ago

How would you get worse performance?

Currently, the lock granularity is the entire database. This makes it smaller.

The reason this isn't transactions by default is correctness concerns for programs.

slaymaker19072• 3 years ago

It's quite dangerous IMO since you run the risk of deadlock even in unexpected cases. There are times this makes sense, but most applications won't need it. Those cases are mainly where you have some long lived transactions that need to do a bunch of writes and you can guarantee they won't conflict since they are touching different tables.

vbezhenar• 3 years ago

What's dangerous about deadlock? Sqlite doesn't throw error if it encounters deadlock?

RMarcus2• 3 years ago

My reading is that no deadlocks should be possible since there is only one lock (pages are "locked" optimistically, meaning that the tx is aborted if the page has changed). "Live locks" are possible, where two repeatedly-reissued transactions cause each other to abort forever.

fauigerzigerk• 3 years ago

I agree that it's not ideal, but knowing that you can concurrently insert into separate tables would create some very welcome optimisation opportunities now that SQLite is increasingly used on servers.

The issues you describe only arise with concurrent inserts/updates into the same table. Essentially, what we would get if this got merged into the main branch is table level locking.

hinkley• 3 years ago

This won't be the first database that has page level artifacting in transactions. I can't recall which but I do recall this coming up as a potential scenario when learning about different transaction levels.

HelloNurse• 3 years ago

Practically, rollbacks due to transactions accessing an overlapping set of pages shouldn't be unexpected: the same client programmers who asked for BEGIN CONCURRENT are responsible for knowing what their queries do and dealing with their own conflicts by retrying the transaction or by avoiding or organizing concurrent access.

Moreover, high performance applications that want to saturate disk writing rates can usually afford to organize their architecture around making massively parallel modifications interference-free (for example, support for the nonconsecutive key assignments the article suggests has vast ramifications).

kijin• 3 years 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.

tracker13• 3 years 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• 3 years 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

tracker13• 3 years 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• 3 years 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• 3 years ago

I understand everyone wants concurrency because that's the way everything else in programming works these days, but SQlite in memory or on an SSD (in WAL mode) writes so fast that it makes infinitely more sense to dedicate a single database "write" worker that deals with the dB itself while allowing multiple "read" workers to access the database concurrently. Other workers are spawned to process their data and shovel their writes into a queue to feed that single worker.

In my experience, the CPU utilization of process workers preparing data into SQL statements is generally the chokepoint in most/script programs instead of SQlite write speeds themselves. Of course, this is dependent on numerous factors such as the existence of indices, compexity of write operation, etc., but--as a rule--SQlite is much more efficient if I dedicate one process to handle all writes and optimize that process for speed (i.e. batch writes, etc.).

tracker13• 3 years ago

I agree... My thinking is that if you're growing beyond a single process accessing the database, either put a service on top of the SQLite db, or move to a db more suited to the needs/workflow.

I generally think in terms of SQLite -> Firebird (maybe) -> PostgreSQL -> CockroachDB (or others). Just depends.

Scarbutt• 3 years ago

SQlite is much more efficient if I dedicate one process to handle all writes and optimize that process for speed (i.e. batch writes, etc.).

That increases complexity on the application side though.

benatkin• 3 years ago

This is some minimal write concurrency for people who correctly see that they don't need much write concurrency, but perhaps have one tiny situation where they need more than zero.

matharmin• 3 years ago

SQLite is really great database for mobile applications, where concurrency can make a massive difference in how responsive the app is.

Just as one example, imagine a mobile app that does a bulk data download, and needs to persist it all to the local database in a single transaction for consistency purposes. This transaction can take a couple of seconds, or maybe a minute of you're working with millions of rows.

Now without concurrency, your database is locked for the entire transaction, and this may block any user actions.

With WAL, you can at least continue doing reads during this transaction.

With BEGIN CONCURRENT, the user may be able to continue most actions, as long as it doesn't touch the same set of data.

Now there are other ways of solving this problem: use smaller transactions and different mechanisms to ensure consistency. Or use separate databases if you can structure your data that way. But BEGIN CONCURRENT can be a nice solution without having to restructure the data completely.

Now these data volumes may be extreme, but even with "background" transaction taking in the order of a second can have an impact on how responsive the app feels to the user.

Groxx3• 3 years 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• 3 years 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?

Groxx3• 3 years 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.

alberth• 3 years 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

What’s of more interest is the ‘bedrock’ branch that includes both WAL2 + BEGIN CONCURRENT

https://www.sqlite.org/src/timeline?r=bedrock

spiffytech• 3 years 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

Discussion: https://news.ycombinator.com/item?id=34434025

anyfoo2• 3 years 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• 3 years 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• 3 years ago

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

SQLite Begin Concurrent

Related Stories