Avoiding Connection Pool Closed in SQLite

Question

I am getting error as below:

Exception java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed.

and reason is while I m fetching data from table, some times coincidentally another service start running to insert data and as per SQLite logic, that insert command close the connection and the service engaged in reading data from table got connection pool closed error.

Can some one help how can I handle this? I m not getting this error on all devices. Only few devices and that too sometimes.

I read on some questions about using setWriteAheadLoggingEnabled(true). Is this which I can use too in my class constructor?

My insert method in DatabasWorking Class which i call from different classes to insert data.

public void insert(int deviceid, int productid) {
      SQLiteDatabase  sqLiteDatabase = this.getWritableDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("deviceid", deviceid);
        contentValues.put("productid", productid);
        sqLiteDatabase.insert(payment_table, null, contentValues);
        sqLiteDatabase.close();

    }

Now how to use singleton in this method. Do i need to change something here or not?

Do you close your SQLiteOpenHelper and is it a Singleton? First thing I would recommend is making sure it is a singleton and there is only one instance. The second thing is I would not call close on it. You don't need to close it on Android as the resources are cleaned up for you. — tim.paetz, Oct 23 '17 at 14:48
this I not Singleton. and I m using separate instance of SQLiteDatabase for both insert and get methods. — Panache, Oct 23 '17 at 14:50
Do u mean, i do something like as private static DatabaseWorking mDatabaseWorking; public static synchronized DatabaseWorking getInstance(Context context) { if (mDatabaseWorking == null) { mDatabaseWorking = new DatabaseWorking(context.getApplicationContext()); } return mDatabaseWorking; } — Panache, Oct 23 '17 at 14:59
can u explain how to use it now for insert method. my insert method is in Databaseworking class only. — Panache, Oct 23 '17 at 15:20
i have added my insert method in question, pls suggest changes — Panache, Oct 23 '17 at 15:25
Get rid of "sqLiteDatabase.close();" and see if you still get the issue. — tim.paetz, Oct 23 '17 at 15:26
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/157293/discussion-between-panache-and-tim-paetz). — Panache, Oct 23 '17 at 15:27

score 3 · Answer 1 · answered Feb 07 '18 at 14:12

I met this problem when I query database from multi-theads environment. Since the getWritableDatabase() return same SQLiteDatabase object for the same SQLiteOpenHelper, if you closed it in one thread while another thread using it, it will cause the SQLiteConnectionPool close exception. I analysied it for my case here: java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed.

Here is the suggestions for your code:

Judged from you code that it may exists in the SQLiteOpenHelper object. You need to make it singleton since one SQLiteOpenHelper is mapped to one db. It is not necessary to keep too many SQLiteOpenHelper object in one project for one db. You may make it like this:

// the PalmDB is your Helper name
class PalmDB extends SQLiteOpenHelper { 

    private static PalmDB sInstance = null;

    public static PalmDB getInstance(final Context context){
        if (sInstance == null){
            synchronized (SQLiteOpenHelper.class) {
                if (sInstance == null) {
                    // be sure to call getApplicationContext() to avoid memory leak
                    sInstance = new PalmDB(context.getApplicationContext());
                }
            }
        }
        return sInstance;
    }

    // ... next is the code for your insert method:

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

    protected void closeDatabase() {
        OpenHelperManager.releaseHelper(this);
    }

    /*
    * Note that in this method, I didn't call getWriteDatabse() and SQLiteDatabase.close() 
    * to open and close db connection, instead I used two methods and added some options there.
    */
    public synchronized void insert(int deviceid, int productid) {
        SQLiteDatabase sqLiteDatabase = getDatabase();
        ContentValues contentValues = new ContentValues();
        contentValues.put("deviceid", deviceid);
        contentValues.put("productid", productid);
        sqLiteDatabase.insert(payment_table, null, contentValues);
        closeDatabase();
    }
}

Next is the class used in SQLiteOpenHelper:

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 I used the static and sychronized methods to lock the method, and added calculations in each method. When the getWriteableDatabse() method is called, I will increase the number of connection, otherwise I decrease it. When the connections count is 0, I close the Databse connection pool. I think it may be safe and also closed the connection. (Note that close the Helper class will also close the SQLiteDatabse object. You may refer to the source code.)

Hope it works!

Avoiding Connection Pool Closed in SQLite

1 Answers1