What about multithreading in Android SQLite?

Question

In my app I've got to implement some UI and Sync service. It runs in the background and updates data. Sync service is not very simple, it uses multithreading.

So, here is my story: When I started developing this app I didn't know anything about sqlite, so I just didn't use any thread synchronization in Java. Result: I got many Exceptions like "SQLiteException: database is locked: BEGIN EXCLUSIVE;"

Then I synchronized all my transactions with regular Java synchronized () {} block. Everything got much better. But I used Cursors for implementing CursorAdapter for my lists. So, sometimes I was getting the same "SQLiteException: database is locked: BEGIN EXCLUSIVE;"

I ended creating a small thread safe sqlite utility which handles all that thread-safe stuff. Also I've got to use something like ArrayAdapter (read all data from Cursor and close it after reading, also synchronize this block) for my UI. So, it is working OK

But, I don't like such way to handle UI, cause UI got really slower with this solution - reading some amount of data from cursor is rather fast, but is slower than using CursorAdapter

So, who got the solution for this question? Thank you

Answer 1

So, finally came out to the solution. Here it is.

I read some forums, google groups and found out that sqlite database should be opened only once. So, I implemented this using singleton.

Also, I implemented some db code to synchronize all write operations (to prevent many threads execute write operations at one time). And I don't care about opening cursors, reading from them.

After some days testing I've got no error reports from my users, so I think this works

In my previous work I opened sqlite database many times across the application, that was the problem.

Answer 2

SQLite implements a exclusive write lock, shared read lock model. This means that you can have concurrent readers active in the database at the same time or a single writer, you can't have both. If you use the WAL logging feature you can have a single writer and multiple readers active in the database at the same time, but you still can't have more than one writer. There is excellent documentation on SQLite concurrency here and here.

You might want to consider taking a look at Berkeley DB. Berkeley DB offers a SQL API that is completely compatible with SQLite. If fact, what we've done is to add the SQLite parser, planner and executor on top of the Berkeley DB storage layer. What this provides to the SQLite application developer is a SQLite-compatible library that has additional scalability, concurrency and reliability (HA), as well as other features. Berkeley DB supports multiple readers and writes concurrently accessing the database. There are two excellent white papers written by Mike Owens, author of "The Definitive Guide to SQLite" that compare Berkeley DB and SQLite (Performance comparison, Behavioral Differences).

Disclaimer: I'm one of the product managers for Berkeley DB, so I'm a little biased. But requests like yours (need more concurrency, scalability, reliability from SQLite) is exactly why we decided to provide a combined library that gives you the best of both worlds.

Answer 3

If you use only one singleton helper class to access the db you don't need to synchronize yourself and you can use the helper from multiple readers/writers because helper class manages synchronization itself.

Look at this post for mor detailed explanation

Answer 4

Use a singleton helper for opening connections.

1) Open as many readable connections as you want, and close then after your are done with it.

2) For writable connections, you should open only a single writable connection.

When attempting to open another writable connection, return the already opened connection. If no writable connection exists, open a new writable connection. Keep a writable_connection counter, and close the writable connection, when all threads are done with it.

I am about to implement this, and will let you all know if it works when I am done.

Answer 5

Well, reading the documentation my previous answer seems incorrect. I have a single(ton) SQLiteOpenHelper, so it seems all connections are the same. Even the readable ones are the same as the writable connections.

So I am going to skip calling getReadableDatabase, and use getWritableDatabase only. Then I am going to keep a counter, to make sure the database get closed once and only once.

Since the SQLiteOpenHelper serializes the writes, everything should be fine this way. I will just have to keep track of the connection, to make sure I dont keep one open at the end.

So in my DbHelper class I now have:

private int activeDatabaseCount = 0;
public synchronized SQLiteDatabase openDatabase() {
    SQLiteDatabase connection = getWritableDatabase(); // always returns the same connection instance
    activeDatabaseCount++;
    return connection; 
}
public synchronized void closeDatabase(SQLiteDatabase connection) {
    activeDatabaseCount--;
    if (activeDatabaseCount == 0) {
        if (connection != null) {
            if (connection.isOpen()) {
                connection.close();
            }
        }
    }
}

Answer 6

Ciaoo: Here the solution:

