Join the discussion
Write your take first — we'll ask for email only when you're ready to publish.
- Hacker News
Aren't the mutexes in the more modern implementations (like Cosmo [0]) & runtimes (like Go [1]) already optimized so applications can use mutexes fearlessly?So, I decided on three locking strategies: No-Lock Optimistic locking Pessimistic locking As a default, the no-lock behavior does exactly what the name implies. Nothing. This is the default because my research shows that for 99% all of this is not an issue and every interaction at this level will slow down the whole application.by ignoramous- I have encountered this problem on Jellyfin before. It works like a dream, but there are some very strange circumstances that can cause the database to become locked and then just not work until I restart the docker container. If I check the logs it just says stuff about the database being locked. It happens quite rarely and seems to be when we fidget in the menus on the smart TV like starting to watch a show to realize it's the wrong episode as you click the button, then spam the back button, etc.by ddtaylor
- Articles like this leave me with an uneasy feeling that the “solutions” are just blind workarounds - more debugging/research should be able to expose exactly what the problem is, now that would be something worth sharing.by ricardobeat
- When hctree [1] becomes stable in SQLite, it will be the only database I will be using lol!
I presume the `hc` part in project's code name should be High Concurrency.
[1] https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html
by stefanos82 - SQLite is a cracking database -- I love it -- that is let down by its awful defaults in service of 'backwards compatibility.'
You need a brace of PRAGMAs to get it to behave reasonably sanely if you do anything serious with it.
by mickeyp - There seem to be some misunderstandings in this:
> If your application fully manages this file, the assumption must be made that your application is the sole owner of this file, and nobody else will tinker with it while you are writing data to it.
Kind of, but sqlite does locking for you, so you don't have to do anything to ensure your process is the only one writing to the db file.
> [The WAL] allows multiple parallel writes to take place and get enqueued into the WAL.
The WAL doesn't allow multiple parallel writes. It just allows reads to be concurrent with a single write transaction.
by thayne - One of the biggest contributors I've had in the past for SQLite blocking was disk fragmentation.
We had some old Android tablets using our app 8 hours a day for 3-4 years. They'd complain if locking errors and slowness but every time they'd copy their data to send to us, we couldn't replicate, even on the same hardware. It wasn't until we bought one user a new device and got them to send us the old one that we could check it out. We thought maybe the ssd had worn out over the few years of continual use but installing a dev copy of our app was super fast. In the end what did work was to "defrag" the db file by copying it to a new location, deleting the original, then moving it back to the same name. Boom, no more "unable to open database" errors, no more slow downs.
I tried this on Jellyfin dbs a few months ago after running it for years and then suddenly running into performance issues, it made a big difference there too.
by EionRobb - In SQLite, transactions by default start in “deferred” mode. This means they do not take a write lock until they attempt to perform a write.
You get SQLITE_BUSY when transaction #1 starts in read mode, transaction #2 starts in write mode, and then transaction #1 attempts to upgrade from read to write mode while transaction #2 still holds the write lock.
The fix is to set a busy_timeout and to begin any transaction that does a write (any write, even if it is not the first operation in the transaction) in “immediate” mode rather than “deferred” mode.
https://zeroclarkthirty.com/2024-10-19-sqlite-database-is-lo...
by asa400