I believe one of the most common SQLite gotchas (at least when deploying it for a web application) concerns the need to use BEGIN IMMEDIATE TRANSACTION for write transactions in order to avoid the SQLITE_BUSY error.

If I'm wrong about the details here, please correct me!

My understanding is that SQLite defaults to starting all transactions as read transactions, and then upgrades them to write transactions half way through if they turn out to involve an insert/update/other write operation.

BUT... this can conflict with SQLite's locking mechanism, because if another transaction grabs a write lock in between the start of that transaction and the point at which it "upgrades" the result will be a SQLITE_BUSY error.

You can avoid this problem by starting any transaction that you know will be a write with:

BEGIN IMMEDIATE TRANSACTION

Here's the problem: it's really, really hard to figure this out! Searching for SQLITE_BUSY often doesn't help solve the problem.

The best explanation of this problem I've seen is in https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-the-how-and-why-of-optimal-performance/

Here's where Ruby on Rails implemented this fix: https://github.com/rails/rails/pull/50371

It would be really useful if the SQLite documentation had a section describing this issue in as much detail as possible, designed to be easily found through search engines - especially for SQLITE_BUSY since that's the error code which tends to indicate this underlying problem.