[summarity]

I've always tried to avoid situations that could lead to SQLITE_BUSY. SQLITE_BUSY is an architecture smell. For standard SQLite in WAL, I usually structure an app with a read "connection" pool, and a single-entry write connection pool. Making the application aware of who _actually_ holds the write lock gives you the ability to proactively design access patterns, not try to react in the moment, and to get observability into lock contention, etc.

[simonw]

Even with that pattern (which I use too) you still need to ensure those write operations always start a transaction at the beginning in order to avoid SQLITE_BUSY.

[summarity]

Yes, indeed. In my apps, which are mostly Nim, my pool manager ensures this always happens - along with a host of other optimizations. I often start with barebones SQLite and then later switch to LiteSync (distributed SQLite with multi-master replication), so I keep the lock management at the app level to adapt to whatever backend I'm using.

[probst]

I am really curious about LiteSync. Any chance you could share a bit on your experiences with it (recognising it’s somewhat off-topic…). Do you run with multiple primaries? What sort of use cases do you reach to it for? Conflict resolution seems a bit simplistic at first glance (from the perspective of someone very into CRDTs), have you experienced any issues as a result of that?

[andersmurphy]

Yes! This is the way.

Honestly, its the key to getting the most out of sqlite. It also allows for transaction batching and various other forms if batching that can massively improve write throughput.

[mickeyp]

I mean, you're not wrong, and that is one way to solve it, but the whole point of a sensibly-designed WAL -- never mind database engine -- is that you do not need to commit to some sort of actor model to get your db to serialise writes.

[sethev]

These are performance optimizations. SQLite does serialize writes. Avoiding concurrent writes to begin with just avoids some overhead on locking.

[mickeyp]

"performance optimisation" --- yeees, well, if you don't care about data integrity between your reads and writes. Who knows when those writes you scheduled really get written. And what of rollbacks due to constraint violations? There's we co-locate transactions with code: they are intertwined. But yes, a queue-writer is fine for a wide range of tasks, but not everything.

It's that we need to contort our software to make sqlite not suck at writes that is the problem.

[sethev]

This is just FUD. The reason SQLite does locking to begin with is to avoid data corruption. Almost every statement this blog post makes about concurrency in SQLite is wrong, so it's little surprise that their application doesn't do what they expect.

>Who knows when those writes you scheduled really get written

When a commit completes for a transaction, that transaction has been durably written. No mystery. That's true whether you decide to restrict writes to a single thread in your application or not.

[catlifeonmars]

> When a commit completes for a transaction, that transaction has been durably written. No mystery. That's true whether you decide to restrict writes to a single thread in your application or not.

Usually this is true but there are edge cases for certain journaled file systems. IIRC sqlite.org has a discussion on this.

[zimpenfish]

> there are edge cases for certain journaled file systems. IIRC sqlite.org has a discussion on this.

Can't currently find it but I guess it comes under the "if the OS or hardware lies to SQLite, what can it do?" banner?

[mickeyp]

You are talking about low level stuff like syncing to the filesystem; that data is journalled and ensuring atomicity is maintained and I am in actual fact not.

Dislocating DML from the code that triggers it creates many problems around ensuring proper data integrity and it divorces consistent reads of uncommitted data that you may want to tightly control before committing. By punting it to a dedicated writer you're removing the ability to ensure serialised modification of your data and the ability to cleanly react to integrity errors that may arise. If you don't need that? Go ahead. But it's not fud. We build relational acid compliant databases this way for a reason

[sethev]

Oh, I think you're picturing executing your transaction logic and then sending writes off to a background queue. I agree, that's not a general strategy - it only works for certain cases.

I just meant that if you can structure your application to run write transactions in a single thread (the whole transaction and it's associated logic, not just deferring writing the end result to a separate thread) then you minimize contention at the SQLite level.

[jitl]

> Who knows when those writes you scheduled really get written

I await the write to complete before my next read in my application logic, same as any other bit of code that interacts with a database or does other IO. Just because another thread handles interacting with the writer connection, doesn't mean my logic thread just walks away pretending the write finished successfully in 0ms.

[ncruces]

SQLite, for the most part, uses polling locks. That means it checks if a lock is available to be taken, and if it's not, it sleeps for a bit, then checks again, until this times out.

This becomes increasingly inefficient as contention increases, as you can easily get into a situation where everyone is sleeping, waiting for others, for a few milliseconds.

Ensuring all, or most, writes are serialized, improves this.