Closing the database in a ContentProvider

Question

This week I've been learning all about ContentProvider and using the SQLiteOpenHelper class to manage the creation and upgrading of the database inside of a provider. Specifically, I've been reading through the NotePad example from the sdk's samples directory.

Now, I can see that SQLiteOpenHelper has a close() method. I'm aware that leaving idle databases open is bad practice and can cause memory leaks and whatnot (unless this discussion is headed in the right direction). If I were using the class in an Activity, then I would simply call close() in the onDestroy() method, but as far as I know, ContentProvider does not have the same life cycle that activities do. The code for NotePad never seems to call close(), so I would like to assume that it is handled by SQLiteOpenHelper or some other piece of the puzzle, but I'd really like to know for sure. I don't really trust the sample code that much, either...

Question summary: When should we close the database in a provider, if at all?

Stites

Dianne Hackborn said there's no need to close the db. – Trudietrudnak

This is the most important information on this thread. I made it an answer. – Seguidilla

Answer 1

According to Dianne Hackborn (Android framework engineer) there is no need to close the database in a content provider.

A content provider is created when its hosting process is created, and remains around for as long as the process does, so there is no need to close the database -- it will get closed as part of the kernel cleaning up the process's resources when the process is killed.

Thanks @bigstones for pointing this out.

Seguidilla

Thanks. Btw, they (Android team) should comment this "simple" thing in guideline, or at least in the sample code, rather than letting coders search for it on the net. – Dogger

don't get me started on what is missing :) – Seguidilla

Ok, but what does the shutdown function do. The code is: public void shutdown() { Log.w(TAG, "implement ContentProvider shutdown() to make sure all database " + "connections are gracefully shutdown"); } – Sage

You will need to close DB at shutdown() when it comes for Reboelectric ContentProvider unit testing. – Pediment

Answer 2

This question is a bit old but is still quite relevant. Note that if you're doing things the 'modern' way (e.g. using LoaderManager and creating CursorLoaders to query a ContentProvider in a background thread), make sure that you do NOT call db.close() in your ContentProvider implementation. I was getting all sorts of crashes relating to CursorLoader/AsyncTaskLoader when it tried to access the ContentProvider in a background thread, which were resolved by removing the db.close() calls.

So if you're running into crashes that look like this (Jelly Bean 4.1.1):

Caused by: 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:677)
    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:133)
    at android.content.ContentResolver.query(ContentResolver.java:388)
    at android.content.ContentResolver.query(ContentResolver.java:313)
    at com.hindsightlabs.paprika.loaders.GroceryListLoader.loadInBackground(GroceryListLoader.java:147)
    at com.hindsightlabs.paprika.loaders.GroceryListLoader.loadInBackground(GroceryListLoader.java:1)
    at android.support.v4.content.AsyncTaskLoader.onLoadInBackground(AsyncTaskLoader.java:240)
    at android.support.v4.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:51)
    at android.support.v4.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:40)
    at android.support.v4.content.ModernAsyncTask$2.call(ModernAsyncTask.java:123)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:305)
    ... 4 more

Or this (ICS 4.0.4):

Caused by: java.lang.IllegalStateException: database /data/data/com.hindsightlabs.paprika/databases/Paprika.db (conn# 0) already closed
    at android.database.sqlite.SQLiteDatabase.verifyDbIsOpen(SQLiteDatabase.java:2215)
    at android.database.sqlite.SQLiteDatabase.lock(SQLiteDatabase.java:436)
    at android.database.sqlite.SQLiteDatabase.lock(SQLiteDatabase.java:422)
    at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:79)
    at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:164)
    at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:156)
    at android.content.ContentResolver.query(ContentResolver.java:318)
    at android.support.v4.content.CursorLoader.loadInBackground(CursorLoader.java:49)
    at android.support.v4.content.CursorLoader.loadInBackground(CursorLoader.java:35)
    at android.support.v4.content.AsyncTaskLoader.onLoadInBackground(AsyncTaskLoader.java:240)
    at android.support.v4.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:51)
    at android.support.v4.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:40)
    at android.support.v4.content.ModernAsyncTask$2.call(ModernAsyncTask.java:123)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:305)
    ... 4 more

Or if you're seeing error messages in LogCat that look like this:

Cursor: invalid statement in fillWindow()

Then check your ContentProvider implementation and make sure you're not closing the database prematurely. According to this, the ContentProvider will get cleaned up automatically when the process is killed anyway, so you don't need to close its database ahead of time.

That said, make sure you are still correctly:

1. Closing your Cursors that are returned from ContentProvider.query(). (CursorLoader/LoaderManager does this automatically for you, but if you're doing direct queries outside of the LoaderManager framework, or you've implemented a custom CursorLoader/AsyncTaskLoader subclass, you'll have to make sure you're cleaning up your cursors properly.)
2. Implementing your ContentProvider in a thread-safe way. (The easiest way to do this is to make sure your database access methods are wrapped in a synchronized block.)

Bebel

Answer 3

