5 ms·

SQLite is not a single connection database

77 points by Igor_Wiwi 2y ago

adzm 2y ago
this is barely three sentences and doesn't even get into WAL mode or other stuff wrt journaling.
- skeeter2020 2y ago
  Yeah pretty light for HN content, especially in an area like SQLite which has great visibility into how stuff works. Though technically I guess it's right? Maybe some value for those who know nothing about SQLite and have dismissed it out of hand for lots of things it is actually very good at (like backing most websites that aren't unicorn scale).
  - TeMPOraL 2y ago
    In general I agree, but in this case, even those three sentences could be very useful to many. I remember when I first started playing with SQLite, looking at some project using it, browsing some examples, skimming the SQL side of the docs - I had an impression that it's customary to open a single connection to a given SQLite database, and use it throughout the entire program. And I mean, it feels logical - it's a file database, and you wouldn't fopen() the same file independently several times and write to it when there's even a remote chance you'll end up doing so concurrently, even on a single thread. So it's valuable to spell it out up front that yes, you can and should open multiple connections to the same SQLite database.
simonw 2y ago
Missing from this, but very important: you can connect to the same SQLite database file multiple times inside the same process (e.g. in different threads) but you can also connect to the same SQLite database file from multiple different processes on the same machine (with the same filesystem - NFS will lead to all kinds of nasty problems). Easy way to prove this to yourself: run two different terminals, run "sqlite3 data.db" in each one, run create table / insert / update queries in one and watch the results become visible to selects in the other. Changes only become visible to other connections after they have been committed. In default mode any writes will hold an exclusive lock and will cause any reads to be blocked waiting for that lock. In WAL mode reads can continue even while writes are taking place. Writes can only run one at a time, but can queue waiting for each other to finish - and since most writes take microseconds to run this is very rarely a problem. The most complicated thing to understand is the need to use BEGIN IMMEDIATE TRANSACTION in concurrent environments to avoid the occasional SQLITE_BUSY exception. The best explanation I've seen of this is here: https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-the-how-and-why-of-optimal-performance/ https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-t... - search for SQLITE_BUSY.
- hnfong 2y ago
  Same in Django. Glad we were not alone! https://code.djangoproject.com/ticket/29280 https://code.djangoproject.com/ticket/29280 The issue has since been fixed somehow (after being outstanding for 6 years). The Django docs gaslights the developer into thinking it's of some SQLite limitations: "SQLite is meant to be a lightweight database, and thus can’t support a high level of concurrency. OperationalError: database is locked errors indicate that your application is experiencing more concurrency than sqlite can handle in default configuration." But that's not true, the issue could be reproduced with just two "concurrent" connections given unfortunate timing. Since I was stubborn enough to not switch to another database (for production loads) and I knew sqlite could handle loads of a couple requests per sec, I'm still salty that Django basically introduced random errors to a service I maintain just because they didn't want to fix the problem (and lied to me in the docs).
  - pbronez 2y ago
    Is this resolved now? How well is the SQLite + Django stack working for you?
    - hnfong 2y ago
      When I found out the underlying issue, I basically applied the patch suggested in the ticket. It's been great since.
  - medmunds 2y ago
    Django would likely welcome a correction or clarification to the docs. (And "they" seem to appreciate help fixing problems.)
  - fulafel 2y ago
    Interesting history on that ticket. Closed as "invalid" 7 years ago, reopened 10 months ago, and 6 months ago added as non-default option to git (but not released yet?). I wonder what the downsides would be in enabling this by default. Would lit engthen the period of the write lock on the DB so this change could be a compatibility problem for existing systems?
makerdiety 2y ago
I would never use SQLite when there is more than one persistent data memory user.
- simonw 2y ago
  Why?
  - agos 2y ago
    no transactions on data description layer?
    - jitl 2y ago
      CREATE TABLE etc in SQLite are fully transactional, as are updates to PRAGMA user_version which you can use to store a logical schema version number. I built Notion’s SQLite migration system for client devices on those primitives.
- actinium226 2y ago
  What is a persistent data memory user?
- jmull 2y ago
  I don't know exactly what you mean, but sqlite probably supports your use case. sqlite's defaults balance performance, flexibility, and backwards compatibility. They don't work for every case, but there is almost always a lever to pull. The downside is you have to learn them. The docs are really excellent, though.
- indulona 2y ago
  true, concurrency, let alone parallelism, never worked for me when i tried it. never used in anywhere. sounds good in theory but it is unusable for most real world cases. there are endless key-value databases suitable for 90% of what sqlite does. and if you truly need multiple indexes per table and joins, just use a proper rdbms and stop playing around.
