3 ms·

The Consider SQLite post mentions that one of SQLite’s in the past decade as “WAL mode (enabling concurrent reads and writes)”. Does this mean that the official

The Consider SQLite post mentions that one of SQLite’s in the past decade as “WAL mode (enabling concurrent reads and writes)”. Does this mean that the official advice to avoid SQLite for concurrent writes [1] is no longer a big concern? [1]: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html

simonw 4y ago
I think the way it's worded in that SQLite documentation page is still accurate: > SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution. If your application needs to support hundreds of concurrent writes a second you shouldn't use SQLite. But that's a pretty high bar!
- zoomablemind 4y ago
  Also it should be noted that SQLite is a library, so it charges against the quotas for the main process. This may become an issue with large run-time datasets even in read-only shared access scenarios.
woah 4y ago
Take this with a huge grain of salt because I am by no means an expert, but I currently am working on some scripts that import a few million rows into SQLite. I am using bash and the sqlite command line. I was getting a lot of concurrent write errors from sqlite (bear in mind i am only doing inserts of separate rows so in theory there is never an actual conflict), so I tried using WAL mode. It actually resulted in more contention. I ended up just going back to non-WAL mode and implementing an exponential backoff in bash to retry writes.
- hampelm 4y ago
  Thanks for this comment! I had tried some bulk imports into SQLite and mostly gave up after hitting similar limits, thinking I was doing something wrong with the configuration.
- deleted 4y ago
  [deleted]
- srcreigh 4y ago
  are you ingesting the data under 1 transaction? This is a common SQLite issue as writes aren't slow but transactions are. By default 1 write = 1 txn but you can put millions of writes into 1 txn and get many orders of magnitude speedup
  - woah 4y ago
    Yes, each row is written with one call to the sqlite command line. I was going to do something where I wrote inserts into a file and flushed them into sqlite in batches, but sqlite is not the bottleneck, so once retries were taken care of, it was good enough.

simonw 4y ago

I think the way it's worded in that SQLite documentation page is still accurate: > SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution. If your application needs to support hundreds of concurrent writes a second you shouldn't use SQLite. But that's a pretty high bar!

zoomablemind 4y ago
Also it should be noted that SQLite is a library, so it charges against the quotas for the main process. This may become an issue with large run-time datasets even in read-only shared access scenarios.

woah 4y ago

Take this with a huge grain of salt because I am by no means an expert, but I currently am working on some scripts that import a few million rows into SQLite. I am using bash and the sqlite command line. I was getting a lot of concurrent write errors from sqlite (bear in mind i am only doing inserts of separate rows so in theory there is never an actual conflict), so I tried using WAL mode. It actually resulted in more contention. I ended up just going back to non-WAL mode and implementing an exponential backoff in bash to retry writes.

hampelm 4y ago
Thanks for this comment! I had tried some bulk imports into SQLite and mostly gave up after hitting similar limits, thinking I was doing something wrong with the configuration.
deleted 4y ago
[deleted]
srcreigh 4y ago
are you ingesting the data under 1 transaction? This is a common SQLite issue as writes aren't slow but transactions are. By default 1 write = 1 txn but you can put millions of writes into 1 txn and get many orders of magnitude speedup
- woah 4y ago
  Yes, each row is written with one call to the sqlite command line. I was going to do something where I wrote inserts into a file and flushed them into sqlite in batches, but sqlite is not the bottleneck, so once retries were taken care of, it was good enough.