Android SQLite DB When to Close

Question

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!

Gumwood

Answer 1

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.

Ribbing

I could speed up database access by a factor of 2 with this approach (keep database open), thanks – Presbyopia

Spinning is a very bad technique. See my answer below. – Microchemistry

@Microchemistry good point. I believe i posted this answer before API 16 was available, but I could be wrong – Ribbing

@binnyb Yes, I remember that in older APIs there were issues with concurrent access and I solved them with synchronized access to SQLiteOpenHelper and calling getReadableDatabase() and SQLiteDatabase.lose inside this synchronized block. – Microchemistry

@binnyb I think it's better to update your answer so it did not mislead people. – Microchemistry

WARN isDbLockedByOtherThreads() is Depricated and returns false since API 16. Also checking isDbLockedByCurrentThread() will give infinite looping because it stops the current thread and nothing can 'unlock DB' so that this method returns true. – Perineuritis

Really bad idea to put a while loop. Will loop forever and block the device work. – Bestead

Note that this answer was written in late 2010 and thus the while-loop logic is no longer applicable. – Unilateral

@KennethWorden care to elaborate? while loops certainly are used in 2018 – Ribbing

@binnyb The while(db.isDbLockedByCurrentThread() || db.isDbLockedByOtherThreads()) { ... } logic is no longer relevant, both methods are deprecated so the loop will not work as originally intended. – Unilateral

Answer 2

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.

Microchemistry

There is no point in using one instance per thread. SQLiteOpenHelper is thread safe. This is also very memory inefficient. Instead an app should keep a single instance of SQLiteOpenHelper per-database. For better concurrency, it is recommended to use WriteAheadLogging which provides connection pooling for up to 4 connections. – Darlinedarling

@Darlinedarling I meant that. "Use singleton (= one) SQLiteOpenHelper in every thread", not "per thread". – Microchemistry

Answer 3

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.

Perineuritis

Thank you, after reading your words "after SQLiteDatabase.close(), SQLiteOpenHelper.getWritableDatabase() will return a new instance" I realised that at last I have an answer to an old problem of my application. To a problem, which became critical after migration to Android 9 (see https://mcmap.net/q/212527/-android-p-39-sqlite-no-such-table-error-39-after-copying-database-from-assets ) – Cockatoo

Answer 4

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)

Aventine

These days it's become DEPRECATED. – Perineuritis

Answer 5

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()

Darlinedarling

Answer 6

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

Curd

Answer 7

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.

Foppish

You don't want to rely on finalize because you never know when it's going to be called. An object may stay in limbo until the GC decides to clean it, and only then finalize() will be called. That's why it's useless. The correct way to do it is to have a method that is called when the object is no longer used (regardless of being garbage collected or not), and that's exactly what onTerminate() is. – Abide

Docs say pretty clearly that onTerminate won't be called in a production environment - developer.android.com/reference/android/app/… – Holloway