bvrmn 2y ago
> IMMEDIATE TRANSACTION It has unfair properties, there is no lock queue and workers could starve forever trying to get lock in high contention context. It's very easy to get busy timeout. You have to use external proper lock if you want to use IMMEDIATE mode. A peculiar thing: with external write lock (flock) you could get a true serializability without errors. IMPLICIT (default) transaction mode in other hand could be tricky to manage, because you could get integrity error on commit.
fnordlord 2y ago
I just dealt with a lot of sqlite concurrency stuff recently. When I first got started, a lot of recommendations were that apps should create a connection and hold that connection its the duration. This works great but I feel like it's worth noting that this can get dangerous when you're working in a multi-threaded context. You can have one thread in the middle of stepping through the results of a SELECT prepared statement and if another thread resets the statement and runs it again, that original thread will start stepping through the results from the other thread's call. Or if both threads begin a transaction, you'll get nested transaction errors. Things get really confusing, especially because you might not notice the problems until the app is running on a slower machine.
- deleted 2y ago
  [deleted]
- epcoa 2y ago
  The particulars of using SQLite are clearly documented: https://www.sqlite.org/threadsafe.html https://www.sqlite.org/threadsafe.html You must use serialized mode or provide your own concurrency control.
  - fnordlord 2y ago
    In my case, it's using default serialized mode. I would assume I was safe judging by "In serialized mode, API calls to affect or use any SQLite database connection or any object derived from such a database connection can be made safely from multiple threads" But in the unit tests I have setup, it hasn't been the case, at least with prepared statements being shared between threads.
    - epcoa 2y ago
      Yes that's exactly how it works in serialized build/mode. Are you sure not ultimately compiling with -DSQLITE_THREADSAFE=0 or 2? Also the serialized setting is distinct from the isolation level. sqlite has a famously extensive test suite and is effectively one of the most heavily tested libraries ever, and there are multithread tests in the suite. If your tests are triggering some other kind of race/undefined behavior there is also that to consider.
ncruces 2y ago
There are benefits to having a single writer connection (and multiple readers). One is that you can get fairer and more efficient locking. The (interruptible) file lock APIs that SQLite uses lead to a lot of sleeping and retrying that wastes time, and is not very fair. https://fractaledmind.github.io/2024/07/19/sqlite-in-ruby-backoff-busy-handler-problems/ https://fractaledmind.github.io/2024/07/19/sqlite-in-ruby-ba... Another some APIs, like the backup API, work better if they can track writes, and (e.g.) backing up from the single writer connection helps there.
deleted 2y ago
[deleted]

77 points by Igor_Wiwi 2y ago

adzm 2y ago

this is barely three sentences and doesn't even get into WAL mode or other stuff wrt journaling.

skeeter2020 2y ago
Yeah pretty light for HN content, especially in an area like SQLite which has great visibility into how stuff works. Though technically I guess it's right? Maybe some value for those who know nothing about SQLite and have dismissed it out of hand for lots of things it is actually very good at (like backing most websites that aren't unicorn scale).
- TeMPOraL 2y ago
  In general I agree, but in this case, even those three sentences could be very useful to many. I remember when I first started playing with SQLite, looking at some project using it, browsing some examples, skimming the SQL side of the docs - I had an impression that it's customary to open a single connection to a given SQLite database, and use it throughout the entire program. And I mean, it feels logical - it's a file database, and you wouldn't fopen() the same file independently several times and write to it when there's even a remote chance you'll end up doing so concurrently, even on a single thread. So it's valuable to spell it out up front that yes, you can and should open multiple connections to the same SQLite database.

simonw 2y ago

Missing from this, but very important: you can connect to the same SQLite database file multiple times inside the same process (e.g. in different threads) but you can also connect to the same SQLite database file from multiple different processes on the same machine (with the same filesystem - NFS will lead to all kinds of nasty problems). Easy way to prove this to yourself: run two different terminals, run "sqlite3 data.db" in each one, run create table / insert / update queries in one and watch the results become visible to selects in the other. Changes only become visible to other connections after they have been committed. In default mode any writes will hold an exclusive lock and will cause any reads to be blocked waiting for that lock. In WAL mode reads can continue even while writes are taking place. Writes can only run one at a time, but can queue waiting for each other to finish - and since most writes take microseconds to run this is very rarely a problem. The most complicated thing to understand is the need to use BEGIN IMMEDIATE TRANSACTION in concurrent environments to avoid the occasional SQLITE_BUSY exception. The best explanation I've seen of this is here: https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-the-how-and-why-of-optimal-performance/ https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-t... - search for SQLITE_BUSY.

