Is it OK to have one instance of SQLiteOpenHelper shared by all Activities in an Android application?

Question

Would it be OK to have a single instance of SQLiteOpenHelper as a member of a subclassed Application, and have all Activities that need an instance of SQLiteDatabase get it from the one helper?

Gabble

Answer 1

Having a single SQLiteOpenHelper instance can help in threading cases. Since all threads would share the common SQLiteDatabase, synchronization of operations is provided.

However, I wouldn't make a subclass of Application. Just have a static data member that is your SQLiteOpenHelper. Both approaches give you something accessible from anywhere. However, there is only one subclass of Application, making it more difficult for you to use other subclasses of Application (e.g., GreenDroid requires one IIRC). Using a static data member avoids that. However, do use the Application Context when instantiating this static SQLiteOpenHelper (constructor parameter), so you do not leak some other Context.

And, in cases where you aren't dealing with multiple threads, you can avoid any possible memory leak issues by just using one SQLiteOpenHelper instance per component. However, in practice, you should be dealing with multiple threads (e.g., a Loader), so this recommendation is only relevant for trivial applications, such as those found in some books... :-)

Who

:) I actually used your LoaderEx classes and Advanced Android book to learn how to move database access off the UI thread. Thank you so much for both. They were a huge help. – Gabble

So when should you even call "close", if all classes and threads will reach the same exact instance? – Aculeate

@androiddeveloper: Generally, you don't call close(), unless you happen to be in a circumstance where you're 100% sure it is safe to call close(). – Who

@Who So it's better to leave it open through the rest of the time? wouldn't it make the OS "want" to close the process more when the app is on the background this way? Especially because this class' real engine is written in C/C++ which puts the memory usage outside of the heap? – Aculeate

@androiddeveloper: "wouldn't it make the OS "want" to close the process more when the app is on the background this way?" -- not that I am aware of. "Especially because this class' real engine is written in C/C++ which puts the memory usage outside of the heap?" -- every process has SQLite in it, as it is in the zygote AFAIK. The RAM for an open database is not especially large AFAIK. – Who

@Who I have a question about it: what about "getWritableDatabase" or "getReadableDatabase" ? would it be a good thing to never close them, and use a single instance for each of them, to be used by various threads? Is it also thread-safe to do it? – Aculeate

@androiddeveloper: I do not hold onto the results of those methods, just calling them on a singleton SQLiteOpenHelper as needed. If you happen to be in a position to close the SQLiteOpenHelper, that will close the underlying SQLiteDatabase. SQLiteOpenHelper manages a single instance of SQLiteDatabase, and the thread safety logic is in SQLiteDatabase. – Who

@Who So you say it should be ok to have a single call to them. I think it's best to close them when not needed though. maybe it depends on how much you use the DB. Thank you again. – Aculeate

@androiddeveloper: "I think it's best to close them when not needed though" -- we discussed this previously. If you know a safe time to close them, great, and do so. Frequently, we do not know when a safe time is to close the database. – Who

@Who Before, I asked about closing "SQLiteOpenHelper" . Now I've asked about "SQLiteDatabase". Do you mean that this is relevant for both? – Aculeate

@androiddeveloper: Yes. If you are using a SQLiteOpenHelper, you will be using it to get at your SQLiteDatabase. Close the helper, and it closes the database. – Who

@Who I meant if the tip is relevant for both questions, but ok. Thank you. – Aculeate

@Who My application is using multiple threads to read database is there a performance hit if i use singleton instance to read database from two different threads than opening a new instance in different threads – Lowther

@user1530779: You will run into many problems if you try "opening a new instance in different threads", unless you are doing your own thread synchronization, in which case I would not expect much performance difference. – Who

@Who Also if you could explain the difference between in terms of performance of using developer.android.com/reference/android/database/sqlite/… – Lowther

@Who I understand the problems if im writing and reading at the same time but if im ONLY READING database from multiple threads with multiple instances there should not be a problem. Can you elaborate the problems please – Lowther

@user1530779: "if im ONLY READING database from multiple threads with multiple instances there should not be a problem" -- unless the database is immutable (e.g., you are using SQLiteAssetHelper and have no code to update it), something will be writing to the database. After all, you don't need write-ahead logging if you are not writing. Beyond that, I have no idea what to expect with multiple threads reading from separate SQLiteDatabase instances backed by the same database file, as that is a niche scenario. You may wish to ask a separate Stack Overflow question on this. – Who

@Who Raising a separate question for that but does write-ahead logging have any cons other than extensive memory usage in terms of performance ? – Lowther

@Who #31209995 – Lowther

@Who I certainly think using multiple instances in scenarios if you are reading database from Multiple threads would be faster because all the calls would not be serial and non synchronized while in single instance all calls to database would be synchronized and serial ie no parallel processing even in case of read So CLEARLY ITS A PERFORMANCE HIT IN SCENARIOS WHERE ONLY READING OF DATABASE IS HAPPENING IN MULTIPLE THREADS – Lowther

