[HN Gopher] SQLite B-Tree Module
___________________________________________________________________
SQLite B-Tree Module
Author : rdpintqogeogsaa
Score : 160 points
Date : 2022-04-03 06:29 UTC (16 hours ago)
(HTM) web link (sqlite.org)
(TXT) w3m dump (sqlite.org)
| stavros wrote:
| OK I love SQLite but there has been a long-standing
| misconception, or issue, that I'd like to get clarification on:
| Is using SQLite from multiple processes safe?
|
| For a long while, either SQLite itself or the Python bindings
| weren't safe for concurrent access, is this still the case? Can I
| use SQLite for my Django app? With the backup system on the
| Tailscale post yesterday, the operational burden is much much
| lower than Postgres for many use cases.
| Grimburger wrote:
| With WAL it's a generally smooth experience. Really depends on
| your use case though.
|
| https://sqlite.org/wal.html
| stavros wrote:
| That looks great, thanks. I didn't realize it wasn't on by
| default, but it sounds like it'll greatly benefit the web app
| use case, where all web workers are on the same Linux
| machine.
|
| Sounds like WAL is the way to go in most use cases, unless
| you want shared access from different machines.
| magicalhippo wrote:
| The WAL doesn't work with shared directories (NFS/SMB)
| though.
|
| Found this out when I tried to store the Plex data directory
| on a NSF share in a VM and it had really weird issues. Turned
| out Plex uses SQLite with WAL enabled.
| drunkpotato wrote:
| This is just what I've heard so take with a grain of salt, but
| as I understand it sqlite is "mostly" safe to use concurrently.
| There are situations where it doesn't behave correctly under
| concurrent load, the one I've heard about being when the sqlite
| is on a network-mounted drive. I'd love to hear from someone
| who knows more though.
| stavros wrote:
| That's kind of expected, I think. Due to its design, SQLite
| relies on filesystem semantics to provide atomicity. If the
| filesystem doesn't provide the semantics, it makes sense that
| atomicity will fail.
|
| I'm more asking whether it's still unsafe to use in a
| filesystem that DOES provide those semantics.
| tyilo wrote:
| My experience is that it is only safe to have one connection to
| a SQLite database. Having multiple connections mostly works,
| however if the database is stored on a slow medium, such as an
| SD-card, concurrent queries will fail.
| stavros wrote:
| Hmm, fail completely, or just block?
| tyilo wrote:
| One the queries will fail with something like SQLITE_BUSY
| benbjohnson wrote:
| You need to explicitly set the "busy_timeout" PRAGMA to
| allow concurrent write queries to block under WAL mode.
| Otherwise the query will return a BUSY error.
|
| I wrote up some tips/caveats on the Litestream site with
| more explanation. https://litestream.io/tips/
|
| Generally there's just a few settings you want to set
| when developing with SQLite:
|
| PRAGMA journal_mode = wal;
|
| PRAGMA busy_timeout = 5000;
|
| Also worth setting STRICT mode and setting SYNCHRONOUS to
| NORMAL but those are less important.
| tyingq wrote:
| It appears to be safe, but potentially slow, assuming you have
| working fcntl() locking, that is, for example, not using NFS.
| And you have to handle SQLITE_BUSY or set a timeout. More in
| their FAQ, Q5: https://www.sqlite.org/faq.html#q5
|
| There's also "BEGIN CONCURRENT" with WAL mode:
| https://sqlite.org/src/doc/begin-concurrent/doc/begin_concur...
| stavros wrote:
| Interesting, thanks!
|
| EDIT: Looks like it has a simple read/write lock, which isn't
| great for write-heavy workloads, but that's up to the
| application developer to decide.
|
| BEGIN CONCURRENT looks great, however.
| groue wrote:
| It is always safe, and by "safe" I mean "safe for data". You
| won't have to deal with data corruption. Precisely, see "How To
| Corrupt An SQLite Database File":
| https://www.sqlite.org/howtocorrupt.html
|
| Now concurrent accesses from different processes/connections
| can lead to runtime errors (SQLITE_BUSY), because the database
| happens to be locked by one connection.
|
| Those errors are greatly reduced by the WAL mode
| (https://sqlite.org/wal.html) which provides ultra-robust
| single-writer/multiple-readers semantics:
|
| - Writes can not happen concurrently (SQLITE_BUSY).
|
| - One can reduce the occurrences of such SQLITE_BUSY errors by
| using a built-in timeout
| (https://www.sqlite.org/c3ref/busy_timeout.html).
|
| - Several reads can happen concurrently, including with
| writers.
|
| - A writer connection can enter the "Serializable" isolation
| level.
|
| - A reader connection can enter the "Snapshot Isolation" level.
|
| For more details, see https://www.sqlite.org/isolation.html
|
| During all the years I've been developing the GRDB library
| (https://github.com/groue/GRDB.swift), I could never see SQLite
| fail its documented guarantees. This made it possible to build
| one of the most concurrency-focused SQLite toolkit for Swift,
| and I'm pretty happy with it (https://github.com/groue/GRDB.swi
| ft/blob/master/Documentatio...).
| stavros wrote:
| Oh excellent, I wasn't sure the "snapshot isolation" level
| existed (the WAL kind of implied it, but I wasn't sure).
| SQLITE_BUSY only happens if a write lock is kept for longer
| than the readers' configured timeout, right? It shouldn't
| happen for short writes?
| groue wrote:
| What's cool with WAL mode is that SQLITE_BUSY won't happen
| for readers (except very rare scenarios: https://www.sqlite
| .org/wal.html#sometimes_queries_return_sql...)
|
| One should only expect SQLITE_BUSY for writes (if a writer
| is already holding the lock, and the busy timeout expires
| before the other writer releases the lock). So yes, prefer
| short writes, or adjust your timeout. Generally speaking,
| SQLITE_BUSY can not be 100% prevented for writes.
| stavros wrote:
| Yeah, that makes perfect sense. For most apps, it's
| fairly easy to keep writes short, so SQLite is a great
| fit.
| groue wrote:
| Yes. Now, _many_ short writes look exactly as one _very
| long_ write from the point of view of an enqueued write
| that is waiting for its turn :-) I don 't quite remember
| how fair is SQLite scheduling, in practice.
| stavros wrote:
| Hm, right. At that point, I guess it's time to increase
| throughput, but it's a fair observation.
| avdempsey wrote:
| I don't think there is any scheduling. Each connection
| polls to see if the write lock is available up to the max
| busy timeout setting.
|
| The connection polls at these intervals: static const u8
| delays[] = { 1, 2, 5, 10, 15, 20, 25, 25, 25, 50, 50, 100
| };
|
| So, if you are using the default 5 second timeout, and
| you are trying to acquire a lock while an exclusive lock
| is held, you will wait 1 second, then 2 seconds, then 5
| seconds, and timeout. I'm not sure if you timeout after 3
| total seconds have elapsed, or sometimes after the 2 and
| sometimes after the 5.
|
| If you have a thread running many fast queries in a loop
| you can deny access to another thread that needs a lock.
| The other thread may get lucky and poll for the lock at
| the exact moment in between locks from the other thread,
| but it might not.
| tored wrote:
| Home page of SQLite uses SQLite concurrently.
|
| https://www.sqlite.org/whentouse.html
|
| If it exist a concurrency problem it should therefore either be
| in the bindings, operating system or the file system.
|
| I only used SQLite in a non-concurrent fashion from PHP (single
| use command line scripts).
|
| It should be easy to disprove with a concurrent test program if
| you can reliably tell when concurrency has failed.
| polyrand wrote:
| > Is using SQLite from multiple processes safe?
|
| Yes [0].
|
| > The Python bindings weren't safe for concurrent access, is
| this still the case?
|
| I think they're safe now [1]. The error message when using the
| connection from multiple threads is "outdated" [2].
|
| [0]: https://www.sqlite.org/threadsafe.html [1]:
| https://bugs.python.org/issue45613 [2]:
| https://docs.python.org/3/library/sqlite3.html#sqlite3.threa...
| stavros wrote:
| Excellent, thank you! Combined with WAL mode, it sounds like
| SQLite is eminently usable for most of my use cases.
| chaxor wrote:
| Yeah it's safe.
|
| The biggest problem SQLite has is it's size limitations. It can
| only hold ~281 TB in a database unfortunately. If you need more
| storage than that - that's the only reason I could endorse
| someone using a different database. :P
| stavros wrote:
| Haha, well, Postgres does have some niceties like isolation
| levels, JSON fields, etc.
| mariusor wrote:
| SQLite also supports JSON fields and operators:
| https://www.sqlite.org/json1.html
| stavros wrote:
| Well at least I'm learning a ton of stuff about SQLite in
| this thread, thank you!
| jstsch wrote:
| A bit off-topic, but SQLite continues to impress me. This year,
| we've added a simple form builder to our product which allows
| creators to quickly gather feedback/input from their audiences.
| Basically each form has it's own small sqlite database as a
| datastore and a json file for the form definition (field
| styling/constraints, etc). It's 'low tech', but performs great.
| Not having an additional service running also means having one
| less thing to maintain.
| ruslan_talpa wrote:
| Been feeling lately the fatigue of managing 3-5 different
| services for a every single trivial project. This pushed me to
| explore adding SQLite support in my product [1], i think I'll
| wrap a Litestream [2] process in the same container and call it
| "Backed in a Box" :)
|
| [1] https://github.com/subzerocloud/blue-steel
|
| [2] https://github.com/benbjohnson/litestream
|
| edit: typo
| bob1029 wrote:
| SQLite is the cornerstone behind our "One zip file = one
| entire product instance" devops experience.
| nhatbui wrote:
| I would be interested in hearing more about how you deploy.
| I'm guessing the zip has the application(s) and SQLite is
| for configuration?
| ergocoder wrote:
| I used Sqlite and wasn't impressed.
|
| Their SQL dialect is lacking. Parsing a date or regex
| extraction are extremely difficult. You have to resort to WITH
| RECURSIVE.
| plq wrote:
| This has been shared without context but I guess the SQLite team
| is starting to modularize the btree code in order to facilitate
| work like SQLightning: https://github.com/LMDB/sqlightning
|
| At the time SQLightning greatly improved SQLite performance but
| due to LMDB's requirement to have keys fit in 2/3 of a page it
| wasn't really useful as a general purpose replacement of SQLite's
| internal b-tree implementation.
|
| EDIT: It looks like SQLightning got adopted and has been worked
| on by the SQLite team under the name LumoSQL. Here's the
| project's readme:
| https://lumosql.org/src/lumosql/doc/trunk/README.md which
| contains at the end "A Brief History of LumoSQL"
| plq wrote:
| > It looks like SQLightning got adopted and has been worked on
| by the SQLite team under the name LumoSQL.
|
| Sorry about this misinformation, it looks like Sunday morning
| sleepiness got me.
|
| It rather looks like LumoSQL is maintained by an independent
| group of engineers who seem to be quite familiar with the
| sqlite codebase and tooling.
| wootest wrote:
| Where does it say that the authors/contributors of LumoSQL is
| the SQLite team or from the SQLite team? There are meeting
| notes at
| https://lumosql.org/src/lumosql/file?name=doc/meetings/lumo-...
| which does not overlap with the SQLite developers page:
| https://sqlite.org/crew.html or with commits going a year back
| on SQLite's Fossil timeline.
|
| The strongest the README gets is saying "The LumoSQL and SQLite
| projects are cooperating", which is closer than any other
| effort I've seen, and welcome if the SQLite project ever wants
| to swap out the underlying storage engine, but doesn't really
| mean that the SQLite team "works on LumoSQL" or vice versa.
| Certainly it looks like LumoSQL has put significant work into
| the cooperation by using Fossil and by "not forking" which may
| have made the cooperation palatable.
|
| Also, the SQLite project has been consistent on wanting to
| write all the code for SQLite themselves and not merge in
| patches (https://sqlite.org/copyright.html). Them working on
| another exploratory project would be a way for them to absorb
| those changes back into SQLite in a way that wouldn't be
| incompatible, but it would have to be the same team doing the
| changes for that to be consistent.
| henrydark wrote:
| Well, it mentions fossil, so...
| wootest wrote:
| Fossil is an SCM system - https://fossil-scm.org/. Are you
| suggesting that every piece of software ever kept in, say,
| git repositories are therefore maintained by the same team?
| fivea wrote:
| > EDIT: It looks like SQLightning got adopted and has been
| worked on by the SQLite team under the name LumoSQL.
|
| I see none of your claims being supported by your link.
|
| It seems LumoSQL is just an umbrella term that refers to a
| bunch of patches applied over SQLite. I'm not sure if that's
| enough to not call it a fork.
|
| Also,I saw zero references to the people actually involved in
| LumoSQL, other than the project being supported by the NLNet
| foundation.
|
| I'd expect that a small project managed and hosted by the
| SQLite team to be hosted in www.sqlite.org, but this looks like
| a completely independent and unrelated effort.
| arcticbull wrote:
| The comment at the top... This is obsolete
| documentation, retained only for historical reference. Do not
| consider anything on this page to be authoritative.
|
| Implies this is old or abandoned, but I could not find any date
| information to validate that against.
| SQLite wrote:
| Edit history:
| https://sqlite.org/docsrc/finfo?name=pages/btreemodule.in
|
| That last substantive edit to the document was in 2009. There
| were some spelling corrections in 2010. We finally got around
| to removing it from the documentation set in 2016.
| sydthrowaway wrote:
| Has anyone actually ever fixed a TODO they wrote in their life?
| rrdharan wrote:
| Yes, I have, maybe 20-30% of them but not zero as your comment
| would seem to imply.
___________________________________________________________________
(page generated 2022-04-03 23:02 UTC)