CODEWITHSUNDEEP
objective-cdatabasemultithreadingperformancesqlite
Vladyslav Shepitko
Vladyslav Shepitko

Reputation: 73

How to correctly handle sqlite queries in different threads?

I am working on an iPhone app that uses a sqlite database. The app downloads data off of the Internet in a background thread with the UI in the main thread. The background download thread can preform INSERT, UPDATE, and SELECT on the database. The UI layer can also interact with the database by doing UPDATEs and SELECTs. If I don't heavily interact with the UI while the background thread is downloading, everything works fine. However, I start to run into problems when a lot of UPDATEs on the main (UI) thread are performed while the download is going on.

In every query to data base i'm use @synchronized statement, but it doesn't solve problem. When querys (from UI, and from backround thread) are performing in same time, it cause losing data. Updating data in main thread performs fine as in backround too (if they executes separatly from each other).

Data base has sqlite3_threadsafe() == 2; For all query I'm using one connection mayby problem hides in this?

Upvotes: 0

Views: 333

Answers (2)

CL.
CL.

Reputation: 180070

SQLite's threading mode protects only individual function calls. However, each connection has a single transaction, so if you have multiple threads accessing the database at the same time, they will still interfere with each other. You should use multiple connections instead.

If you want to read and write at the same time, consider using WAL mode.

Upvotes: 0

battlmonstr
battlmonstr

Reputation: 6290

According to the docs you should call sqlite3_config() function and select a threading model. I'd recommend to try SQLITE_CONFIG_SERIALIZED first.

Also make sure that you don't use SQLITE_OPEN_NOMUTEX flag to sqlite3_open_v2().

In general a good practice is to serialize all writes to a single thread. Kind of "one writer - many readers" model. I would advice to even avoid all DB accesses from the main thread, although that might be complicated depending on the amount of the legacy code you have.

Upvotes: 1

Related Questions