Read sqlite data when a transaction is running (Android)

Question

I need to read some data from a database meanwhile I'm loading some data in another thread with a transaction.

All my threads to read other tables are stopped until the transaction in other thread is finished.

I need to be able to read info from the database without worrying about the other thread.

I've read a lot of info about sqlite, android...but nothing is working, always my query to read parameters is blocked.

I've followed this advices as @KevinGalligan says in this thread (What are the best practices for SQLite on Android?) resolving locks and other problems.

1) I'm using only one SQLiteOpenHelper (singleton)

2) I've never closing the database

I've tried:

start the transaction with:

database.execSQL("begin immediate transaction");

or

database.beginTransactionNonExclusive();

instead

database.beginTransaction();

Not working, query blocked.

I've read about WAL(database.enableWriteAheadLogging()), but I need to support api 9.

Any other solutions to read meanwhile a transaction is updating some tables? I don't care if my info is deprecated, it's more important to not block my threads.

Answer 1

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?

Answer 2

I think you should use synchronization concept to avoid issues which you are facing.

Answer 3

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.