Read sqlite data when a transaction is running (Android)
I solved the problem.
I followed this steps.
To solve the problem of database locking and multi-threading use of the database (Based in http://touchlabblog.tumblr.com/post/24474750219/single-sqlite-connection):
1) I'm using only one SQLiteOpenHelper (singleton).
2) Never close the database.
To be able to read and write without blocking my queries I followed this steps:
Use database.enableWriteAheadLogging()
(only api>=11), my mistake was that you have to enable this mode in all connections, not only in your transaction or your writings, so I added the following code to my openHelper class in "onOpen" method. Based in this code of Mozilla in github (https://github.com/mozilla/mozilla-central/blob/9f2b8297b99d9d28038256b4f92a5aaa941749f1/mobile/android/base/db/TabsProvider.java).
@SuppressLint("NewApi")
@Override
public void onOpen(SQLiteDatabase db) {
// From Honeycomb on, it's possible to run several db
// commands in parallel using multiple connections.
if (Build.VERSION.SDK_INT >= 11) {
try{
db.enableWriteAheadLogging();
}catch(Exception e){
Log.e("onOpen", e.getMessage());
}
}
}
With this solution I solved my problem of blocking reads while I'm updating some tables, but other updates are blocked too. If you want to solve this last issue, as @commonsware says:
using yieldIfContendedSafely()
inside your transaction you will give to other threads a chance to work with the database. I haven't seen any difference using this method with beginTransaction
or
beginTransactionNonExclusive
.
Another interesting read is: What are the best practices for SQLite on Android?
I think you should use synchronization concept to avoid issues which you are facing.
If your data-loading work is not just one massive SQL transaction, use yieldIfContendedSafely()
to allow your other threads to read the database at various points. So, for example, if you are doing some sort of bulk data load, and you are doing a transaction every 100 inserts, call yieldIfContendedSafely()
on every pass of the every-100-inserts loop to give your other threads a chance to work with the database.