Android - Database is Locked

I have an application that I can insert data into a SQLite database. There's a service thread running every sixty seconds checking if a condition is true and raising an alarm if necessary. Think "calendar" application.

I have lots of activities with a reference to their own SQLiteOpenHelper, and their own SQLiteDatabase object.

The application was working pretty well, but one of the main update applications I decided needed a progress dialog subject - that's another subject, but if you can tell me how I can get the spinner to spin that'd be great. It does however display.

But to get it to display, I needed to put it in a thread.

Creating this thread, I started to get loads of "database is locked" messages in the Log.

I figured the issue may have been related to the fact I had a task in the background running every sixty seconds - even though it visually only took a couple of seconds to do the update, so I placed the main write transactions for the updater function in a beginTransaction block. That's the only place I used one.

Everything seemed good, but then I started getting more spurious errors about the database being locked all over the place.

It's encouraged me to do some tidying up, but it seems after I do the first beginTransaction, any further database modifications I want to make fail with a report that the database is locked, despite that fact I do call db.close. I even close the database helper object and cursor just to be on the safe side.

I was able to clear up most of the warnings with regards to cursors still being open, eg

E/Database(  678): android.database.sqlite.DatabaseObjectNotClosedException: Application did not close the cursor or database object that was opened here

I couldn't figure out what was locking the database as everything was being closed.

I then decided to remove the "beginTransaction" which stabilised things - I could now navigate my program again. But Im still getting a few random locking issue开发者_JAVA技巧s.

E/Database( 1304): Failure 5 (database is locked) on 0x26e5d8 when executing 'INSERT INTO

Is it possible to figure out what is locking the database? I can see what is grumbling about the lock, but not what is locking it.

What is the best way to handle concurrent database updates/reads like this? I've read a lot about synchronized and ContentProviders but I must admit, it is a little bit big for me.

Any pointers on the "best" way of doing things?

And can I find out what is locking the database?

Thanks Simon

class NAME extends SQLiteOpenHelper

add:

@Override
public void onOpen(SQLiteDatabase db) {
    super.onOpen(db);
      if (!db.isReadOnly()) {
          db.execSQL("PRAGMA foreign_keys=ON;");
          Cursor c = db.rawQuery("PRAGMA foreign_keys", null);
             if (c.moveToFirst()) {
                 int result = c.getInt(0);
             }
             if (!c.isClosed()) {
                    c.close();
             }        
      }
}

You may want to look at the latest version of Berkeley DB as an option. It provides a SQLite3-compatible SQL API (so that your application can stay the same) and it provides a finer granularity of locking and therefore better concurrency. Berkeley DB supports multiple concurrent read and write threads to actively access the database at the same time. You can find out additional information about Berkeley DB on our website.