@Who check out my answer below – Lowther

@Who I'm late to this thread, but can you confirm it is safe to NEVER call SQLiteOpenHelper.close()? I would like a single instance for the application (created and opened for writing at Application.onCreate), but there is no Application.onDestroy I can deterministically call close in. Will the resource be properly cleaned up when/if application is killed by OS at some point? – Buckshot

@Sean: "can you confirm it is safe to NEVER call SQLiteOpenHelper.close()?" -- I have been suitably convinced at this point that it is safe to never call close(). SQLite is transactional; all buffers are flushed and stuff written to disk by the time a transaction ends. There are no unflushed buffers or something that would cause data loss. "Will the resource be properly cleaned up when/if application is killed by OS at some point?" -- well, the process goes away, if that is what you mean by "cleaned up". – Who

@Who Thanks. By "resource be properly cleaned up" I meant system (or db engine) resources (if any) allocated to support the db connection when I call getWritableDatabase(). – Buckshot

@Sean: No, the process just gets terminated. All your memory, threads, and so on go poof when that happens. – Who

@CommonsWare, I have a situation where I need to keep multiple databases (Different SQLiteOpenHelper instances) in open state. In android's sqlite3, is there any limit that maximum number of different database SQLiteOpenHelpers in open state? Note: There is only one SQLiteOpenHelper instance per database. – Ame

@UmaSankar: I do not know the answer, sorry. – Who

Answer 2

Click here to see my blog post on this subject.

---

CommonsWare is right on (as usual). Expanding on his post, here is some sample code that illustrates three possible approaches. These will allow access to the database throughout the application.

Approach #1: subclassing `Application`

If you know your application won't be very complicated (i.e. if you know you'll only end up having one subclass of Application), then you can create a subclass of Application and have your main Activity extend it. This ensures that one instance of the database is running throughout the Application's entire life cycle.

public class MainApplication extends Application {

    /**
     * see NotePad tutorial for an example implementation of DataDbAdapter
     */
    private static DataDbAdapter mDbHelper;

    /**
     * Called when the application is starting, before any other 
     * application objects have been created. Implementations 
     * should be as quick as possible...
     */
    @Override
    public void onCreate() {
        super.onCreate();
        mDbHelper = new DataDbAdapter(this);
        mDbHelper.open();
    }

    public static DataDbAdapter getDatabaseHelper() {
        return mDbHelper;
    }
}

Approach #2: have `SQLiteOpenHelper` be a static data member

This isn't the complete implementation, but it should give you a good idea on how to go about designing the DatabaseHelper class correctly. The static factory method ensures that there exists only one DatabaseHelper instance at any time.

/**
 * create custom DatabaseHelper class that extends SQLiteOpenHelper
 */
public class DatabaseHelper extends SQLiteOpenHelper { 
    private static DatabaseHelper mInstance = null;

    private static final String DATABASE_NAME = "databaseName";
    private static final String DATABASE_TABLE = "tableName";
    private static final int DATABASE_VERSION = 1;

    private Context mCxt;

    public static DatabaseHelper getInstance(Context ctx) {
        /** 
         * use the application context as suggested by CommonsWare.
         * this will ensure that you dont accidentally leak an Activitys
         * context (see this article for more information: 
         * http://developer.android.com/resources/articles/avoiding-memory-leaks.html)
         */
        if (mInstance == null) {
            mInstance = new DatabaseHelper(ctx.getApplicationContext());
        }
        return mInstance;
    }

    /**
     * constructor should be private to prevent direct instantiation.
     * make call to static factory method "getInstance()" instead.
     */
    private DatabaseHelper(Context ctx) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.mCtx = ctx;
    }
}

Approach #3: abstract the SQLite database with a `ContentProvider`

This is the approach I would suggest. For one, the new LoaderManager class relies heavily on ContentProviders, so if you want an Activity or Fragment to implement LoaderManager.LoaderCallbacks<Cursor> (which I suggest you take advantage of, it is magical!), you'll need to implement a ContentProvider for your application. Further, you don't need to worry about making a Singleton database helper with ContentProviders. Simply call getContentResolver() from the Activity and the system will take care of everything for you (in other words, there is no need for designing a Singleton pattern to prevent multiple instances from being created).

Hope that helps!

Embellishment

Just thought I'd point out that CommonsWare's LoaderEx library shows how to use the LoaderManager.LoaderCallbacks interface and Loaders when working directly with a SQLite database instead of a ContentProvider. github.com/commonsguy/cwac-loaderex – Gabble

You need to call super.onCreate() from your override. Possibly the same for onTerminate() - not sure. – Defraud

oops, forgot about that... you need it for both methods. fixed! – Embellishment

