I'm using SQLite as a sort of "structured logging" tool. I have a Program Under Test, written in C++, that logs tracing information and a Test Driver program, written in C#, which accesses this logging database to determine if the program under test is working correctly.

I'm running into problems where during this producer-consumer process, either side may start to get a SQLITE_BUSY (5) return code, which neither application currently handles well. (The C function for translating return codes says the message is, "the database is locked," however the actual code is 5 not 6.)

I haven't tried adding a SQLITE_BUSY handler yet - one problem is I'm not sure it's even possible to do so from the C# side, or if the library I'm using may already have its own. It wouldn't do much good to fix only one side, when either is failing with SQLITE_BUSY. I'm also not sure if I'm making an unwarranted assumption about the circumstances under which db connections are closed when I dispose their owning objects.

HOWEVER - I'm writing this forum post to also ask if this SQLite use-case is even a good idea in general (architecturally). Or if I'm forever going to be chasing my tail trying to avoid problems with this setup.

Whatever the solution, I also need the producer process to never get "stuck" waiting for its turn to insert rows of logging information. (While not technically real-time, processing the serial communication does have time-out limits.) I was under the impression this would not happen with one or multiple readers, as long as there was only one writer. That seems to not be the case? Or do I have a bug?

I could implement a log spooler on a background thread in the Program Under Test so that trace calls never wait on database I/O, but I don't want to go to that trouble if there's something wrong, architecturally, with using SQLite as an ersatz interprocess communication (IPC) in this way.

In fact, I actually HAVE an IPC implementation between the Test Driver and the Program Under Test; what SQLite gives me is the ability to define new kinds of log data and analysis using DB schema changes and views instead of having to come up with an ad hoc programmatic interface for every type of tracing data I might want to share or analyze. So one answer could be, stop using SQLite for this and just pass the data through the IPC channel.

Alternatively I could use this IPC channel to arrange for the Test Driver and the Program Under Test to explicitly co-ordinate all of their writes and reads to the SQLite db file. Again though, before I go to that trouble I want to know if I'm approaching the problem from the correct angle. If SQLite should be able to coordinate this producer-consumer dance itself but for something else I'm doing wrong, I'd hate to reinvent the wheel.

Are you aware of WAL mode?

By default, i.e. JOURNAL mode, SQLite writers block readers, and vice-versa, thus the SQLITE_BUSY you are getting, on both sides.

The only drawback of WAL, is when to checkpoint, OTOH.

You can use pragma busy_timeout at the SQL level no matter what language your application is written in.

Yes, in the default rollback journal mode SQLITE_BUSY is expected on both the reader and writer side in certain circumstances. The reasons are:

1. A writer cannot safely update the database file while there is an active reader. By "safely" I mean that no filesystem enables this kind of update to happen atomically, so we risk causing spurious values or perceived corruption if we write to the file while readers are active
2. To prevent writer starvation, new readers are not allowed to start once a writer is ready to commit its changes to the database file

The key question is how long these windows are. With well behaved processess they should be small, on the order of 100s of milliseconds. "Well behaved" here means:

(a) read transactions are short-lived (any lengthy transaction will significantly hold up the writer) (b) write transactions are not exhausting sqlite's in-memory page cache (which will result in a cache spill and lock readers out for longer than usual)

SQLite's default behaviour is to return SQLITE_BUSY instantly in one of these scenarios, but you can also use eg. sqlite3_busy_timeout(500) to install a busy handler which will transparently retry for 500 milliseconds. After this you will only see SQLITE_BUSY if a reader/writer has to wait more than 500 milliseconds OR a transaction tries to obtain a write lock while another writer is already active.

Note that for small write transactions there is a significant "constant time" component due to the synchronous I/O involved, so batching multiple logging events to throttle the frequency of writes will reduce contention.

As another reply said, if these constraints don't suit your case then look into WAL mode :)

Thanks everyone for your responses; I did not know about WAL mode, and all of the information you've provided has been helpful. I hadn't responded until now because I was creating a test case of a stripped down pair of programs that just exercises the data producing and consuming code.

I had difficulty reproducing the exact same error, but I get plenty of other related conflicts. One thing I realized on further examination is my producer process opens a connection and transaction immediately, and then holds that connection open continuously. It just commits and starts a new transaction every N log messages. I think this was four-years-ago-me's idea of optimizing insert performance, back when there was only logging activity and no log reader process.

Enabling WAL mode changed the behavior of my test case but wasn't a panacea - perhaps not surprising when the writing process is holding open connections and transactions. I kind of got different results (different interactions) every time I ran the pair of processes. One thing that isn't clear to me is whether WAL mode must be enabled on all connections, or if the read-only connection will detect that the -wal file exists and automatically use it.

Rather than waste time troubleshooting exactly what's going wrong in the current strategy, it looks like I'm going to need to implement log spooling anyway, so that I can write log messages in small batches with a db connection that's only opened and closed for a brief time to do one transaction. I don't see a downside to also using WAL mode with that strategy(?).

the writing process is holding open connections and transactions.

…and transactions? Why ever for? Build up a transaction and drop it on the database. Done. Don't build it up over long periods.

a db connection that's only opened and closed for a brief time to do one transaction

No, don't do that. Every time you open the database, you force SQLite to re-parse the schema, which is costly. You also invalidate any prepared statements, which you should be using given your goal of performance.

SQLite is designed with the assumption that, if you need the database through the program's whole runtime, you open the connection at the start of the program and close it at the end.