I tried to explain everything in the code .

import android.app.Activity;
import android.content.Context;
import android.database.Cursor;
import android.os.AsyncTask;
import android.view.View;
import android.view.ViewGroup;
import android.view.ViewTreeObserver;
import android.widget.ScrollView;

import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;

class HelpUI {

   final static class GetDataFromDBInAsyncMode extends AsyncTask<Object, Object, Object> {

      private Context context;
      private String SQL;
      private final int xMin = -X_App.ScreenHeight; // put here your screen height (get it from code)
      private final int xMax = X_App.ScreenHeight * 2; // put here your screen height (get it from code)

      // this is the main view witch go to have all views
      // Use this in onProgressUpdate to add other vies you get in runtime when you get data from database
      private ViewGroup view_container;

      // if you have a scrollview hide views witch isnot visible to user
      private ScrollView scroll_view_container;

      // this workaround make free processors in measureaments, your UI is more fluent
      final ViewTreeObserver.OnScrollChangedListener onScrollChangedListener = () -> {

         if (view_container == null || view_container.getChildCount() == 0) { return; }

         int scrollY = scroll_view_container.getScrollY();

         for (int i = 0; i < view_container.getChildCount(); i++) {

            final View current = view_container.getChildAt(i);
            final int topView = current.getTop(); //container_views.getChildAt(i).getTop();
            final int diffTop = topView - scrollY;

            if ((diffTop > xMin) && (diffTop < xMax)) {
               current.setVisibility(View.VISIBLE);
            } else {

               current.setVisibility(View.INVISIBLE);
            }

         }

      };

      // constructor
      GetDataFromDBInAsyncMode(Context ctx, String mSQL) {
         this.context = ctx;
         this.SQL = mSQL;

         // put here the id of scrollViewContainer
         scroll_view_container = X_App.getRootV().findViewById(R.id.scroll_view_container);
         if (scroll_view_container != null) {
            // add listener on scroll
            scroll_view_container.getViewTreeObserver().addOnScrollChangedListener(onScrollChangedListener);
         }

      }


      @Override
      protected Object doInBackground(Object... objects) {


         // All dirty things go to being in background
         // Your cursor
         final Cursor cursor = X_SqLite.get(X_App.getContext()).GeDataAsCursor(SQL);

         if (cursor != null && cursor.getCount() > 0 && cursor.moveToFirst()) {

            // The magic part
            ScheduledExecutorService giveMeAsync = Executors.newSingleThreadScheduledExecutor();

            // Give time to processor to do her tasks and do you dirty task here
            // 50 millisec is enough per a single task
            giveMeAsync.scheduleAtFixedRate(() -> {
               ViewGroup viewInflated = ((Activity) this.context).findViewById(R.id.icon_view);

               // Give your data from Database
               // Do here all your things but take care some of these need to be done on UI thread (like adding view etc, you can do that on onProgressUpdate)
               final String data1 = cursor.getString(cursor.getColumnIndex("data1"));
               viewInflated.setTag(data1);

               // Send this to UI thread
               publishProgress(viewInflated, false);

               // Here test to cursor if is finish or not
               if (!cursor.moveToNext()) {
                  giveMeAsync.shutdownNow();
                  cursor.close();
                  publishProgress(null, true);
               }

            }, 1, 50, TimeUnit.MILLISECONDS);


         }

         // otherwise if cursor is emty close them
         if (cursor != null && cursor.getCount() == 0) {
            cursor.close();
            publishProgress(null, true);
         }


         return null;

      }


      @Override
      protected void onProgressUpdate(Object... values) {
         final View viewInflated = (View) values[0];
         final boolean isCursorEnded = (Boolean) values[0];

         // Here you is in main thread
         // You can do all what you do with the viewInflated


         if (isCursorEnded) {
            //raise your event to tell to your app reading data is finished
         }

      }


   }


}

Usage:

import android.app.Activity;
import android.os.Bundle;

class MyActivity extends Activity {
   @Override
   protected void onCreate(Bundle savedInstanceState) {
      super.onCreate(savedInstanceState);
      new HelpUI.GetDataFromDBInAsyncMode(this, "Select * FROM YourTable").execute();
   }
} 

Change variables with your variables/objects