Android SQLite DB When to Close

I am working with a SQLite database on android. My database manager is a singleton and right now opens a connection to the database when it is initialized. It is safe to leave the database open the entire time so that when someone calls my class to work with the database it is already open? Or should I open and close the database before and after each access is needed. Is there any harm in just leaving it open the whole time?

Thanks!

i would keep it open the whole time, and close it in some lifecycle method such as onStop or onDestroy. that way, you can easily check if the database is already in use by calling isDbLockedByCurrentThread or isDbLockedByOtherThreads on the single SQLiteDatabase object every time before you use it. this will prevent multiple manipulations to the database and save your application from a potential crash

so in your singleton, you might have a method like this to get your single SQLiteOpenHelper object:

private SQLiteDatabase db;
private MyDBOpenHelper mySingletonHelperField;
public MyDBOpenHelper getDbHelper() {
    db = mySingletonHelperField.getDatabase();//returns the already created database object in my MyDBOpenHelper class(which extends `SQLiteOpenHelper`)
    while(db.isDbLockedByCurrentThread() || db.isDbLockedByOtherThreads()) {
        //db is locked, keep looping
    }
    return mySingletonHelperField;
}

so whenever you want to use your open helper object, call this getter method(make sure it's threaded)

another method in your singleton may be(called EVERY TIME before you try to call the getter above):

public void setDbHelper(MyDBOpenHelper mySingletonHelperField) {
    if(null == this.mySingletonHelperField) {
        this.mySingletonHelperField = mySingletonHelperField;
        this.mySingletonHelperField.setDb(this.mySingletonHelperField.getWritableDatabase());//creates and sets the database object in the MyDBOpenHelper class
    }
}

you may want to close the database in the singleton as well:

public void finalize() throws Throwable {
    if(null != mySingletonHelperField)
        mySingletonHelperField.close();
    if(null != db)
        db.close();
    super.finalize();
}

if the users of your application have the ability to create many database interactions very quickly, you should use something like i have demonstrated above. but if there is minimal database interactions, i wouldn't worry about it, and just create and close the database every time.

As of now there is no need to check if database locked by another thread. While you use singleton SQLiteOpenHelper in every thread you are safe. From isDbLockedByCurrentThread documentation:

> The name of this method comes from a time when having an active > connection to the database meant that the thread was holding an actual > lock on the database. Nowadays, there is no longer a true "database > lock" although threads may block if they cannot acquire a database > connection to perform a particular operation.

isDbLockedByOtherThreads is deprecated since API Level 16.

Regarding the questions: > My database manager is a singleton and right now opens a connection to the database when it is initialized.

We should divide 'opening DB', 'opening a connection'. SQLiteOpenHelper.getWritableDatabase() gives an opened DB. But we do not have to control connections as it is done internally.

> It is safe to leave the database open the entire time so that when someone calls my class to work with the database it is already open?

Yes, it is. Connections do not hang if transactions are properly closed. Note that your DB will be also closed automatically if GC finalizes it.

> Or should I open and close the database before and after each access is needed.

Closing the SQLiteDatabase instance gives nothing tremendous except closing connections but this is a developer's bad if there are some connections at this moment. Also, after SQLiteDatabase.close(), SQLiteOpenHelper.getWritableDatabase() will return a new instance.

> Is there any harm in just leaving it open the whole time?

No, there isn't. Note also that closing the DB at an unrelated moment and thread e.g. in Activity.onStop() might close active connections and leave the data in inconsistent state.

Android 8.1 has an SQLiteOpenHelper.setIdleConnectionTimeout(long) method which:

> Sets the maximum number of milliseconds that SQLite connection is > allowed to be idle before it is closed and removed from the pool.

https://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html#setIdleConnectionTimeout(long)

From performance perspective the optimal way is to keep a single instance of SQLiteOpenHelper on the application level. Opening database can be expensive and is a blocking operation, so it shouldn't be done on the main thread and/or in the activity lifecycle methods.

setIdleConnectionTimeout() method (introduced in Android 8.1) can be used to free RAM when the database is not use. If idle timeout is set, database connection(s) will be closed after a period of inactivity, i.e. when database was not accessed. Connections will be re-opened transparently to the app, when a new query is executed.

In addition to that, an app can call releaseMemory() when it goes into background or detects memory pressure, e.g. in onTrimMemory()

You also may use ContentProvider. It will do this stuff for you.

Create your own Application context, then open and close the database from there. That object also has an OnTerminate() method you could use to close the connection. I havent tried it yet but it seems a better approach.

@binnyb: I dont like using finalize() to close the connection. Might work, but from what I understand writing code in a Java finalize() method is a bad idea.