Geek Answers Handbook

SQLite Connections and Blocking

I would like to access the SQLite database from 2 different threads, thereby using 2 different database connections. Both threads will mainly read from the database and will only be written to the database occasionally. If I feel that it is likely that both threads will be written to the database at the same time, should I feel safe that I should not have problems in this scenario?

+8
android sqlite locking
source share
3 answers

SQLite is thread safe, and in recent versions you can use one connection between threads. However, the SQLite FAQ says: "Threads Are Evil" (I don’t think they mean it in the context of SQLite, but as a general statement).

SQLite has locking mechanisms, so even if the second instance tries to get a write lock on the database, it will wait in line until the existing locks are finished, so even if both threads write SQLite, you should place you. Frequently asked questions suggest that it is generally unsafe to use multiple connections from different processes on network file systems due to a poorly implemented lock on the file system, but I do not think this warning applies to your use.

+6
source share

Not quite right. Please see my long answer here:

What are the best practices for SQLite on Android?

You will not damage your database, but if two different streams, with two different connections, try to write to db at the same time, you will have problems. One will lose. They will not wait to be put in order. If you call "insert" instead of "insertOrThrow", you will not even get an exception. You simply will not write to the database.

This is how Sqlite works in Android. Each instance of SqliteOpenHelper has 1 database connection. It doesn't matter how many times you call "getRead / WriteableDatabase". One assistant, one connection. In addition, the sqlite connection code for Android implements its own thread blocking, so if you use the same SqliteOpenHelper, and, by extension, the same connection, you will be fine.

If, however, you use more than one assistant, you may be in poor condition.

I suspect that you may have multiple read streams, and 1 write, and exit OK, but I have not tested this.

Do you use multiple threads for recording performance? If so, I would suggest simply optimizing the use of "transactions." If you make several independent recordings, it is very slow. If you complete them all in batch mode, it will be very fast (relatively). I suspect that this is due to the fact that with each self-recording Android is flushed to disk (which is very slow). If you do them together, all changes are made in 1 record.

As for maintaining 1 sub instance, here's a recent post from my blog about it:

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

I wrote a pretty complicated link counting logic as part of my early implementation of the OrmLite Android port, but I don’t think that all this is necessary at the moment.

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

For completeness of links, my blog post about sqlite blocking and multiple connections:

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

+9
source share

SQLite is clearly not thread safe, so you can expect data corruption if you do.

Use a mutex lock to make sure that both threads cannot access the database at the same time, or spend hours searching for phantom errors when data failures are rare and difficult to reproduce.

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

0
source share

More articles:


All Articles