hnfong 2y ago
Same in Django. Glad we were not alone! https://code.djangoproject.com/ticket/29280 https://code.djangoproject.com/ticket/29280 The issue has since been fixed somehow (after being outstanding for 6 years). The Django docs gaslights the developer into thinking it's of some SQLite limitations: "SQLite is meant to be a lightweight database, and thus can’t support a high level of concurrency. OperationalError: database is locked errors indicate that your application is experiencing more concurrency than sqlite can handle in default configuration." But that's not true, the issue could be reproduced with just two "concurrent" connections given unfortunate timing. Since I was stubborn enough to not switch to another database (for production loads) and I knew sqlite could handle loads of a couple requests per sec, I'm still salty that Django basically introduced random errors to a service I maintain just because they didn't want to fix the problem (and lied to me in the docs).
- pbronez 2y ago
  Is this resolved now? How well is the SQLite + Django stack working for you?
  - hnfong 2y ago
    When I found out the underlying issue, I basically applied the patch suggested in the ticket. It's been great since.
- medmunds 2y ago
  Django would likely welcome a correction or clarification to the docs. (And "they" seem to appreciate help fixing problems.)
- fulafel 2y ago
  Interesting history on that ticket. Closed as "invalid" 7 years ago, reopened 10 months ago, and 6 months ago added as non-default option to git (but not released yet?). I wonder what the downsides would be in enabling this by default. Would lit engthen the period of the write lock on the DB so this change could be a compatibility problem for existing systems?

makerdiety 2y ago

I would never use SQLite when there is more than one persistent data memory user.

simonw 2y ago
Why?
- agos 2y ago
  no transactions on data description layer?
  - jitl 2y ago
    CREATE TABLE etc in SQLite are fully transactional, as are updates to PRAGMA user_version which you can use to store a logical schema version number. I built Notion’s SQLite migration system for client devices on those primitives.
actinium226 2y ago
What is a persistent data memory user?
jmull 2y ago
I don't know exactly what you mean, but sqlite probably supports your use case. sqlite's defaults balance performance, flexibility, and backwards compatibility. They don't work for every case, but there is almost always a lever to pull. The downside is you have to learn them. The docs are really excellent, though.
indulona 2y ago
true, concurrency, let alone parallelism, never worked for me when i tried it. never used in anywhere. sounds good in theory but it is unusable for most real world cases. there are endless key-value databases suitable for 90% of what sqlite does. and if you truly need multiple indexes per table and joins, just use a proper rdbms and stop playing around.

bvrmn 2y ago

> IMMEDIATE TRANSACTION It has unfair properties, there is no lock queue and workers could starve forever trying to get lock in high contention context. It's very easy to get busy timeout. You have to use external proper lock if you want to use IMMEDIATE mode. A peculiar thing: with external write lock (flock) you could get a true serializability without errors. IMPLICIT (default) transaction mode in other hand could be tricky to manage, because you could get integrity error on commit.

fnordlord 2y ago

I just dealt with a lot of sqlite concurrency stuff recently. When I first got started, a lot of recommendations were that apps should create a connection and hold that connection its the duration. This works great but I feel like it's worth noting that this can get dangerous when you're working in a multi-threaded context. You can have one thread in the middle of stepping through the results of a SELECT prepared statement and if another thread resets the statement and runs it again, that original thread will start stepping through the results from the other thread's call. Or if both threads begin a transaction, you'll get nested transaction errors. Things get really confusing, especially because you might not notice the problems until the app is running on a slower machine.

deleted 2y ago
[deleted]
epcoa 2y ago
The particulars of using SQLite are clearly documented: https://www.sqlite.org/threadsafe.html https://www.sqlite.org/threadsafe.html You must use serialized mode or provide your own concurrency control.
- fnordlord 2y ago
  In my case, it's using default serialized mode. I would assume I was safe judging by "In serialized mode, API calls to affect or use any SQLite database connection or any object derived from such a database connection can be made safely from multiple threads" But in the unit tests I have setup, it hasn't been the case, at least with prepared statements being shared between threads.
  - epcoa 2y ago
    Yes that's exactly how it works in serialized build/mode. Are you sure not ultimately compiling with -DSQLITE_THREADSAFE=0 or 2? Also the serialized setting is distinct from the isolation level. sqlite has a famously extensive test suite and is effectively one of the most heavily tested libraries ever, and there are multithread tests in the suite. If your tests are triggering some other kind of race/undefined behavior there is also that to consider.

ncruces 2y ago

There are benefits to having a single writer connection (and multiple readers). One is that you can get fairer and more efficient locking. The (interruptible) file lock APIs that SQLite uses lead to a lot of sleeping and retrying that wastes time, and is not very fair. https://fractaledmind.github.io/2024/07/19/sqlite-in-ruby-backoff-busy-handler-problems/ https://fractaledmind.github.io/2024/07/19/sqlite-in-ruby-ba... Another some APIs, like the backup API, work better if they can track writes, and (e.g.) backing up from the single writer connection helps there.

deleted 2y ago

[deleted]