10

I'm trying to determine if my sqlite access to a database is thread-safe on iOS. I'm writing a non App Store app (or possibly a launch daemon), so Apple's approval isn't an issue. The database in question is the built-in sms.db, so for sure the OS is also accessing this database for reading and writing. I only want to be able to safely read it.

I've read this about reading from multiple processes with sqlite:

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

I understand that thread-safety can be compiled out of sqlite, and that sqlite3_threadsafe() can be used to test for this. Running this on iOS 5.0.1 

int safe = sqlite3_threadsafe();

yields a result of 2. According to this, that means mutex locking is available. But, that doesn't necessarily mean it's in use.

I'm not entirely clear on whether thread-safety is dynamically enabled on a per connection, per database, or global basis.

I have also read this. It looks like sqlite3_config() can be used to enable safe multi-threading, but of course, I have no control, or visibility into how the OS itself may have used this call (do I?). If I were to make that call again in my app, would it make it safe to read the database, or would it only deconflict concurrent access for multiple threads in my app that used the same sqlite3 database handle?

Anyway, my question is ...

can I safely read this database that's also accessed by iOS, and if so, how?

Community
  • 1
  • 1
Nate
  • 31,017
  • 13
  • 83
  • 207

2 Answers2

12

I've never used SQLite, but I've spent a decent amount of time reading its docs because I plan on using it in the future (and the docs are interesting). I'd say that thread safety is independent of whether multiple processes can access the same database file at once. SQLite, regardless of what threading mode it is in, will lock the database file, so that multiple processes can read from the database at once but only one can write.

Thread safety only affects how your process can use SQLite. Without any thread safety, you can only call SQLite functions from one thread. But it should still, say, take an EXCLUSIVE lock before writing, so that other processes can't corrupt the database file. Thread safety just protects data in your process's memory from getting corrupted if you use multiple threads. So I don't think you ever need to worry about what another process (in this case iOS) is doing with an SQLite database.

Edit: To clarify, any time you write to the database, including a plain INSERT/UPDATE/DELETE, it will automatically take an EXCLUSIVE lock, write to the database, then release the lock. (And it actually takes a SHARED lock, then a RESERVED lock, then a PENDING lock, then an EXCLUSIVE lock before writing.) By default, if the database is already locked (say from another process), then SQLite will return SQLITE_BUSY without waiting. You can call sqlite3_busy_timeout() to tell it to wait longer.

