SQLite on iPhone - Techniques for tracking down multithreading-related bugs

Question

Hey guys,

I'm working with an Objective-C wrapper around SQLite that I didn't write, and documentation is sparse...

It's not FMDB. The people writing this wrapper weren't aware of FMDB when writing this code.

It seems that the code is suffering from a bug where database connections are being accessed from multiple threads -- which according to the SQLite documentation won't work if the if SQLite is compiled with SQLITE_THREADSAFE 2.

I have tested the libsqlite3.dylib provided as part of the iPhone SDK and seen that it is compiled in this manner, using the sqlite_threadsafe() routine.

Using the provided sqlite library, the code regularly hits SQLITE_BUSY and SQLITE_LOCKED return codes when performing routines.

To combat this, I added some code to wait a couple of milliseconds and try again, with a maximum retry count of 50. The code didn't contain any retry logic prior to this.

Now when a sqlite call returns SQLITE_BUSY or SQLITE_LOCKED, the retry loop is invoked and the retry returns SQLITE_MISUSE. Not good.

Grasping at straws, I replaced the provided sqlite library with a version compiled by myself setting SQLITE_THREADSAFE to 1 - which according to the documentation means sqlite is safe to be used in a multithreaded environment, effectively serialising all of the operations. It incurs a performance hit, that which I haven't measured, but it ridded the app of the SQLITE_MISUSE happening and seemed to not need the retry logic as it never hit a busy or locked state.

What I would rather do is fix the problem of accessing a single db connection from multiple threads, but I can't for the life of me find where it's occurring.

So if anyone has any tips on locating multithreaded bugs I would be extremely appreciative.

Thanks in advance.

Answer 1

I haven't tested this to see if it works, but you could try wrapping access to the db connection in a function and then using Instruments to log accesses to that function which, IIRC, should let you get a thread ID and a stack trace. That should give you a bit of a handle on where it's coming from. Alternatively you could just set a breakpoint on it but that might take a bit longer.