CODEWITHSUNDEEP
iosmultithreadingsqlitegrand-central-dispatch
Balthazar
Balthazar

Reputation: 245

Using sqlite on iOS with grand central dispatch

I am developing an iOS app which will benefit from moving sqlite queries to the background. The sqlite FAQ says that an sqlite3 handle can not be used in another thread than the one that created it. It goes on to say:

The restriction on moving database connections across threads was relaxed somewhat in version 3.3.1. With that and subsequent versions, it is safe to move a connection handle across threads as long as the connection is not holding any fcntl() locks. You can safely assume that no locks are being held if no transaction is pending and all statements have been finalized.

In other words, to be allowed to move a sqlite3 handle to another thread, I must make sure no transaction is pending, AND I must finalize all prepared statements. The solution to this suggested on StackOverflow is to create one serial queue, and move all sqlite accesses to that queue:

sqlite3 multithreading in objective c

But in Apple's "Concurrency Programming Guide" it says about serial queues:

The currently executing task runs on a distinct thread (which can vary from task to task) that is managed by the dispatch queue.

Doesn't this mean that although no sqlite accesses will be concurrent, they still may be from different (OS) threads? And in that case, does it mean that I would have to finalize all my prepared statements after everything I do? Also, I don't know enough about databases to know how to ensure that "no transaction is pending". I assume I can ignore that part since my database will be static?

Upvotes: 1

Views: 1165

Answers (1)

ipmcc
ipmcc

Reputation: 29926

It appears from looking at the sqlite sources that it makes use of recursive mutexes to guard certain operations. Recursive mutexes are going to rely on the thread id to detect recursive takings of the lock. Their comment:

You can safely assume that no locks are being held if no transaction is pending and all statements have been finalized.

seems like a description of things that would cause that recursive mutex to be held, so yes, trying to perform those operations, broken up into multiple tasks submitted, even to a serial queue, could lead to problems.

You mention that your database will be static. If that's the case, why not just open a handle per task (with the SQLITE_OPEN_READONLY flag set)? If the overhead of creating a handle per task is too great, you could store handles in thread-local storage using pthread_key_create, pthread_setspecific and pthread_getspecific, and then have a destructor (passed in to pthread_key_create) that cleans up the handles when the thread goes away. (i.e. handle per thread) I would expect that having a handle per thread to a readonly database should also allow you to read from the database concurrently.

Upvotes: 1

Related Questions