Hacker News Reader

Comment by malkia | Hacker News Reader

What do they mean by "Since SQLite does not support concurrent transactions" - it supports them, as long as you don't access the .db file through a file share (UNC, or NFS, etc) - https://www.sqlite.org/wal.html

I've been using this to update/read db from multiple threads/processes on the same machine. You can also do snapshotting with the sqlite backup API, if you want consistent view, and to not hold on transaction (or copy in-memory).

But maybe I'm missing something here... Also haven't touched sqlite in years, so not sure...

View on HN

Nope! I was mistaken - it's really multiple readers, single writer - Probably I was assuming things the whole time, and did not spent enough time thoroughly checking - granted most of the db's I've done with sqlite were more about reading than writing.

So stand corrected!

Have patience and eventually hctree [1] will become stable and will be offered to us to choose between its traditional backend mechanism and the newly implemented to support concurrency!

[1] https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html

> Writers merely append new content to the end of the WAL file. Because writers do nothing that would interfere with the actions of readers, writers and readers can run at the same time. However, since there is only one WAL file, there can only be one writer at a time.

I think the OP meant that updates have to run sequentially.

Which just means the lock happens at user scope in this case instead of per table or row. This limitation still causes so much confusion when it’s a completely reasonable design.

I've been importing data to sqlite databases running being actively written to for years. Just throws exception if the database is locked and I retry. Do 10k row batches, with a small sleep between. No issues. Helps if your use case doesn't really care about data being in order I guess.

it works if there is low traffic, as soon as you got bigger transactions or the amount of concurrent writes becomes heavier, you will at some point (even with WAL enabled) get "database locked" issues. You can work around that on application level to a certain point, but in general, if you are at that point, you should really consider using another database backend.

Probably means there’s still no row-level locking at least last I checked, very limited table-level locking. The writer still grabs a lock on entire db per docs

Did you disable auto checkpointing? Wouldn’t checkpointing result in potential corruption or at least data loss if two processes do that simultaneously? Or is that scenario exhaustively prevented with a lock file?

Your post made me look more into checkpoints, and understand better the tradeoffs in sqlite+wal about it - e.g. less frequent checkpoint means larger log (wal files), hence slower reads - as reads have to go through the wal file (there is some index, but still) to vet the data. But it makes writing faster.

And the opposite more frequent checkpoints, means faster reads (no need to through bigger wal file, and smaller index), but writes are slower.

So it really depends on what's happening right now, if you can anticipate it - e.g. populating for the first time data into it (maybe decrease checkpoint updates, then turn it back on).

Or if you constantly log, and read only that much (though not sure if you have constraints, triggers whether there are no hidden reads).

Or the opposite - a "read-only" if possible version of sqlite.db would be ideally without any wal.

So your post helped understand that there is stuff that I don't know and need to look further into it.

Thanks!

This is the tradeoff of SQLite, it is extremely fast, as long as you only mostly have one user. With WAL you can get multiple readers, but it doesn't scale the same way that e.g. PostgreSQL does

Reply on news.ycombinator.com