androidandroid-roomsqlcipher-android

TimeoutException: net.sqlcipher.database.SQLiteCompiledSql.finalize() timed out after 10 seconds (Android)

In my android app I am using Room with sqlcipher library for encrypt/decrypt. Often I see in Crashlytic the following crash:

java.util.concurrent.TimeoutException: net.sqlcipher.database.SQLiteCompiledSql.finalize() timed out after 10 seconds at sun.misc.Unsafe.park(Native Method) at java.util.concurrent.locks.LockSupport.park(LockSupport.java:190) at java.util.concurrent.locks.AbstractQueuedSynchronizer.parkAndCheckInterrupt(AbstractQueuedSynchronizer.java:868) at java.util.concurrent.locks.AbstractQueuedSynchronizer.acquireQueued(AbstractQueuedSynchronizer.java:902) at java.util.concurrent.locks.AbstractQueuedSynchronizer.acquire(AbstractQueuedSynchronizer.java:1227) at java.util.concurrent.locks.ReentrantLock$FairSync.lock(ReentrantLock.java:231) at java.util.concurrent.locks.ReentrantLock.lock(ReentrantLock.java:294) at net.sqlcipher.database.SQLiteDatabase.lock(SQLiteDatabase.java:567) at net.sqlcipher.database.SQLiteCompiledSql.releaseSqlStatement(SQLiteCompiledSql.java:104) at net.sqlcipher.database.SQLiteCompiledSql.finalize(SQLiteCompiledSql.java:146) at java.lang.Daemons$FinalizerDaemon.doFinalize(Daemons.java:289) at java.lang.Daemons$FinalizerDaemon.runInternal(Daemons.java:276) at java.lang.Daemons$Daemon.run(Daemons.java:137) at java.lang.Thread.run(Thread.java:929)

The line where it crash is SQLiteDatabase.lock() line 567

Previously it was line 566, but in that method I inserted a check: if the database is not open -> return and don't proceed with locking, but it didn't help and the crash appeared again. I think that this crash may be because the garbage collecting happens when the app is in the background (our app has a foreground service that works all the time). But not sure how to fix it. As for Room: I don't close it, it is open all the time, because my app works all the time, so need it often. And close it after every query to the database is the bad practice.

I asked the developers of sqlcipher, but they don't know, what can cause this crash. Maybe anybody knows?

Solution

  • I've been seeing this in Crashlytics for quite a while, and seemingly has been able to fix this in the end completely, I'll post the full research here.

    Issue

    For queries like this one:

    @Query("DELETE FROM table WHERE id NOT IN (:ids)")
abstract fun deleteNotInList(ids: List<String>): Int

    Room generates code that doesn’t clean cursor and generated statements (on the image below, compare how the method one doesn’t use cursor, and doesn’t call release() methods, compared to the bottom one which calls cursor.close(); method names and queries are slightly different there, cause I simplified the snippets):

    enter image description here

    In this scenario statements are kept in memory un-released, and the releasing shifts to GC phase, inside finalize() method. And finalize() in SqlCipher (SQLiteCompiledSql) in turn needs to lock the Database in order to release the statement:

    enter image description here

    Problem is that the Database might be locked for a long transaction for more than 10 sec (or a batch of shorter transactions, awakening order using such locks isn’t guaranteed and isn’t fair).

    GC watchdog crashes the thread when it reaches 10/20 seconds depending on the OS/JVM exact version.

    Solution

    Is to rewrite all DELETE / UPDATE using IN operator with manual raw queries like this:

    @RawQuery
abstract fun deleteNotInListRaw(query: SimpleSQLiteQuery): Int

fun deleteNotInList(
    ids: List<String>
) {
    deleteNotInListRaw(
        SimpleSQLiteQuery(
            "DELETE FROM table WHERE id NOT IN (${ids.joinToString(prefix = "'", postfix = "'", separator = "','")})"
        )
    )
}

    In this case the query uses cursor and closes it after deletion is done, still locking database but not on GC phase and on the dedicated thread.

    P.S. There’re more stable long term solutions possible but they needs to be implemented on the Room / SqlCipher sides.

    In the given state, SqlCipher can be refactored to not lock the database on GC phase. There’s an open issue for that: https://github.com/sqlcipher/android-database-sqlcipher/issues/537

    Room should probably fix the codegen and use the query builder and generate statement closure lines, there’s no open issues there, but I’ll double check this thought later and will raise this as an issue with them.

    For us this has seemingly solved the issue completely.

    Note that all DELETE / UPDATE queries using IN / NOT IN operators and probably some other operators that prevent Room from precompiling the query (due to runtime params) cause this. You can check the codegen to verify that generated code calls either cursor.close() or statement.release()