0

I met this problem in my own db framework. I have searched this problem in StackOverflow and tried many methods as mentioned, but this problem has not been solved. It worked well in single thread, the problem usually occor in App Widget, when I try to update the list items.

Here is the explanation:

1.this is the SQLiteOpenHelper used to get SQLiteDatabase object, as you see it's singleton:

 public class PalmDB extends SQLiteOpenHelper {

    public static PalmDB getInstance(final Context context){
        if (sInstance == null){
            synchronized (PalmDB.class) {
                if (sInstance == null) {
                    sInstance = new PalmDB(context.getApplicationContext());
                }
            }
        }
        return sInstance;
    }
}

2.Next is the store used to query and save data to database. It is abstract:

public abstract class BaseStore<T extends Model> {
    private PalmDB mPalmDatabase = null;

    @SuppressWarnings("unchecked")
    public BaseStore(Context context) {
        this.mPalmDatabase = PalmDB.getInstance(context);
    }

    protected SQLiteDatabase getWritableDatabase() {
        return mPalmDatabase.getWritableDatabase();
    }

    protected synchronized void closeCursor(Cursor cursor) {
        if (cursor == null || cursor.isClosed()) return;
        try {
            cursor.close();
        } catch (Exception e){
            LogUtils.d("Couldn't close cursor correctly");
        }
    }

    public synchronized List<T> get(String whereSQL, String orderSQL, Status status, boolean exclude) {
        Cursor cursor = null;
        List<T> models = null;
        SQLiteDatabase database = getWritableDatabase();
        try {
            cursor = database.rawQuery(" SELECT * FROM " + tableName
                        + " WHERE " + BaseSchema.USER_ID + " = " + userId
                        + (TextUtils.isEmpty(whereSQL) ? "" : " AND " + whereSQL)
                        + (status == null ? "" : " AND " + BaseSchema.STATUS + (exclude ? " != " : " = ") + status.id)
                        + (TextUtils.isEmpty(orderSQL) ? "" : " ORDER BY " + orderSQL),
                new String[]{});
            models = getList(cursor);
        } finally {
            closeCursor(cursor);
        }
        return models;
    }

    protected synchronized List<T> getList(Cursor cursor){
        LogUtils.d(this); // print the hash code of this object
        LogUtils.d(Thread.currentThread());
        List<T> models = new LinkedList<>();
        if (cursor != null && !cursor.isClosed() && cursor.moveToFirst()){ // exception here
            do {
                models.add(getModel(cursor));
            } while (cursor.moveToNext());
        } else if (cursor != null && cursor.isClosed()) {
            LogUtils.e("cursor is closed : " + cursor);
        }
        return models;
    }

    private T getModel(Cursor cursor) {
        T model = StoreHelper.getBaseModel(cursor, entityClass);
        fillModel(model, cursor);
        return model;
    }

3.Then I override this class for concrete object like this. As you can see that it is also singleton:

public class MindSnaggingStore extends BaseStore<MindSnagging> {
    private static MindSnaggingStore sInstance = null;

    public static MindSnaggingStore getInstance(Context context){
        if (sInstance == null){
            synchronized (MindSnaggingStore.class) {
                if (sInstance == null) {
                    sInstance = new MindSnaggingStore(context.getApplicationContext());
                }
            }
        }
        return sInstance;
    }

