"error code 5: database is locked" when using a ContentProvider

Question

I have an application that runs an activity with a service in a separate process that is started and bound to the activity. The service contains a handler that posts a runnable to be run after a delay.

I want each component to log to the database, so I implemented a content provider that deals with database access and I call it from the service or activity via extended AsyncTask sub-classes.

This all works beautifully on the emulator, but when I run it in debug on my phone I get a sporadic database locked error on my database writing:

UPDATE

I made some changes to my database handling and the error has changed slightly.

ERROR/Database(15235): Error inserting MY_MESSAGE
ERROR/Database(15235): android.database.sqlite.SQLiteException: error code 5: database is locked
ERROR/Database(15235):     at android.database.sqlite.SQLiteStatement.native_execute(Native Method)
ERROR/Database(15235):     at android.database.sqlite.SQLiteStatement.execute(SQLiteStatement.java:61)
ERROR/Database(15235):     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1591)
ERROR/Database(15235):     at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1435)
ERROR/Database(15235):     at mypackagename.DatabaseHelper.insertLogging(DatabaseHelper.java:190)
ERROR/Database(15235):     at mypackagename.ContentProvider.insert(ContentProvider.java:139)
ERROR/Database(15235):     at android.content.ContentProvider$Transport.insert(ContentProvider.java:198)
ERROR/Database(15235):     at android.content.ContentResolver.insert(ContentResolver.java:604)
ERROR/Database(15235):     at mypackagename.Activity$LogToDatabase.doInBackground(Activity.java:642)
ERROR/Database(15235):     at mypackagename.Activity$LogToDatabase.doInBackground(Activity.java:1)
ERROR/Database(15235):     at android.os.AsyncTask$2.call(AsyncTask.java:185)
ERROR/Database(15235):     at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:306)
ERROR/Database(15235):     at java.util.concurrent.FutureTask.run(FutureTask.java:138)
ERROR/Database(15235):     at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1088)
ERROR/Database(15235):     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:581)
ERROR/Database(15235):     at java.lang.Thread.run(Thread.java:1019)

I didn't put much detail before in because I thought it was an issue with different processes or threads, but now I'm thinking the problem is more likely located in the code calling the database.

Questions:

1) Why am I hitting locks when I'm using a ContentProvider?
2) Why does this not show up on an equivalent API 2.3.3 emulator?
3) Does the fact that none of my code catches an exception mean that the error was handled properly and I can ignore it?
4) I read in another place someone suggesting adjusting the busy timeout. How would I do that?

The irony that it's my debug logging that's causing the error is not lost on me.

If I can't solve it, my next step is to bundle the logging messages up in a list and dump them out in batches of ten at a time.

Here's the path the through the code to the error:

Activity:

private void logDatabaseMessage(String status, String message)
{
    String[] args = {status, message};
    LogToDatabase logTask = new LogToDatabase();
    logTask.execute(args);      
}

private class LogToDatabase extends AsyncTask<String, Integer, Void>
{
    @Override
    protected Void doInBackground(final String... args) 
    {
        try
        {
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS"); 
            String dateText = dateFormat.format(new Date());

            ContentValues loggingValues = new ContentValues();
            loggingValues.put(MyContentProvider.LOGGING_DATETIME, dateText);
            loggingValues.put(MyContentProvider.LOGGING_STATUS, args[0]);
            loggingValues.put(MyContentProvider.LOGGING_MESSAGE, args[1]);
            getContentResolver().insert(MyContentProvider.LOGGING_CONTENT_URI, loggingValues);
        }
        catch (Exception ex)
        {
            Log.e(TAG, "LogToDatabase.doInBackground threw exception: " + ex.getMessage());
            ex.printStackTrace();
        }               

        return null;
    }
}

ContentProvider:

@Override
public Uri insert(Uri uri, ContentValues values) 
{
    Uri _uri = null;
    long rowID = 0;

    try
    {
        switch (uriMatcher.match(uri))
        {
            case LOGGING:
                rowID = dbHelper.insertLogging(values);
                if (rowID == 0)
                    throw new SQLException("Failed to insert row into " + uri);

                _uri = ContentUris.withAppendedId(LOGGING_CONTENT_URI, rowID);
                break;

            default: throw new SQLException("Failed to insert row into " + uri);
        }

        if (rowID != 0)
            getContext().getContentResolver().notifyChange(_uri, null);    
    }
    catch (Exception ex)
    {
        Log.e(TAG, LogPrefix + "insert threw exception: " + ex.getMessage());
        ex.printStackTrace();
    }

    return _uri;    
}

DatabaseHelper:

public long insertLogging(ContentValues values)
{
    long rowID = 0;

    try
    {
        rowID = db.insert(LOGGING_TABLE, null,  values);
    }
    catch (Exception ex)
    {
        Log.e(TAG, LogPrefix + "ERROR: Failed to insert into logging table: " + ex.getMessage());
        ex.printStackTrace();
    }

    return rowID;
}

Solution 1

Are you possibly accessing the database using multiple SQLiteDatabase (or perhaps SQLiteOpenHelper) instances?

You can only have one connection to the database otherwise you'll get the errors you've experienced.

SQLiteDatabase itself is thread safe, so you can access it concurrently.

Solution 2

Also check the multiprocess flag of the content provider in the manifest. Each process would have a separate connection to the database.

Comments

[ponomy]

SQLiteDatabase and SQLiteOpenHelper appear in only one of my classes, which is a member in my content provider, but I call the content provider from both the service and the activity. Is that OK?

[dhaag23]

That's ok as long as you only have one SQLiteOpenHelper and your only instance of SQLiteDatabase is the one you get from SQLiteOpenHelper.getWritableDatabase() (or getReadableDatabase()).

[rahul]

@dhaag23 If I want to access the DB using multiple threads , ie, one thread for writing data to the DB and other for reading from the same DB concurrently, Hw can I use the same instance of the Helper class?

[dhaag23]

There's nothing special you have to do to use SQLiteOpenHelper from multiple threads as the SQLiteDatabase you get from it is already thread safe. Again, the key is to use only one SQLiteOpenHelper.

[Sergey Dryganets]

Hmm, and what about transactions? According to the documentation of SQLite transaction is bound to the connection. So in order to perform transactions in multiple threads, I need to have two connections to the database.

[Denny]

I guess we'll never know