SQLite Connections & Locking

Question

I'd like to access a SQLite database from 2 different threads, thereby using 2 different connections to the database. Both threads will mainly perform reads from the DB and will write to the DB only occasionally. If I feel the odds are against both threads writing to the DB at the same time, should I feel safe that I should not have any issues in this scenario ?

Answer 1

SQLite is threadsafe and, with recent versions, you can share a single connection among threads. That said, the SQLite FAQ states "Threads Are Evil" (I don't think they mean this in the context of SQLite, but as a general statement).

SQLite has locking mechanisms so that even if a second instance tries to get a write lock on the database it will be queued until existing locks are finished, so even if both threads are writing SQLite should accommodate you. The FAQ suggests that it is generally not safe to use multiple connections from different processes on network file systems due to poorly implemented locking in the file system, but I don't think that warning applies to your usage.

Answer 2

SQLite is explicitly not thread safe, therefore you can expect data corruption if you do this.

Use mutex locking to make sure that both threads cannot access the database at the same time, or spend hours trying to track down phantom data corruption bugs that only rarely happen and in a hard to reproduce fashion.

http://sqlite.org/faq.html#q5

Answer 3

Not quite right. Please see my long reply here:

What are the best practices for SQLite on Android?

You won't corrupt your database, but if two different threads, with two different connections, try to write to the db at the same time, you will have problems. One will "lose". They will not wait to be run in order. If you call 'insert' instead of 'insertOrThrow', you will not even get an exception. You just won't write to the DB.

Here's how Sqlite, in Android, works. Each SqliteOpenHelper instance has 1 connection to the database. It does not matter how many times you call 'getRead/WriteableDatabase'. One helper, one connection. Also, the Android sqlite connection code implements its own thread locking, so if you use the same SqliteOpenHelper, and by extension, the same connection, you'll be fine.

If, however, you use more than one helper, you can be in a bad way.

I suspect you could have multiple threads reading, and 1 writing, and come out OK, but I haven't tested this.

Are you using multiple threads for write performance? If so, I'd suggest simply optimizing your use of "transactions". If you do multiple independent writes, its VERY slow. If you wrap them all in a batch, its very fast (relatively). I suspect this is because with each independent write, Android flushes to disk (which is very slow). If you do them in a bunch, the changes are all made in 1 write.

As for maintaining 1 helper instance, here's a recent blog post of mine about it:

http://touchlabblog.tumblr.com/post/24474750219/single-sqlite-connection

I had written some fairly complex reference counting logic as part of my early implementation of OrmLite Android port, but I don't think that's all necessary at this point.

http://touchlabblog.tumblr.com/post/24474455802/ormlite-for-android

For completeness in links, my blog post about sqlite locking and multiple connections:

http://touchlabblog.tumblr.com/post/24474398246/android-sqlite-locking