on second thought, there might not be a definite answer for onTerminate(), since the method might not even be called. including it seems safer though :) – Embellishment

Why this assignment ` this.mCtx = ctx;`? – Workhouse

@Mirko IDK... maybe I edited this at some point and forgot about that. Doesn't look like it is at all necessary. An updated blog post on the topic can be found here :) – Embellishment

Thanks Alex! I keep seeing that assignment in all the tutorial/articles about Android database adapters and I was wondering why...probably an OOP best practice?? ;) – Workhouse

It's more because the adapters are subclasses of SQLiteOpenHelper, which is a helper class to manage database creation and version management. Most tutorials/articles/apps use it because it's easier than opening/closing/managing a raw SQLiteDatabase on your own. – Embellishment

@AlexLockwood: For Approach #2. Do we have to close DatabaseHelper when application onTerminate? – Creep

@LocHa According to the documentation, onTerminate() will never be called in production environments, so overriding onTerminate() will have no effect. – Embellishment

@AlexLockwood In that case, approach #1 is not reliable since you have overridden onTerminate() there. – Pocahontas

With the singleton, in Android Studio I get this: "Do not place Android context classes in static fields; this is a memory leak (and also breaks Instant Run)" for private static DatabaseHelper mInstance. Is there a way to avoid this issue? – Terrel

When implementing the second approach which is singleton pattern, the main activity being the first class to instantiate db helper, should the same context be retained for all activities? If not, how to pass each context without reinitializing everytime – Rosecan

Answer 3

I have written MultiThreadSQLiteOpenHelper which is an enhanced SQLiteOpenHelper for Android applications where several threads might open and close the same sqlite database.

Instead of calling close method, threads ask for closing the database, preventing a thread from performing a query on a closed database.

If each thread asked for closing, then a close is actually performed. Each activity or thread (ui-thread and user-threads) performs an open call on database when resuming, and asks for closing the database when pausing or finishing.

Source code and samples available here: https://github.com/d4rxh4wx/MultiThreadSQLiteOpenHelper

Gounod

Why didn't you implement getReadableDatabase() ? – Anhydride

Answer 4

I did a lot of research on this topic and I agree with all the points mentioned by commonware . But i think there is an important point everyone is missing here , Answer to this question is entirely dependent on your Use Case so if your application is reading databases via multiple threads and only reading using Singleton has a huge performance hit as all the functions are synchronized and are executed serially as there is a single connection to database Open source is great, by the way. You can dig right into the code and see what’s going on. From that and some testing, I’ve learned the following are true:

Sqlite takes care of the file level locking.  Many threads can read, one can write.  The locks prevent more than one writing.
Android implements some java locking in SQLiteDatabase to help keep things straight.
If you go crazy and hammer the database from many threads, your database will (or should) not be corrupted.

If you try to write to the database from actual distinct connections at the same time, one will fail. It will not wait till the first is done and then write. It will simply not write your change. Worse, if you don’t call the right version of insert/update on the SQLiteDatabase, you won’t get an exception. You’ll just get a message in your LogCat, and that will be it.

The first problem, real, distinct connections. The great thing about open source code is you can dig right in and see what’s going on. The SQLiteOpenHelper class does some funny things. Although there is a method to get a read-only database connection as well as a read-write connection, under the hood, its always the same connection. Assuming there are no file write errors, even the read-only connection is really the single, read-write connection. Pretty funny. So, if you use one helper instance in your app, even from multiple threads, you never really using multiple connections.

Also, the SQLiteDatabase class, of which each helper has only one instance, implements java level locking on itself. So, when you’re actually executing database operations, all other db operations will be locked out. So, even if you have multiple threads doing stuff, if you’re doing it to maximize database performance, I have some bad news for you. No benefit.

Interesting Observations

If you turn off one writing thread, so only one thread is writing to the db, but another reading, and both have their own connections, the read performance shoots WAY up and I don’t see any lock issues. That’s something to pursue. I have not tried that with write batching yet.

If you are going to perform more than one update of any kind, wrap it in a transaction. It seems like the 50 updates I do in the transaction take the same amount of time as the 1 update outside of the transaction. My guess is that outside of the transaction calls, each update attempts to write the db changes to disk. Inside the transaction, the writes are done in one block, and the overhead of writing dwarfs the update logic itself.

Lowther

Great research. Thanks for sharing. – Gabble

Thanks @Julian so the inference here is that if your database is being only Read Don't use SINGLETON but create different instances as that would allow concurrent access as a single instance would block all other operations when reading a database and if you need multiple readers and one writer use WAL feature with separate instances so SINGLETON should not be used according to me – Lowther

Answer 5

Yes, that is the way you should go about it, having a helper class for the activities that need an instance of the Database.

Bonnes

how to pass context as an argument to the constructor of database helper class that extends sqliteopenhelper? Since it varies for different activities, is there a base context that we can pass as an argument? – Rosecan