Jordan Miner
  • 2,034
  • 17
  • 19
  • When you say "*it should still, say, take an EXCLUSIVE lock before writing*", do you mean that SQLite will automatically do this for me? Or that I (or the iOS SMS framework) am responsible for this? For example, do I (or iOS) need to issue a `BEGIN EXCLUSIVE TRANSACTION;` statement before writing? – Nate Jul 07 '12 at 03:26
  • I added an edit. `BEGIN EXCLUSIVE TRANSACTION` is for taking an EXCLUSIVE lock at the beginning of a transaction. By default, SQLite waits as long as it can. From the docs: *Note that the BEGIN command does not acquire any locks on the database. After a BEGIN command, a SHARED lock will be acquired when the first SELECT statement is executed. A RESERVED lock will be acquired when the first INSERT, UPDATE, or DELETE statement is executed.* ... – Jordan Miner Jul 07 '12 at 09:18
  • ... *No EXCLUSIVE lock is acquired until either the memory cache fills up and must be spilled to disk or until the transaction commits. In this way, the system delays blocking read access to the file file until the last possible moment.* Also, even if you don't use `BEGIN TRANSACTION`, all statements are still in an implicit transaction. – Jordan Miner Jul 07 '12 at 09:21
  • Many thanks. One last question, and then the bounty is yours! I **just** found [this question](http://stackoverflow.com/a/1063768/119114). Do you think the answer given is incorrect (i.e. you don't **need** an explicit `BEGIN` to start a transaction)? – Nate Jul 07 '12 at 09:31
  • From the docs: *An implicit transaction (a transaction that is started automatically, not a transaction started by BEGIN) is committed automatically when the last active statement finishes.* So you don't need a BEGIN for a transaction. You can pass a plain INSERT statement to `sqlite3_exec()`, and it will run as an implicit transaction. But that answer might just be giving advice to use `BEGIN IMMEDIATE` instead of just `BEGIN`. That would make your code cleaner, but you don't have to. ... – Jordan Miner Jul 07 '12 at 10:52
  • ... If you use BEGIN IMMEDIATE and the database is locked by another process, SQLite will return SQLITE_BUSY at the beginning of the transaction. Then you can try again. If you use a BEGIN and the database is locked, then SQLite will return SQLITE_BUSY at some later time. It could be when you try to commit, or it could be an earlier statement. If it is an earlier statement, you should rollback the transaction. If it is when you try to commit, the transaction remains active, and you can try to commit again. So more logic is needed with BEGIN than BEGIN IMMEDIATE. ... – Jordan Miner Jul 07 '12 at 10:52
  • ... Alternatively, you could use no explicit transactions, and if you get SQLITE_BUSY, just retry that statement. From the docs: *SQLITE_BUSY means that the database engine was unable to acquire the database locks it needs to do its job. If the statement is a COMMIT or occurs outside of an explicit transaction, then you can retry the statement. If the statement is not a COMMIT and occurs within an explicit transaction then you should rollback the transaction before continuing.* – Jordan Miner Jul 07 '12 at 10:53
  • Here is a clearer quote from the docs I just found: *No changes can be made to the database except within a transaction. Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last query finishes.* – Jordan Miner Jul 07 '12 at 10:58
2

I don't think any of this is news to you, but a few thoughts:

In terms of enabling multi-threading (either serialized or multi-threaded), the general counsel is that one can invoke sqlite3_config() (but you may have to do a shutdown first as suggested in the docs or as discussed on SO here) to enable the sort of multi-threading you want. That may be of diminished usefulness here, though, where you have no control over what sort of access iOS is requesting of sqlite and/or this database.

Thus, I would have thought that, from an academic perspective, it would not be safe to read this system database (because as you say, you have no assurance of what the OS is doing). But I wouldn't be surprised if iOS is opening the database using whatever the default mode is, so from a more pragmatic perspective, you might be fine.

Clearly, for most users concerned about multi-threaded access within a single app, the best counsel would be to bypass the sqlite3_config() silliness and just simply ensure coordinated access through your own GCD serial queue (i.e., have a dedicated queue through which all database interactions go through, gracefully eliminating the multi-thread issue altogether). Sadly, that's not an option here because you're trying to coordinate database interaction with iOS itself.

Community
  • 1
  • 1
Rob
  • 415,655
  • 72
  • 787
  • 1,044
  • thanks for your thoughtful and well written response. first, about shutdown ... does `sqlite3_shutdown()` shutdown sqlite for the entire OS, because if so, that seems to also be another interprocess thread-safety problem. i guess one unsafe operation might be better than doing something unsafe every time I want to read the .db, but maybe not? – Nate Jun 16 '12 at 21:03
  • and secondly, GCD queues seem like a good way to coordinate access between multiple threads of my own, but that's not my problem (in fact, my app will probably only ever use one thread of its own for sql operations). how can I force iOS to also use the queues? or am I missing something (usually, when I ask that question the answer is "yes"!) – Nate Jun 16 '12 at 21:05
  • @Nate Regarding your first question re sqlite3_shutdown(), I can't answer that. I was just quoting those other references. It doesn't strike me as a good idea, but I can't say. – Rob Jun 16 '12 at 21:21
  • @Nate Regarding your second question, you don't need to worry about the rest of iOS. You just need to coordinate your own sqlite activity against your own database (and you might not need to worry about that given that you say that all of your db activity will be done in a single thread). There's no need to "force iOS to also use the queues." – Rob Jun 16 '12 at 21:23
  • why don't i need to worry about iOS? it's reading and writing to the same sms.db file. i'm not saying you're wrong, but an explaination of **why** it's safe (if it is) is really the crux of the question. – Nate Jun 16 '12 at 21:57
  • Oh, no, I'm quite wrong, so feel free to say it. I had entirely glossed over the fact that you were trying to read a system DB as it's anathema to the sandboxed world in which I live. I was entirely fixated in an app's different queues accessing the same DB. Regarding SMS.db, you can hope that doing sqlite3_config will do it, but you obviously can't be confident of what iOS itself is doing. – Rob Jun 17 '12 at 00:04
  • No problem. I think it *could* be safe to access the db, **if** I knew that iOS either didn't call `sqlite3_config()` and accepted the default mode (multi-threading), or if it called `sqlite3_config()` and passed it `SQLITE_CONFIG_MULTITHREAD`. But, still looking for a way to determine that ... – Nate Jun 17 '12 at 01:04
  • it's **totally** up to you, but would you consider removing your answer? It sounds like it was predicated on you understanding my problem to be different than what it was. Not that you gave incorrect information, just that the problem was different. I know that I personally am more likely to answer a question if I see that it's currently "unanswered", as opposed to some lazy questioner who just doesn't want to click the Accept button. Anyway, your call, just thought I'd ask as I'm not getting much of a response yet. – Nate Jun 25 '12 at 09:22
  • @Nate I have revised my answer to focus on the key question, cross process db interoperability. Even though I know I am not answering your question (not sure there's a good answer to how to access a system db for which Apple doesn't provide an interface), I think there's value in acknowledging the two common approaches to multi-threaded sqlite, e.g. `sqlite3_config()` and GCD serial queues, because people might stumble across your question as they look for standard multi-thread sqlite access questions). But, if you feel strongly about me removing my answer, I'm willing to do so. Let me know. – Rob Jun 25 '12 at 17:08
  • No worries. I've upvoted, but not yet accepted your answer. It's useful information now, pertinent to the question, but it's possible that someone else may have some insider knowledge about iOS's configuration, or be a crazy sqlite power user, that has additional input. I'll accept your answer if after a while, there's no other data. I haven't asked any other questions on SO, so I won't forget :) – Nate Jun 26 '12 at 04:07