Error Cannot perform this operation because the connection pool has been closed

Question

I am working with sqlite db and use some code of Alex LockWood Correctly Managing Your SQLite Database

It works very well but sometimes I got the error "java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed." Here is the full Error:

02-20 16:37:21.385: W/dalvikvm(25730): threadid=13: thread exiting with uncaught exception (group=0x41c122a0)
02-20 16:37:21.390: E/AndroidRuntime(25730): FATAL EXCEPTION: Timer-0
02-20 16:37:21.390: E/AndroidRuntime(25730): java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed.
02-20 16:37:21.390: E/AndroidRuntime(25730):    at android.database.sqlite.SQLiteConnectionPool.throwIfClosedLocked(SQLiteConnectionPool.java:963)
02-20 16:37:21.390: E/AndroidRuntime(25730):    at android.database.sqlite.SQLiteConnectionPool.waitForConnection(SQLiteConnectionPool.java:678)
02-20 16:37:21.390: E/AndroidRuntime(25730):    at android.database.sqlite.SQLiteConnectionPool.acquireConnection(SQLiteConnectionPool.java:349)
02-20 16:37:21.390: E/AndroidRuntime(25730):    at android.database.sqlite.SQLiteSession.acquireConnection(SQLiteSession.java:894)
02-20 16:37:21.390: E/AndroidRuntime(25730):    at android.database.sqlite.SQLiteSession.executeForCursorWindow(SQLiteSession.java:834)
02-20 16:37:21.390: E/AndroidRuntime(25730):    at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:62)
02-20 16:37:21.390: E/AndroidRuntime(25730):    at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:143)
02-20 16:37:21.390: E/AndroidRuntime(25730):    at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:133)
02-20 16:37:21.390: E/AndroidRuntime(25730):    at com.uit.pokemon.DatabaseHandler.getStadiumStatusById(DatabaseHandler.java:533)
02-20 16:37:21.390: E/AndroidRuntime(25730):    at playground.RoomActivity.checkTable(RoomActivity.java:276)
02-20 16:37:21.390: E/AndroidRuntime(25730):    at playground.RoomActivity$6.run(RoomActivity.java:321)
02-20 16:37:21.390: E/AndroidRuntime(25730):    at java.util.Timer$TimerImpl.run(Timer.java:284)
02-20 16:37:21.460: I/timertask cancel(25730): canceled

And here is the code that causes the error:

public int getStadiumStatusById(int dataStadiumId){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cur = db.rawQuery("SELECT " + keyStadiumId + " as _id, "
                + keyRoomName + ", " + keyP1Name + ", " + keyP1PokemonName
                + ", " + keyP1PokemonLevel + ", " + keyP1PokemonHp + ", "
                + keyP2Name + ", " + keyP2PokemonName + ", "
                + keyP2PokemonLevel + ", " + keyP2PokemonHp + ", "
                + keyTimeCreate + ", " + keyStadiumStatus + " from "
                + tbl_stadium + " WHERE " + keyStadiumId + " = " + "'"
                + dataStadiumId + "'", new String[] {});

        int stadiumStatus = 0;
        if(cur.getCount()>0)
        {
        cur.moveToFirst();
        stadiumStatus = cur.getInt(11);
        }
        db.close();
        cur.close();
        return stadiumStatus;
    }

I tried googling for many hours but no result. Please help me to fix it. Any help will be appreciated. Thank you!

