Several Exceptions when working with SQLite Database

Question

This code:

public void foo() {
    new Thread() {
        @Override
        public void run() {
            ShaufelDatabaseHelper dbHelper = ShaufelDatabaseHelper.getInstance(parentActivity);
            SQLiteDatabase connection = null;
            Cursor queryData = null;

            final String[] values = new String[8];

            try{
                connection = dbHelper.getReadableDatabase();

                queryData = connection.rawQuery(         // line 132
                    "SELECT name, firstname, section, row, gravenr, type, workorder, comment "+
                    "FROM orders, graves "+
                    "WHERE orders.id=? AND graves.id=orders.id"
                    , new String[] {orderID});

                queryData.moveToFirst(); // line 138

                // ...
        } catch(Throwable t) {
            // ...
        }
    }.start();
}

is giving me these Exceptions:

10-29 15:19:55.103: E/AndroidRuntime(23102): FATAL EXCEPTION: Thread-1387
10-29 15:19:55.103: E/AndroidRuntime(23102): java.lang.IllegalMonitorStateException
10-29 15:19:55.103: E/AndroidRuntime(23102):    at java.util.concurrent.locks.ReentrantLock$Sync.tryRelease(ReentrantLock.java:126)
10-29 15:19:55.103: E/AndroidRuntime(23102):    at java.util.concurrent.locks.AbstractQueuedSynchronizer.release(AbstractQueuedSynchronizer.java:1239)
10-29 15:19:55.103: E/AndroidRuntime(23102):    at java.util.concurrent.locks.ReentrantLock.unlock(ReentrantLock.java:431)
10-29 15:19:55.103: E/AndroidRuntime(23102):    at android.database.sqlite.SQLiteDatabase.unlock(SQLiteDatabase.java:487)
10-29 15:19:55.103: E/AndroidRuntime(23102):    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:62)
10-29 15:19:55.103: E/AndroidRuntime(23102):    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1564)
10-29 15:19:55.103: E/AndroidRuntime(23102):    at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1538)
10-29 15:19:55.103: E/AndroidRuntime(23102):    at de.l_one.app.shaufel.OrderDetailView$3.run(OrderDetailView.java:132)

10-29 15:21:07.953: E/SQLiteQuery(23133): exception: library routine called out of sequence; query: SELECT name, firstname, section, row, gravenr, type, workorder, comment FROM orders, graves WHERE orders.id=? AND graves.id=orders.id
10-29 15:21:07.953: E/SQL order detail(23133): android.database.sqlite.SQLiteMisuseException: library routine called out of sequence
10-29 15:21:07.953: E/SQL order detail(23133): android.database.sqlite.SQLiteMisuseException: library routine called out of sequence
10-29 15:21:07.953: E/SQL order detail(23133):  at android.database.sqlite.SQLiteQuery.nativeFillWindow(Native Method)
10-29 15:21:07.953: E/SQL order detail(23133):  at android.database.sqlite.SQLiteQuery.fillWindow(SQLiteQuery.java:86)
10-29 15:21:07.953: E/SQL order detail(23133):  at android.database.sqlite.SQLiteCursor.fillWindow(SQLiteCursor.java:164)
10-29 15:21:07.953: E/SQL order detail(23133):  at android.database.sqlite.SQLiteCursor.getCount(SQLiteCursor.java:156)
10-29 15:21:07.953: E/SQL order detail(23133):  at android.database.AbstractCursor.moveToPosition(AbstractCursor.java:161)
10-29 15:21:07.953: E/SQL order detail(23133):  at android.database.AbstractCursor.moveToFirst(AbstractCursor.java:201)
10-29 15:21:07.953: E/SQL order detail(23133):  at de.l_one.app.shaufel.OrderDetailView$3.run(OrderDetailView.java:138)

