Synchronize sqlite database in objective-C

Question

I have a view in which I am inserting data in a background thread to sqlite database coming from server. Now when user taps and navigate to another view in which I have to read the contents from this database, but I am not able to fetch the contents as the database is already open in background thread of last view and it gives error "database is locked".

NOTE: both the operations are in different Tables i.e I am writing in different table and reading from other table.

Does sqlite support multi threading, If yes then how can I remove the lock from database?

Seva Alekseyev · Accepted Answer · 2012-01-25 19:54:57Z

3

SQLite supports multithreading. You can share your connection handle/object between threads, they'll sync their access nicely.

EDIT: pass the sqlite3 object (the one that sqlite3_open() returns as the second parameter) to the thread instead of reopening the database in the thread. Something like this:

sqlite3 *MyDatabase; //Initialized somewhere
NSArray *DataForThread = [NSArray arrayWithObjects:
    request,
    [NSValue valueWithPointer: MyDatabase],
    nil];

[self performSelectorInBackground:@selector(processResponseInBackground:)        withObject:DataForThread];

Inside the thread, recover the SQLite3 object like this:

sqlite3 *MyDatabase = [[ThreadData objectAtIndex:1] pointerValue];

edited Jan 25, 2012 at 19:54

answered Jan 25, 2012 at 16:54

Seva Alekseyev

61.8k25 gold badges173 silver badges297 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Manish Agrawal Over a year ago

@Seve It would be nice if you provide some piece of code. Thanks

Seva Alekseyev Over a year ago

You first. How do you open the database and start the background thread?

Manish Agrawal Over a year ago

In the first view when data comes from server insert the data in background using '[self performSelectorInBackground:@selector(processResponseInBackground:) withObject:request];' and when I navigate from this view it still uses the database and database is locked for another view

Seva Alekseyev Over a year ago

How do you connect to the database? Are you using SQLite's C API, or Core Data, or what? The general idea is to pass the connection to the thread as a part of the withObject: parameter. But I don't know what your connection object is.

Manish Agrawal Over a year ago

I am using sqlite'c C API and open the database using sqlite3_open commond. I dont what connection object you are talking?

Josh Vickery · Accepted Answer · 2012-05-25 17:17:45Z

3

SQLite does support multi-threading, but it does not support simultaneous access to the database from multiple threads.

If you are accessing SQLite from multiple threads in Objective-C you should use some kind of locking mechanism to coordinate access to the database handle. One option is the @synchronized keyword.

As for reading from the database at the same time that inserts are happening on a different thread, you'll need to get creative.

One option is to put the two tables into two different databases and create two different connections to them.

Further reading on the nature of using SQLite in a multi-threaded environment:

http://www.sqlite.org/threadsafe.html

http://www.sqlite.org/faq.html#q6

answered May 25, 2012 at 17:17

Josh Vickery

9328 silver badges10 bronze badges

Comments

Esses77 · Accepted Answer · 2014-01-11 23:26:09Z

Sqlite supports multi-threading in the sense of "writing so fast to avoid concurrency problems". The reality is that there shouldn't be lock troubles in case of read only queries but if you want to write too... you could meet the SQLITE_BUSY error. You can avoid it in several ways:

either doing all queries into the same thread (but if you could do it...no questions on stackoverflow :-) )
or, as mentioned above, split database tables into two or more files (grouping together tables with less concurrency accesses or with read only accesses) and creating for each file a different db connection
or, as mentioned above, using a thread synchronizing approach on each method (or block of code) that does the query (to be sure that db will be locked when you start the transaction use "BEGIN IMMEDIATE TRANSACTION")
or, handle the sqlite:busy error with the 2 callbacks provided by the c api, "sqlite_busy_handler" and "sqlite_busy_timeout"
or, after receiving lock errors waiting for 1-2 seconds and retry (better approach the point above i think)

I prefer to reduce concurrency splitting db and then if needed synchronizing threads accesses to query code. It's just your choice...

Collectives™ on Stack Overflow

Synchronize sqlite database in objective-C

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related