Ive follow Mannaz's answer and saw that SQLiteCursor(database, driver, table, query); constructor is deprecated. Then I found getDatabase() method and used it instead of mDatabase pointer; and kept constructor for backward capability

public class MyOpenHelper extends SQLiteOpenHelper {
    public static final String TAG = "MyOpenHelper";

    public static final String DB_NAME = "myopenhelper.db";
    public static final int DB_VESRION = 1;

    public MyOpenHelper(Context context) {
        super(context, DB_NAME, new LeaklessCursorFactory(), DB_VESRION);
    }

    //...
}

public class LeaklessCursor extends SQLiteCursor {
    static final String TAG = "LeaklessCursor";

    public LeaklessCursor(SQLiteDatabase db, SQLiteCursorDriver driver,
            String editTable, SQLiteQuery query) {
        super(db, driver, editTable, query);
    }

    @Override
    public void close() {
        final SQLiteDatabase db = getDatabase();
        super.close();
        if (db != null) {
            Log.d(TAG, "Closing LeaklessCursor: " + db.getPath());
            db.close();
        }
    }
}


public class LeaklessCursorFactory implements CursorFactory {
    @Override
    public Cursor newCursor(SQLiteDatabase db, SQLiteCursorDriver masterQuery,
        String editTable, SQLiteQuery query) {
        return new LeaklessCursor(db,masterQuery,editTable,query);
    }
}

Yashmak

I love it when people take the time to answer/update older questions. Thanks Pleerock! Looks like I need to revisit this... – Stites

YW. By the way I found some "leak" in our LeaklessCursor. It isn't effective when you are using content providers. For example, when you are updating data in the database, your cursor will be updated too. So this cursor will be closed and new cursor will be opened. When our cursor will be closed, our database will be closed too. It may cause an error. For example: ContentProvider(opens database)->query(for cursor, uses db)->update(any data, uses db)->notifies->closes an old cursor(also closes db)->creates new cursor(query, uses db) and POOOOW error -> db was closed, cannot open new cursor – Yashmak

if the getDatabase() call can return null then the Log.d(...) could crash, I put in an edit that moves the Log to the if-statement, and puts getDatabase() in a reference, since you use it 3 times. You can add a Log outside the if-statement that lets you know that you "should" have closed a database, regardless of if getDatabase() was null if that's useful. – Greater

Note. The idea of closing the database in a ContentProvider is not so good. Avoid this if you can. "Closing the leaking cursor" may lead to a lot of other leaks. Content provider is for a database queries, so it always uses database. Even if you already completed your queries, content provider (and database instance) can depend of other classes – Yashmak

Answer 4

If you want your Database to close automatically you can provide a CursorFactory when opening it:

mContext.openOrCreateDatabase(DB_NAME, SQLiteDatabase.OPEN_READWRITE, new LeaklessCursorFactory());

Here are the classes:

public class LeaklessCursorFactory implements CursorFactory {
    @Override
    public Cursor newCursor(SQLiteDatabase db, SQLiteCursorDriver masterQuery,
        String editTable, SQLiteQuery query) {
        return new LeaklessCursor(db,masterQuery,editTable,query);
    }
}


public class LeaklessCursor extends SQLiteCursor {
    static final String TAG = "LeaklessCursor";
    final SQLiteDatabase mDatabase;

    public LeaklessCursor(SQLiteDatabase database, SQLiteCursorDriver driver, String table, SQLiteQuery query) {
        super(database, driver, table, query);
        mDatabase = database;
    }

    @Override
    public void close() {
        Log.d(TAG, "Closing LeaklessCursor: " + mDatabase.getPath());
        super.close();
        if (mDatabase != null) {
            mDatabase.close();
        }
    }
}

Kubetz

Note to others: Please note Pleerock's answer as well, as it updates this answer in a small but important way. This is a terrific solution, by the way - I always get a little giddy when I see good use of design patterns. :P – Stites

Answer 5

Close it when you are done with it, preferably in a finally block so you can ensure that it happens. I know that sounds a little trite and off-the-cuff, but it's really the only answer that I know of. If you open the database and perform an action, close it when you're done with that action unless you know for a fact it will be needed again (in which case be sure to close it once its no longer needed).

Sinclare

The only way we can know if we'll need it again is from outside of the provider (in the code that's using it). I think that inside the provider, the database is accessed every time getWriteableDatabase() or getReadableDatabase() is called on the SQLiteOpenHelper. Based on your suggestion, should I add a close() afterwards in each method where these are called? It seems like if multiple queries were run one after another, then there'd be a whole lot of database opening and closing going on. I'm not sure, but I would imagine that would affect performance. – Stites

Answer 6

If you are using your content provider within a activity, then I do not believe that you have to maintain the connection of the content provider. You could just manage the cursor object returned using startManagingCursor. In the onPause method of activity, you can release the content provider. ( you can reload it in onResume). Assuming that the activity life cycle will usually be limited, this would suffice. (Atleast according to me ;))

Dynamism

Of course if you are using sql lite, then you can close the connection after getting the results. (Again make sure that the cursor's lifecycle is handled by activity using startmanagingcursor. – Dynamism