[HN Gopher] SQLite Begin Concurrent
___________________________________________________________________
SQLite Begin Concurrent
Author : fauigerzigerk
Score : 131 points
Date : 2023-07-27 13:31 UTC (9 hours ago)
(HTM) web link (www.sqlite.org)
(TXT) w3m dump (www.sqlite.org)
| tracker1 wrote:
| 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.
| bshipp wrote:
| 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.).
| Scarbutt wrote:
| _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.
| tracker1 wrote:
| 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.
| benatkin wrote:
| 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.
| zitterbewegung wrote:
| 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 wrote:
| 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 wrote:
| 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.
| matharmin wrote:
| 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.
| Groxx wrote:
| 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 wrote:
| 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 wrote:
| 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 wrote:
| 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 wrote:
| Definitely agreed there. More SQLite is more good in my
| book.
| alberth wrote:
| 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
| dgllghr wrote:
| 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.
| hinkley wrote:
| 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.
| scottlamb wrote:
| 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.
| paulddraper wrote:
| 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.
| scottlamb wrote:
| > 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.
| paulddraper wrote:
| > It's optimistic concurrency.
|
| Ah.
| slaymaker1907 wrote:
| 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.
| RMarcus wrote:
| 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.
| anyfoo wrote:
| 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?
| vbezhenar wrote:
| What's dangerous about deadlock? Sqlite doesn't throw error
| if it encounters deadlock?
| [deleted]
| ketralnis wrote:
| 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 wrote:
| Using unordered UUIDs for keys will also slow down inserts
| since the b-tree needs rebalancing so often.
| fauigerzigerk wrote:
| 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.
| formerly_proven wrote:
| 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.
| paulddraper wrote:
| Correct. The existing way is (trivially) serializable
| transactions.
| dgllghr wrote:
| Thank you for clarifying!
| kijin wrote:
| 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.
| pstuart wrote:
| I believe this functionality only exists on a non-main branch, so
| it's not available in the standard releases.
| crawshaw wrote:
| 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...
| stickupkid wrote:
| 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
| scottlamb wrote:
| 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 wrote:
| Is is the same cast of characters with a secondary branch?
| spiffytech wrote:
| 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
| anyfoo wrote:
| 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...
___________________________________________________________________
(page generated 2023-07-27 23:02 UTC)