How to handle multiple thread access the sqlite3 with out dblocked error

Question

In my application, background sqlite thread is running... in background thread it fetching data from web service and insert or update data into database. if user insert or delete data from foreground,sometimes i am getting crash it display "sqlite dblocked". but it does not return sqlite busy error.

And i tested thread safe mode

     NSLog(@" sqlite3_threadsafe() = %d", sqlite3_threadsafe());

it display threadsafe is 2.

I want to check if any other db is writing data, if db is writing data.. i want to write data after previous write task is finished.

how to handle these situation..

Answer 1

You can use lock (such as those enumerated in the Synchronization section of the Threading Programming Guide) or you can use a dedicated serial queue. For example, create a queue:

@property (nonatomic, strong) dispatch_queue_t databaseQueue;

Instantiate it:

self.databaseQueue = dispatch_queue_create("com.company.app.database", 0);

And whenever you want to interact with the database, you can do

dispatch_sync(self.databaseQueue, ^{
    // do your database activity here
});

If you want to simplify your life, the FMDB library has a FMDatabaseQueue object that does much of this for you (as well as greatly simplifying your database interaction in general).

Answer 2

The thread safe mode prevents crashed when multiple threads access the same connection, but it cannot prevent multiple connections from interfering with each other.

You should use a common lock to protect all database transactions; use either a mutex or the @synchronized directive.

Answer 3

From the SQLite docs:

Use the SQLITE_THREADSAFE compile-time parameter to selected the threading mode. If no SQLITE_THREADSAFE compile-time parameter is present, then serialized mode is used. This can be made explicit with -DSQLITE_THREADSAFE=1. With -DSQLITE_THREADSAFE=0 the threading mode is single-thread. With -DSQLITE_THREADSAFE=2 the threading mode is multi-thread.

So it seems you have SQLite in multithreaded mode, but not serialised. In this mode, you cannot use the same database connection from different threads, but you will be safe if you open a different connection in each thread.