http://stackoverflow.com/questions/4547461/closing-the-database-in-a-contentprovider — baboo, Feb 20 '13 at 09:54
@baboo Thanks for your answer but i don't use a Content Provider and never call close db method. Can you point me out the problem? — Grin, Feb 20 '13 at 09:58
@Aromal Sasidharan I open it n one activity to read the content and have another thread write content to it. I use this type of sigleton to help me manage the db: http://www.androiddesignpatterns.com/2012/05/correctly-managing-your-sqlite-database.html — Grin, Feb 20 '13 at 10:05
@Apurv Gupta: the line 533 is: if(cur.getCount()>0). I will close the cursor before the db. But that error happen before I call db.close(). — Grin, Feb 20 '13 at 10:07
check if db is actually open while you are performing cur.getCount() — Apurv Gupta, Feb 20 '13 at 10:21
@Apurv Gupta: Sorry, but I am new to android. Can you tell me how to check if db is actually open?. I think if I use single instance of SQLiteOpenHelper to manage and never call method close, so it still open? Thanks for your answer! :) — Grin, Feb 20 '13 at 10:25
Are you calling getStadiumStatusById multiple times? can you show me codes of getReadableDatabase()? — Apurv Gupta, Feb 20 '13 at 10:31
http://stackoverflow.com/questions/14002022/android-sq-lite-closed-exception — Apurv Gupta, Feb 20 '13 at 10:36
Yes, I call getStadiumStatusById() multiple time, about 8 times per 10sec but I call the same code like this in other activity and it don't cause error, just this cause error. getReadableDatabase() is a part of SQLiteOpenHelper. — Grin, Feb 20 '13 at 10:39
@Apurv Gupta: I read that thread, and remove all db.close() from my code but still same error :(. It sometime happens. I will check if db.isopen(), it cost testing for a while. Thank you! :) — Grin, Feb 20 '13 at 11:09
your database is getting closed somewhere and we have no idea where. :( — Apurv Gupta, Feb 20 '13 at 11:10
do one thing check that db.isOpen() else try opening the using db.open() within the function itself — Aromal Sasidharan, Feb 20 '13 at 11:50
it is due to db.close(); . try by closing it after cursor.close(); .. or by removing it.. — Zar E Ahmer, Aug 19 '14 at 08:35

score 1 · Answer 1 · answered Jun 17 '16 at 11:36

In your code you are using:

db.close();
cur.close();

The best practice is to close the cursor first and then closing the database as once you call db.close(), it will make the database freeze n closed and its corresponding cursor invalid. Try this change it will work.

Replace it by:

cur.close();
db.close();

score 0 · Answer 2 · edited May 23 '17 at 12:34

I'm also dealing with this problem. I have an idea of the cause, and would love to hear the experts' opinions on this. I make quite a few assumptions and come to some conclusions not backed by proof....

I think the exception is thrown on the 'return stadiumStatus' line. A couple of lines above, you assign this variable to a member of the Cursor. I believe that you are actually assigning the name stadiumStatus to point to an int variable that only exists inside the cursor. Another possibility (huge leap of logic here) is that the item returned by Cursor.getInt() is actually a pointer to an item (a method?) in the open database (which resides in memory because it is open.) Either way, when you close the Cursor and/or database and then try to return stadiumStatus, the int points to the address in the Cursor or passed by the Cursor, tries to follow it, and dead ends at either the closed Cursor or the closed Database (one of these is the "data pool" you are drawing from.)

If I am right, I think the best fix for either would be to replace:

"stadiumStatus = cur.getInt(11);"

with:

"stadiumStatus = new Integer(cur.getInt(11));"

thereby creating a new Integer in memory which the vm can trivially cast to an int, and which is reliant only on its traditional scope for accessibility.

Does this answer make sense? I'm sure the questioner has long since moved past this problem, but I would love be able to say I answered a question on Stack Overflow (or to be told why I am wrong, and have a better understanding of Cursors and DB's).

Edit: These answers imply I may be right:
Android error: Cannot perform this operation because the connection pool has been closed
Will cursor be still alive after database is closed?
In the second answer, user DeeV, who has a huge reputation, calls Cursors "persistent connections", implying it is not a simple collection of returned values from the DB.

I doubt it. cur.getInt(11) should return a simple int, not an object. So, stadiumStats is not a reference to the int, but simply holds the int value. — MCLLC, Nov 11 '15 at 20:21

