5

I am using SQLite in my iOS app and I have a lot of saving/loading to do while the user is interacting with the UI. This is a problem as it makes the UI very jittery and slow.

I've tried doing the operations in an additional thread but I don't think this is possible in SQLite. I get the error codes SQLITE_BUSY and SQLITE_LOCKED frequently if I do that.

Is there a way to do this in multithreading without those error codes, or should I abandon SQLite?

VTS12
  • 452
  • 8
  • 22
  • 1
    Have you tried tuning the queries you use and looking at the query plans to see if any indexes are needed ? – Frederick Cheung Jan 04 '12 at 12:56
  • 2
    Are you using transactions when you do multiple writes? You should -- it makes an enormous difference. – Hot Licks Jan 04 '12 at 12:57
  • Frederick Cheung: I am using multiple writes and retrieves. I will look into seeing if indexing will help, but there are a lot of records to insert so I don't think indexing will help. Hot Licks: I am not doing transactions when using multiple writes. Should I do this for every write in the database using the same lock? – VTS12 Jan 04 '12 at 13:09
  • You should bracket a group of writes with a transaction. Otherwise each write is start-transaction/write/end-transaction, and it's the end-transaction that takes all the time. (This is spelled out clearly in the SQLite documentation.) – Hot Licks Jan 04 '12 at 18:01

6 Answers6

3

It's perfectly possible, you just need to serialise the access to SQLite in your background thread.

My answer on this recent question should point you in the right direction I think.

As mentioned elsewhere, SQLite is fine for concurrent reads, but locks at the database level for writes. That means if you're reading and writing in different threads, you'll get SQLITE_BUSY and SQLITE_LOCKED errors.

The most basic way to avoid this is to serialise all DB access (reads and writes) either in a dispatch queue or an NSOperationQueue that has a concurrency of 1. As this access is not taking place on the main thread, your UI will not be impacted.

This will obviously stop reads and writes overlapping, but it will also stop simultaneous reads. It's not clear whether that's a performance hit that you can take or not.

To initialise a queue as described above:

NSOperationQueue *backgroundQueue = [[NSOperationQueue alloc] init];

[backgroundQueue setMaxConcurrentOperationCount:1];

Then you can just add operations to the queue as you see fit.

Community
  • 1
  • 1
paulbailey
  • 5,328
  • 22
  • 35
  • I tried doing it this way and still experienced errors. I'm now using a dispatch queue. – VTS12 Jan 04 '12 at 13:08
  • do you now have everything working properly then? A dispatch queue should be fine too. – paulbailey Jan 04 '12 at 13:11
  • No, after using a dispatch queue I'm constantly getting the SQLITE_BUSY and SQLITE_LOCKED error codes. Also weird behavior like views are being popped early etc. – VTS12 Jan 04 '12 at 13:13
  • We'll need to see the code around your dispatch queue then. As per Apple's docs, "Blocks submitted to a serial queue are executed one at a time in FIFO order". – paulbailey Jan 04 '12 at 13:31
  • The problem is even after the queue has completed, that's when I'm getting SQLITE_BUSY and SQLITE_LOCKED. – VTS12 Jan 04 '12 at 15:53
  • Hold on, how are you getting those errors? Any code that could return them should be executing on the queue. – paulbailey Jan 04 '12 at 15:56
  • Does every database operation need to use the queue? What about things that don't need multithreading? – VTS12 Jan 04 '12 at 16:27
  • The answer is: it depends. As mentioned elsewhere, concurrent reads are fine, but writes lock the whole file. So, all reads have to wait for a write to finish. Queuing all DB operations serially will avoid the SQLite errors, but won't be that efficient. I'll edit my answer to clarify. – paulbailey Jan 04 '12 at 16:37
  • You should create one queue, and then add the DB activity to it as the requests come in. – paulbailey Jan 04 '12 at 16:52
  • that's what I mean...so with that 1 queue, every update should go through it? are reads free to do whatever whenever then? – VTS12 Jan 04 '12 at 17:09
  • This is not ideal, but I think it'll at least solve the problem. Last questions: how do you initialize the NSOperationQueue and set the concurrency to 1? NSOperationQueue *queue = [NSOperationQueue mainThread]? – VTS12 Jan 06 '12 at 13:49
  • That's the queue associated with the main thread. I've added code to my answer. – paulbailey Jan 06 '12 at 14:48
2

Having everything in a dedicated SQLite thread, or a one-op-at-a-time operation queue are great solutions, especially to solve your jittery UI. Another technique (which may not help the jitters) is to spot those error codes, and simply loop, retrying the update until you get a successful return code.

Graham Perks
  • 23,007
  • 8
  • 61
  • 83
1

Put SQLite into WAL mode. Then reads won't be blocked. Not so writes - you need to serialize them. There are various ways how to achieve it. One of them is offered by SQLite - WAL hook can be used to signal that the next write can start.

WAL mode should generally improve performance of your app. Most things will be a bit faster. Reads won't be blocked at all. Only large transactions (several MB) will slow down. Generally nothing dramatic.

Jan Slodicka
  • 1,505
  • 1
  • 11
  • 14
0

Don't abandon SQLite. You can definitely do it in a thread different than the UI thread to avoid slowness. Just make sure only one thread is accessing the database at a time. SQLite is not great when dealing with concurrent access.

Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292
  • 1
    It's fine for concurrent _reads_, but _updates_ lock out everything else (due to the complexity of anything finer-grained than DB-level locking). – Donal Fellows Jan 04 '12 at 12:58
  • My problem is I can't control when the user accesses / writes data. For example, on one view it will read and save the data, but then they could click details and push a new view that will also want to read/write data. Not sure how I can make sure one thread accesses the database at the same time. – VTS12 Jan 04 '12 at 13:12
0

OFF:

Have you checkout: FMDB it is a sqlite Wrapper and is thread safe. I used it in all my sqlite Project.

zaph
  • 111,848
  • 21
  • 189
  • 228
CarlJ
  • 9,461
  • 3
  • 33
  • 47
0

I recommend using Core Data which sits on top of sqlite. I use it in a multithreaded environment. Here's a guide on Concurrency with Core Data.

Jeremy
  • 8,902
  • 2
  • 36
  • 44