    private MindSnaggingStore(Context context) {
        super(context);
    }
}

It worked fine in activities and fragments, but when I include it in App Widget. When I try to remove any entities, usually twice, then it crashed.

Here is the exception:

02-05 20:01:58.497 19317-19330/? E/AndroidRuntime: FATAL EXCEPTION: Binder:19317_1
                                               Process: me.shouheng.notepal, PID: 19317
                                               java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed.
                                                   at android.database.sqlite.SQLiteConnectionPool.throwIfClosedLocked(SQLiteConnectionPool.java:962)
                                                   at android.database.sqlite.SQLiteConnectionPool.waitForConnection(SQLiteConnectionPool.java:599)
                                                   at android.database.sqlite.SQLiteConnectionPool.acquireConnection(SQLiteConnectionPool.java:348)
                                                   at android.database.sqlite.SQLiteSession.acquireConnection(SQLiteSession.java:894)
                                                   at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:834)
                                                   at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
                                                   at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:143)
                                                   at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:132)
                                                   at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:219)
                                                   at android.database.AbstractCursor.moveToFirst(AbstractCursor.java:258)
                                                   at me.shouheng.notepal.provider.BaseStore.getList(BaseStore.java:330)
                                                   at me.shouheng.notepal.provider.BaseStore.get(BaseStore.java:127)
                                                   at me.shouheng.notepal.provider.BaseStore.get(BaseStore.java:101)
                                                   at me.shouheng.notepal.widget.desktop.ListRemoteViewsFactory.getNotes(ListRemoteViewsFactory.java:72)
                                                   at me.shouheng.notepal.widget.desktop.ListRemoteViewsFactory.setupModels(ListRemoteViewsFactory.java:63)
                                                   at me.shouheng.notepal.widget.desktop.ListRemoteViewsFactory.onDataSetChanged(ListRemoteViewsFactory.java:86)
                                                   at android.widget.RemoteViewsService$RemoteViewsFactoryAdapter.onDataSetChanged(RemoteViewsService.java:142)
                                                   at com.android.internal.widget.IRemoteViewsFactory$Stub.onTransact(IRemoteViewsFactory.java:49)
                                                   at android.os.Binder.execTransact(Binder.java:565)

I did add the sychronized to important methods and the db class is singleton, I wonder why I still get the problem.

More information. Below is my log, I printed the thread, database and store objects:

02-05 22:41:53.119 15712-15712/me.shouheng.notepal D/colorful: ThemeDelegate fetched theme in 1 ms
02-05 22:41:53.305 3030-3149/system_process I/ActivityManager: Displayed me.shouheng.notepal/.activity.TrashedActivity: +235ms
02-05 22:41:53.648 15712-15712/me.shouheng.notepal D/NotePal: [ (BaseStore.java:327)#GetList ] me.shouheng.notepal.provider.NotebookStore@76f9b87
02-05 22:41:53.649 15712-15712/me.shouheng.notepal D/NotePal: [ (BaseStore.java:328)#GetList ] Thread[main,5,main]
02-05 22:41:53.656 15712-15712/me.shouheng.notepal D/NotePal: [ (BaseStore.java:327)#GetList ] me.shouheng.notepal.provider.NotesStore@f4252
02-05 22:41:53.656 15712-15712/me.shouheng.notepal D/NotePal: [ (BaseStore.java:328)#GetList ] Thread[main,5,main]

02-05 22:41:58.018 15712-15712/me.shouheng.notepal D/NotePal: [ (BaseStore.java:41)#<init> ] me.shouheng.notepal.provider.PalmDB@a34edfa
02-05 22:41:58.057 15712-15712/me.shouheng.notepal D/NotePal: [ (AppWidgetUtils.java:20)#NotifyAppWidgets ] Notifies AppWidget data changed for widgets [87, 85]
02-05 22:41:58.061 15712-15712/me.shouheng.notepal D/NotePal: [ (BaseStore.java:327)#GetList ] me.shouheng.notepal.provider.NotebookStore@76f9b87
02-05 22:41:58.062 15712-15712/me.shouheng.notepal D/NotePal: [ (BaseStore.java:328)#GetList ] Thread[main,5,main]
02-05 22:41:58.071 15712-15712/me.shouheng.notepal D/NotePal: [ (BaseStore.java:327)#GetList ] me.shouheng.notepal.provider.NotesStore@f4252
02-05 22:41:58.072 15712-15712/me.shouheng.notepal D/NotePal: [ (BaseStore.java:328)#GetList ] Thread[main,5,main]
02-05 22:41:58.155 15712-15712/me.shouheng.notepal D/NotePal: [ (ListRemoteViewsFactory.java:51)#OnCreate ] Created widget 87
02-05 22:41:58.157 15712-15712/me.shouheng.notepal D/NotePal: [ (BaseStore.java:41)#<init> ] me.shouheng.notepal.provider.PalmDB@a34edfa
02-05 22:41:58.158 15712-15712/me.shouheng.notepal D/NotePal: [ (ListRemoteViewsFactory.java:78)#GetMinds ] me.shouheng.notepal.provider.MindSnaggingStore@f341cfd
02-05 22:41:58.158 15712-15712/me.shouheng.notepal D/NotePal: [ (ListRemoteViewsFactory.java:79)#GetMinds ] Thread[main,5,main]
02-05 22:41:58.160 15712-15712/me.shouheng.notepal D/NotePal: [ (BaseStore.java:327)#GetList ] me.shouheng.notepal.provider.MindSnaggingStore@f341cfd
02-05 22:41:58.161 15712-15712/me.shouheng.notepal D/NotePal: [ (BaseStore.java:328)#GetList ] Thread[main,5,main]
02-05 22:41:58.169 15712-15712/me.shouheng.notepal D/NotePal: [ (ListRemoteViewsFactory.java:51)#OnCreate ] Created widget 85
02-05 22:41:58.171 15712-15712/me.shouheng.notepal D/NotePal: [ (BaseStore.java:327)#GetList ] me.shouheng.notepal.provider.NotesStore@f4252
02-05 22:41:58.172 15712-15712/me.shouheng.notepal D/NotePal: [ (BaseStore.java:328)#GetList ] Thread[main,5,main]
02-05 22:41:58.172 15712-16198/me.shouheng.notepal D/NotePal: [ (ListRemoteViewsFactory.java:85)#OnDataSetChanged ] onDataSetChanged widget 87
02-05 22:41:58.173 15712-16198/me.shouheng.notepal D/NotePal: [ (ListRemoteViewsFactory.java:78)#GetMinds ] me.shouheng.notepal.provider.MindSnaggingStore@f341cfd
02-05 22:41:58.173 15712-16198/me.shouheng.notepal D/NotePal: [ (ListRemoteViewsFactory.java:79)#GetMinds ] Thread[Binder:15712_3,5,main]
02-05 22:41:58.174 15712-16198/me.shouheng.notepal D/NotePal: [ (BaseStore.java:327)#GetList ] me.shouheng.notepal.provider.MindSnaggingStore@f341cfd
02-05 22:41:58.175 15712-16198/me.shouheng.notepal D/NotePal: [ (BaseStore.java:328)#GetList ] Thread[Binder:15712_3,5,main]
02-05 22:41:58.177 15712-15725/me.shouheng.notepal D/NotePal: [ (ListRemoteViewsFactory.java:85)#OnDataSetChanged ] onDataSetChanged widget 85
02-05 22:41:58.178 15712-15725/me.shouheng.notepal D/NotePal: [ (BaseStore.java:327)#GetList ] me.shouheng.notepal.provider.NotesStore@f4252
02-05 22:41:58.178 15712-15725/me.shouheng.notepal D/NotePal: [ (BaseStore.java:328)#GetList ] Thread[Binder:15712_1,5,main]
02-05 22:41:58.179 3030-4466/system_process W/InputMethodManagerService: Window already focused, ignoring focus gain of: com.android.internal.view.IInputMethodClient$Stub$Proxy@86f53ab attribute=null, token = android.os.BinderProxy@8dfbb89
02-05 22:41:58.483 15712-16191/me.shouheng.notepal D/OpenGLRenderer: endAllActiveAnimators on 0xc8ecfc00 (MenuPopupWindow$MenuDropDownListView) with handle 0xc8eb0540
02-05 22:41:58.933 3030-3485/system_process D/AudioService: Stream muted, skip playback
02-05 22:41:59.775 3030-3485/system_process D/AudioService: Stream muted, skip playback
02-05 22:41:59.804 15712-15712/me.shouheng.notepal D/NotePal: [ (AppWidgetUtils.java:20)#NotifyAppWidgets ] Notifies AppWidget data changed for widgets [87, 85]
02-05 22:41:59.807 15712-15725/me.shouheng.notepal D/NotePal: [ (ListRemoteViewsFactory.java:85)#OnDataSetChanged ] onDataSetChanged widget 87
02-05 22:41:59.807 15712-16198/me.shouheng.notepal D/NotePal: [ (ListRemoteViewsFactory.java:85)#OnDataSetChanged ] onDataSetChanged widget 85
02-05 22:41:59.808 15712-15725/me.shouheng.notepal D/NotePal: [ (ListRemoteViewsFactory.java:78)#GetMinds ] me.shouheng.notepal.provider.MindSnaggingStore@f341cfd
02-05 22:41:59.808 15712-15712/me.shouheng.notepal D/NotePal: [ (BaseStore.java:327)#GetList ] me.shouheng.notepal.provider.NotebookStore@76f9b87
02-05 22:41:59.808 15712-15725/me.shouheng.notepal D/NotePal: [ (ListRemoteViewsFactory.java:79)#GetMinds ] Thread[Binder:15712_1,5,main]
02-05 22:41:59.808 15712-15712/me.shouheng.notepal D/NotePal: [ (BaseStore.java:328)#GetList ] Thread[main,5,main]
02-05 22:41:59.808 15712-16198/me.shouheng.notepal D/NotePal: [ (BaseStore.java:327)#GetList ] me.shouheng.notepal.provider.NotesStore@f4252
02-05 22:41:59.808 15712-15725/me.shouheng.notepal D/NotePal: [ (BaseStore.java:327)#GetList ] me.shouheng.notepal.provider.MindSnaggingStore@f341cfd
02-05 22:41:59.810 15712-16198/me.shouheng.notepal D/NotePal: [ (BaseStore.java:328)#GetList ] Thread[Binder:15712_3,5,main]
02-05 22:41:59.810 15712-15712/me.shouheng.notepal I/SQLiteConnectionPool: The connection pool for /data/user/0/me.shouheng.notepal/databases/NotePal.db has been closed but there are still 1 connections in use.  They will be closed as they are released back to the pool.
02-05 22:41:59.811 15712-15725/me.shouheng.notepal D/NotePal: [ (BaseStore.java:328)#GetList ] Thread[Binder:15712_1,5,main]
02-05 22:41:59.828 15712-15712/me.shouheng.notepal D/NotePal: [ (BaseStore.java:327)#GetList ] me.shouheng.notepal.provider.NotesStore@f4252
02-05 22:41:59.829 15712-15712/me.shouheng.notepal D/NotePal: [ (BaseStore.java:328)#GetList ] Thread[main,5,main]
02-05 22:41:59.867 15712-15717/me.shouheng.notepal I/art: Do full code cache collection, code=97KB, data=125KB
02-05 22:41:59.868 15712-15717/me.shouheng.notepal I/art: Starting a blocking GC JitCodeCache
02-05 22:41:59.868 15712-15717/me.shouheng.notepal I/art: After code cache collection, code=79KB, data=87KB
02-05 22:41:59.899 3030-14418/system_process W/InputMethodManagerService: Window already focused, ignoring focus gain of: com.android.internal.view.IInputMethodClient$Stub$Proxy@9aae3c6 attribute=null, token = android.os.BinderProxy@8dfbb89
02-05 22:41:59.957 15712-15725/me.shouheng.notepal E/AndroidRuntime: FATAL EXCEPTION: Binder:15712_1
                                                                     Process: me.shouheng.notepal, PID: 15712
                                                                     java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed.
                                                                         at android.database.sqlite.SQLiteConnectionPool.throwIfClosedLocked(SQLiteConnectionPool.java:962)
                                                                         at android.database.sqlite.SQLiteConnectionPool.waitForConnection(SQLiteConnectionPool.java:599)
                                                                         at android.database.sqlite.SQLiteConnectionPool.acquireConnection(SQLiteConnectionPool.java:348)
                                                                         at android.database.sqlite.SQLiteSession.acquireConnection(SQLiteSession.java:894)
                                                                         at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:834)
                                                                         at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
                                                                         at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:143)
                                                                         at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:132)
                                                                         at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:219)
                                                                         at android.database.AbstractCursor.moveToFirst(AbstractCursor.java:258)
                                                                         at me.shouheng.notepal.provider.BaseStore.getList(BaseStore.java:330)
                                                                         at me.shouheng.notepal.provider.BaseStore.get(BaseStore.java:127)
                                                                         at me.shouheng.notepal.provider.BaseStore.get(BaseStore.java:101)
                                                                         at me.shouheng.notepal.widget.desktop.ListRemoteViewsFactory.getMinds(ListRemoteViewsFactory.java:80)
                                                                         at me.shouheng.notepal.widget.desktop.ListRemoteViewsFactory.setupModels(ListRemoteViewsFactory.java:65)
                                                                         at me.shouheng.notepal.widget.desktop.ListRemoteViewsFactory.onDataSetChanged(ListRemoteViewsFactory.java:86)
                                                                         at android.widget.RemoteViewsService$RemoteViewsFactoryAdapter.onDataSetChanged(RemoteViewsService.java:142)
                                                                         at com.android.internal.widget.IRemoteViewsFactory$Stub.onTransact(IRemoteViewsFactory.java:49)
                                                                         at android.os.Binder.execTransact(Binder.java:565)
Shawn Wong
  • 554
  • 6
  • 15
  • Notice the log item at 02-05 22:41:59.810 of the last log, the database connection pool is closed, but why? Since I never manually close it. – Shawn Wong Feb 05 '18 at 15:02

1 Answers1

0

My problem is solved. The reason is rather simple, it's just in one place that I used the SQLiteDatabase.close() so it crashed. Even so, it's still a good opportunity for me to learn more about Android database connection pool. Here is something that useful for me to solve this problem.

We call SQLiteOpenHelper.getWrteableDatabase() and SQLiteOpenHelper.getReadableDatabase() to get the SQLiteDatabase object. The logic is rather simple, if the SQLiteDatabase object is already for business, it will return it, else it will create a new one and prepare for business and then return it. So, that means, if the SQLiteOpenHelper is singleton, the SQLiteDatabase object is singleton too. If we call the SQLiteOpenHelper.close() or SQLiteDatabase.close(), they will all call SQLiteDatabase.close(). In SQLiteDatabase.close(), it will judge current references number, if all the references released, the SQLiteConnectionPool will be closed.

So, that means, if all the Store (used to store and query data from db) objects use the same SQLiteDatabaseHelper, they will use the same SQLiteDatabase too. If I closed SQLiteDatabase in one Store object, it will affect another Store object. Even I added the sychronized keyword in one Store methods, it will only lock methods in the same object. That is the real reason caused this exception.

Then I removed the SQLiteDatabase.close() logic, it worked fine.

But, what if I want to close the SQLiteDatabase. I found out one way for my case. The OpenHelperManager is used to calculate current connections number and if the number is zero, then it can close the db connection.

public class OpenHelperManager {
    @SuppressLint("StaticFieldLeak")
    private static boolean isClosed = false;
    private static int instanceCount = 0;

    public static synchronized void releaseHelper(PalmDB helper) {
        instanceCount--;
        LogUtils.e(String.format("releasing helper %s, instance count = %s", helper, instanceCount));
        if (instanceCount <= 0) {
            if (helper != null) {
                LogUtils.e(String.format("zero instances, closing helper %s", helper));
                helper.close();
                isClosed = true;
            }
            if (instanceCount < 0) {
                LogUtils.e(String.format("too many calls to release helper, instance count = %s", instanceCount));
            }
        }
    }

    public static synchronized void requireConnection() {
        isClosed = false;
        instanceCount++;
    }

    public static boolean isClosed() {
        return isClosed;
    }
}

Here, the PalmDB extends SQLiteOpenHelper:

public class PalmDB extends SQLiteOpenHelper {
    // ...
}

In the store object:

protected SQLiteDatabase getWritableDatabase() {
    OpenHelperManager.requireConnection();
    return mPalmDatabase.getWritableDatabase();
}

protected void closeDatabase(SQLiteDatabase database) {
    OpenHelperManager.releaseHelper(mPalmDatabase);
}

I use getWritableDatabase() method to get SQLiteDatabase, and at the same time increase the count of instance in OpenHelperManager. Add use closeDatabase() method instead of SQLiteDatabase.close() to close database. In closeDatabase() method, I called OpenHelperManager.releaseHelper(mPalmDatabase), which will decrease the count of instance in OpenHelperManager. If the count of instance is zero, then close the SQLiteOpenHelper, that will close SQLiteDatabse object. It worked fine for my case. You may also use some atomic classes like AtomicInteger to calculate the count.

That is the answer for my problem, hope it helps for you.

Shawn Wong
  • 554
  • 6
  • 15