6

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..

banu
  • 787
  • 9
  • 24

3 Answers3

9

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).

Rob
  • 415,655
  • 72
  • 787
  • 1,044
  • If I user global queue here will it make difference?? – Mrugesh Tank Apr 12 '16 at 11:36
  • @MrugeshTank - That's a concurrent queue, so that's not going to help here. In fact, multi-thread access is one of those things that ends up requiring some form of synchronization of the database. – Rob Apr 12 '16 at 12:18
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.

Community
  • 1
  • 1
CL.
  • 173,858
  • 17
  • 217
  • 259
  • i want to check if any other write process is run.. if any other task is running..wait until it finish...is it possible? – banu Nov 17 '13 at 11:12
  • 1
    That is what these locks do. – CL. Nov 17 '13 at 11:17
  • Can you please tell me then how to handle db lock – banu Nov 17 '13 at 11:18
  • 1
    What particular problem do you have with the [documentation](http://developer.apple.com/library/ios/DOCUMENTATION/Cocoa/Conceptual/Multithreading/ThreadSafety/ThreadSafety.html)? – CL. Nov 17 '13 at 14:04
  • 1
    This is a problem with your code, not with the documentation, which it appears you have not read and understood. – CL. Nov 21 '13 at 08:36
1

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.

asandroq
  • 575
  • 2
  • 15
  • yes i am using different thread.. my issue is some times i am getting db is locked error. – banu Nov 20 '13 at 02:32
  • You need to open a different DB connection in each thread, or rebuild SQLite in serialised mode (with SQLITE_THREADSAFE=1). – asandroq Nov 20 '13 at 08:10
  • I have `sqlite3_threadsafe() == 2` and put this line of code `sqlite3_config(SQLITE_CONFIG_SERIALIZED)` while performing transactions on different threads concurrently. Is it fine? – mr5 May 14 '16 at 01:16