SQLite Begin Concurrent | Hacker News Reader

SQLite Begin Concurrent

sqlite.org

168 pointsfauigerzigerk47 comments

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

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...

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.

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.

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.

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

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)

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.

Thank you for clarifying!

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

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.

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.

> How would you get worse performance?

It's optimistic concurrency. If there's a conflict, the client gets SQLITE_BUSY_SNAPSHOT and has to roll back and try again. There's no guarantee the second try will succeed either. They might need to back off (as in, sleep between attempts) and/or entirely stop using "BEGIN CONCURRENT" after n tries.

edit:

originally also wrote above: In fact, without some way of ensuring transactions write to pages in a consistent order, I don't think there's any guarantee any transaction will make progress. It could degenerate to livelock.

...but I think I was wrong about this part. I think SQLITE_BUSY_SNAPSHOT means another transaction actually committed touching these pages; so progress was made in the system as a whole.

> It's optimistic concurrency.

Ah.

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.

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

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.

Above, scottlamb came to the conclusion that live lock isn't possible after all, because one transaction will always have made progress. Intuitively, that makes sense, but intuition alone is always dangerous with concurrency. Which is it?

After reading the post a bit more carefully, I think livelock is impossible since transactions are only aborted if one or more read pages have been modified since they were read. That implies forward progress must be made another transaction in order for a transaction to be aborted.

However, unless they do some very careful accounting, this sounds equivalent to snapshot isolation which has anomalies not found with serializable isolation, though the chance of this happening is reduced by using page level locking.

Right, unless they’re marking non-leaf pages as “read” — which would imply marking the root — it would have to be snapshot, right?

Otherwise you can’t properly abort the txn because a “select x where y” that was previously empty, no longer is.

Ah, I missed that this requires WAL mode -- indeed, if WAL is used, one transaction should always be able to make progress.

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.

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.

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).

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

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

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.

Is is the same cast of characters with a secondary branch?

uhh, yes, fwiw.

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...

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.

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

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

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.

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?

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.

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".

Definitely agreed there. More SQLite is more good in my book.

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.

SQLite is getting to the point where small websites it can work as the default . Wordpress basic has SQLite in the default install configuration

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.

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.

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.).

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.

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.

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.

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.

Reply on news.ycombinator.com