10-29 15:22:52.983: E/AndroidRuntime(23133): FATAL EXCEPTION: Thread-1411
10-29 15:22:52.983: E/AndroidRuntime(23133): java.lang.IllegalStateException: database /data/data/de.l_one.app.shaufel/databases/poidatabase (conn# 0) already closed
10-29 15:22:52.983: E/AndroidRuntime(23133):    at android.database.sqlite.SQLiteDatabase.verifyDbIsOpen(SQLiteDatabase.java:2082)
10-29 15:22:52.983: E/AndroidRuntime(23133):    at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:58)
10-29 15:22:52.983: E/AndroidRuntime(23133):    at android.database.sqlite.SQLiteProgram.compileSql(SQLiteProgram.java:143)
10-29 15:22:52.983: E/AndroidRuntime(23133):    at android.database.sqlite.SQLiteProgram.compileAndbindAllArgs(SQLiteProgram.java:361)
10-29 15:22:52.983: E/AndroidRuntime(23133):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:127)
10-29 15:22:52.983: E/AndroidRuntime(23133):    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:94)
10-29 15:22:52.983: E/AndroidRuntime(23133):    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:53)
10-29 15:22:52.983: E/AndroidRuntime(23133):    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:47)
10-29 15:22:52.983: E/AndroidRuntime(23133):    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1564)
10-29 15:22:52.983: E/AndroidRuntime(23133):    at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1538)
10-29 15:22:52.983: E/AndroidRuntime(23133):    at de.l_one.app.shaufel.OrderDetailView$3.run(OrderDetailView.java:132)

These are not thrown in one run but rather (what appears to be) randomly. There's no pattern when which Exception is thrown, sometimes no Exception is thrown at all.
I use the same approach everywhere in my app and it works without a single problem. I don't have the smallest clue where the IllegalStateException is coming from because the database aren't closed, at least not by me. (according to some answers on SO that is ok and in some cases even preferable to closing your dbs manually).

Notes to the code:
ShaufelDatabaseHelper is a Singleton that extends SQLiteOpenHelper. It is used across different Activities which works in general, this seems to be the only exception.

I just tested that on a second device, where no exception was thrown and everything works.
The device that throws Exceptions is a lenovo thinkpad running android 4.0.3.
The one running withouth Exceptions is a HTC Sensation Z710e running Android 2.3.4.

Has anyone an idea what could cause this?

E: I don't know if it helps but this is logged before the SQLiteMisuseException:

10-29 16:07:46.530: I/SqliteDatabaseCpp(23728): sqlite returned: error code = 21, msg = API called with NULL prepared statement, db=/data/data/de.l_one.app.shaufel/databases/poidatabase
10-29 16:07:46.530: I/SqliteDatabaseCpp(23728): sqlite returned: error code = 21, msg = misuse at line 58929 of [8609a15dfa], db=/data/data/de.l_one.app.shaufel/databases/poidatabase
10-29 16:07:46.530: I/SqliteDatabaseCpp(23728): sqlite returned: error code = 21, msg = API call with invalid database connection pointer, db=/data/data/de.l_one.app.shaufel/databases/poidatabase
10-29 16:07:46.530: I/SqliteDatabaseCpp(23728): sqlite returned: error code = 21, msg = misuse at line 106340 of [8609a15dfa], db=/data/data/de.l_one.app.shaufel/databases/poidatabase
10-29 16:07:46.530: I/SqliteDatabaseCpp(23728): sqlite returned: error code = 21, msg = API call with invalid database connection pointer, db=/data/data/de.l_one.app.shaufel/databases/poidatabase
10-29 16:07:46.530: I/SqliteDatabaseCpp(23728): sqlite returned: error code = 21, msg = misuse at line 106271 of [8609a15dfa], db=/data/data/de.l_one.app.shaufel/databases/poidatabase

Answer 1

The reason

if by any chance another thread closed the database just between your call to getReadableDatabase() and rawQuery() or during the execution of rawQuery() you will get this exception. this is because your SQLiteOpenHelper is a singleton (and it should be), which means that if some other thread closed the database, the single internal mDatabase member that holds the database connection will be closed.

My advice:

implement your own close() method to your SQLiteOpenHelper derivative. in your implementation check a reference count of opened connections before actually closing it (calling SQLiteOpenHelper.close()).

private int openedConnections = 0;
//...
public synchronized SQLiteDatabase getReadableDatabase() {
    openedConnections++;
    getReadableDatabase();
}

public synchronized void close() {
    openedConnections--;
    if (openedConnections == 0) {
        close();
    }
}

let me know if it worked! :)