score 0 · Answer 3 · answered Mar 02 '17 at 13:57

0

I found that this exception also occurs when using database in multithreading. So it's better to add synchronized between db = helper.getXXXDatabase() and db.close()

answered Mar 02 '17 at 13:57

PinkD

1

score 0 · Answer 4 · answered Jan 31 '18 at 22:41

The database opened by a SQLiteOpenHelper class is kept open for the life of the class.

So there is no need to open and close the database. In fact if it is used on multiple threads, it will be wrong.

My app has been database error free for some time, I have a single helper class. This needs to be a singleton, so different instances of SqliteDatabase don't try and modify the file at the same time.

Also some of the crashes I used to get show that both Cursors and Queries can call a sqlite Query, so allowing concurrent read and write access to the database made no sense.

For this reason I added a reader/writer lock to the helper, and use this pattern...

    public static class MyDbHelper extends SQLiteOpenHelper {
        private MyDbHelper (Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }
        public static MyDbHelper mInstance;
        private static Object mObj = new Object();
        public static MyDbHelper getInstance( Context context ){
            synchronized (mObj ){
                if( mInstance == null ){
                    mInstance = new MyDbHelper ( context );
                }
            }
            return mInstance;
        }
        private ReentrantReadWriteLock mLock = new ReentrantReadWriteLock();
        public Lock readLock() {
            return mLock.readLock();
        }
        public Lock writeLock() {
            return mLock.writeLock();
        }

Using the class is something like this....

    MyStorageContract.MyDbHelper helper = MyStorageContract.MyDbHelper.getInstance(this);
    SQLiteDatabase db = helper.getReadableDatabase();
    helper.readLock().lock();
    try {
        String[] columns = {
                MyStorageContract.Libraries.COLUMN_NAME_LIBRARY_ID,
                MyStorageContract.Libraries.COLUMN_NAME_LIBRARY_DIR,
                MyStorageContract.Libraries.COLUMN_NAME_LIBRARY_SOMETHING,
        };
        String result = null;
        Cursor c = db.query(MyStorageContract.Libraries.TABLE_NAME, columns, null, null, null, null, null, "1");
        int idxDir = c.getColumnIndex(MyStorageContract.Libraries.COLUMN_NAME_LIBRARY_DIR);
        int idxRoot = c.getColumnIndex(MyStorageContract.Libraries.COLUMN_NAME_LIBRARY_SOMETHING);
        int idxId = c.getColumnIndex(MyStorageContract.Libraries.COLUMN_NAME_LIBRARY_ID);
        for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
            result = c.getString(idxDir);
        }
        c.close();
        return result;
    }
    finally {
        helper.readLock().unlock();
    }

score 0 · Answer 5 · answered Feb 07 '18 at 13:42

I met this problem and solved it here java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed. In my case I analysied the database framework of android. The real reason of mime is that I use SQLiteDatabase in many store objects which are used to save and query data. Since the SQLiteOpenHelper is singleton, the SQLiteDatabase is singleton too. Even I add the sychronized in one store, it's still unlocked in another store. So, if I forget to remove SQLiteDatabase.close() in one place, it will caused the SQLiteConnectionPool closed. Furthermore, I have also added some methods to release SQLite connection. Hope it helps.

score -6 · Answer 6 · edited Oct 30 '13 at 10:10

-6

Call

db.open()

after this line:

SQLiteDatabase db = this.getReadableDatabase();

However, I personally don't know how this.getReadableDatabase() is doing exactly but I would just make my own database adapter. See this link for more details: http://www.vogella.com/articles/AndroidSQLite/article.html

edited Oct 30 '13 at 10:10

Tamil Selvan C

19,913
12
49
70

answered Sep 27 '13 at 05:28

swhaider

1
3

1

There is no method called db.open() in the SQLiteDatabase Class http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html – Akh Nov 11 '13 at 23:51

Error Cannot perform this operation because the connection pool has been closed

6 Answers6