Concurrent access to a SQLite database in Android - db already closed

Question

I read a lot of topics on this subjet but no one could answer my question.

I access my database from concurrent threads, my SQLiteOpenHelper implements the design pattern of the singleton so I have only one instance for my app.

I access my database with code like this :

 SQLiteDatabase db = DatabaseHelper.getInstance().getWritableDatabase();
 ...
 Do some update in the DB
 ...
 db.close();

I don't understand why I still get "db already closed" error, shouldn't the getWritableDatabase() method lock the database until close() is called ? Other getWritableDatabase() calls from other threads should wait until the db is closed ? Is that right or did I miss something ?

Vince

Answer 1

Expanding on elhadi's answer I have come across similar issues when opening and closing database connections across multiple async tasks. From my investigation at the time it became clear that it was unnecessary to constantly open and close db connections. The approach I ended up adopting was sub-classing Application and performing a single db open during onCreate and a single db close onTerminate. I then set up a static getter for retrieving the already opened SQLiteDatabase object. Not DI (Dependency Injection) friendly but Android can't really do that, yet.

Something like this;

    public class MainApplication extends Application {
          private static SQLiteDatabase database;

          /**
           * 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();
          try {
           database = SQLiteDatabase.openDatabase("/data/data/<yourdbpath>", null, SQLiteDatabase.OPEN_READWRITE);
          } catch (SQLiteException e) {
            // Our app fires an event spawning the db creation task...
           }
         }


          /**
           * Called when the application is stopping. There are no more 
           * application objects running and the process will exit.
           * <p>
           * Note: never depend on this method being called; in many 
           * cases an unneeded application process will simply be killed 
           * by the kernel without executing any application code...
           * <p>
           */
          @Override
          public void onTerminate() {
            super.onTerminate();
            if (database != null && database.isOpen()) {
              database.close();
            }
          }


          /**
           * @return an open database.
           */
          public static SQLiteDatabase getOpenDatabase() {
            return database;
          }
    }

Reading the JavaDoc back I have certainly plagerised this from somewhere but this static single db open/close resolved this issue you are having. There is another answer on SO somewhere describing this solution.

More detail:

In response to Fr4nz's comment about an NPE below, I have provided more details of our specific implementation.

Short version

The below 'full picture' is difficult to grasp without a good understanding of BroadcastReceivers. In your case (and as a first off) add in your DB creation code and intialise and open the database after you have created the database. So write;

      try {
       database = SQLiteDatabase.openDatabase("/data/data/<yourdbpath>", null, SQLiteDatabase.OPEN_READWRITE);
      } catch (SQLiteException e) {
        // Create your database here!
        database = SQLiteDatabase.openDatabase("/data/data/<your db path>", null, SQLiteDatabase.OPEN_READWRITE);
       }
     }

Long version

Yes, there is a little bit more to it than just the above code. Notice my comment on the exception catch in the first instance (i.e. the first time your application is run, ever). Here is says, "Our app fires an event spawning the db creation task". What actually happens in our app is that a listener (Android's BroadcastReceiver framework) is registered and one of the first things the main application activity does is check that the database static variable in MainApplication is not null. If it is null, then an async task is spawned that creates the db, which when it finishes (i.e. runs the onPostExecute() method) ultimately fires the event which we know will be picked up by the listener we registered in the try-catch. The receiver lives under the MainApplication class as an inner-class and look like this;

    /**
    * Listener waiting for the application to finish
    * creating the database.
    * <p>
    * Once this has been completed the database is ready for I/O.
    * </p>
    *
    * @author David C Branton
    */
      public class OpenDatabaseReceiver extends BroadcastReceiver {
        public static final String BROADCAST_DATABASE_READY = "oceanlife.core.MainApplication$OpenDatabaseReceiver.BROADCAST_DATABASE_READY";

        /**
         * @see android.content.BroadcastReceiver#onReceive(android.content.Context, android.content.Intent)
         */
        @Override
        public void onReceive(final Context context, final Intent intent) {
          Log.i(CreatedDatabaseReceiver.class.getSimpleName(), String.format("Received filter event, '%s'", intent.getAction()));
          database = SQLiteDatabase.openDatabase("/data/data/<your db path>", null, SQLiteDatabase.OPEN_READWRITE);
          unregisterReceiver(openDatabaseReceiver);

          // Broadcast event indicating that the creation process has completed.
          final Intent databaseReady = new Intent();
          databaseReady.setAction(BROADCAST_DATABASE_READY);
          context.sendBroadcast(databaseReady);
        }
      }

So the summary of the start-up process for the first install is like so;

1. Class: MainApplication, role- check there is a database?
   • Yes? database variable is initialised
   • No? Receiver registered (the OpenDatabaseReceiver)
2. Class: MainActivity: role- landing activity for the application and initially checks that the database variable is not null.
   • database is null? Does not add in the fragments that perform I/O and adds in dialog saying "creating the application database" or similar.
   • database is not null? Carries on with main application execution flow, adds in listings backed by db etc
3. Class: DatabaseCreationDialogFragment: role- spawns async task to create the database.
   • Registers new receiver listening for when the database has been created.
   • On collecting the "I've created the database" message fires another event (from the receiver) telling the app to open the database.
4. Class: MainApplication: role 2- listen for the "database created" message.
   • Receiver described above (OpenDatabaseReceiver) opens the database and broadcast (by another event!) that the database is ready to be used.
5. Class: MainActivity: role 2- Picks up the "database is ready" meassage, gets rid of "we're creating the database" dialog and gets on with displaying the data/functionality in the app.

Peace is restored.

Sabaean

Don't forget to update your Manifest.xml with the path to the new application "name" if you attempt this. – Sabaean

Hi I'm having some issues with the database path right now. Apparently it cannot find the DB and getOpenDatabase throws NullPointerException. Could I use instead of openDatabase() something like DatabaseHelper.getInstance().getWritableDatabase() (only once) ? – Vince

(Significantly) more details added Fr4nz. Have a read and sketch some stuff out. If you want to know our reasons for the 'Long version' we'd best pick that up in "chat". It's all about roles and responsibilities of Fragments and informing the user what is going on. – Sabaean

Thank's for your detailed answer, I'm gonna try this – Vince

Good summary! (even if outlining a somewhat imho. silly design - dialogs have no business spawning threads and receivers) – Dobbins

The fragment starts a service which host's an AsyncTask. The listener gets invoked when the work is completed. The dialog is blocking at this time (initial db creation). Loose coupling, high cohesion. – Sabaean

Answer 2

If you call DatabaseHelper.getInstance().getWritableDatabase() in your thread, i advice you to manage it before starting your threads. you open your db in main program, you call your threads. after threads termination, you close your db in the main program.

Davilman

Yes I call DatabaseHelper.getInstance().getWritableDatabase() in each of my thread. Thank's for your answer I'm gonna try this. But was my reflexion right in theory ? – Vince

In your solution, am I supposed to call getWritableDatabase() before starting the threads in order to initiate the database ? I don't really see how it's supposed to help because I still need to call getWritableDatabase() in my thread, right ? – Vince