IssueTracker

Is there any updates? This is a big problem!

Hi there - could you provide more context on the issue & a sample project to reproduce? Database locked exceptions are quite difficult to pinpoint without a repro project. Thanks!

Hi. It is reproduceable on some users by using this code:

suspend fun <R> MyDatabase.workaroundWithTransaction(block: suspend TransactionScope<R>.() -> R) {
    useWriterConnection {
        it.immediateTransaction(block)
    }

    // TODO: Temporally fix https://issuetracker.google.com/issues/340606803#comment2
    // Manually triggers invalidation
    invalidationTracker.refreshAsync()
}

Maybe it is about this? https://android-review.googlesource.com/c/platform/frameworks/support/+/3114928

I am not sure.

Hi. Please merge the pull request. This is big problem!

Hi. Is there any updates on this problem?

Hi, I updated my version to alpha12 but still have these crashes. Please release a fix asap.

Will there be fixes released this Wednesday?

Hey, there is no release on Wednesday (holiday in the USA)

We haven't found the root cause of this exception, it happens when SQLite can't commit the transaction because some other connection has a lock, we need more information, a sample project that reproduces the issue preferably, or more details on how the database is being used (is it from multiple process? is there heavy concurrency?).

You can also increase the time SQLite will wait before it error with 'database locked' by executing PRAGMA busy_timeout = <time in ms>, the current default is 3000

Thank you for you response.

1. Isn't these pull request addressed this issue? 1.1 https://android-review.googlesource.com/c/platform/frameworks/support/+/3408679 1.2 https://android-review.googlesource.com/c/platform/frameworks/support/+/3114928
2. According to RoomDB documentation about withTransaction: "Room will only perform at most one transaction at a time, additional transactions are queued and executed on a first come, first serve order." So the transaction should just wait for other transaction to complete and not throw exception instead. Isn't it?

I tried to use this code and still getting the same error from users. (deferredTransaction instead of immediateTransaction)

private val dbMutex = Mutex()

suspend fun <R> MyDatabse.workaroundWithTransaction(block: suspend TransactionScope<R>.() -> R) {
    dbMutex.withLock {
        useWriterConnection {
            it.deferredTransaction(block)
        }

        // TODO: Fix https://issuetracker.google.com/issues/340606803#comment2
        // Manually triggers invalidation
        invalidationTracker.refreshAsync()
    }
}

Moreover now I am also think that @Update function caused particularly this crash. I will monitor the situation to this if this 1 crash or it will be more with the same reason.

After checking I can confirm that this crash happens as many as before.

The crash occurs when the user adds a new record to the database from the interface, and the crash occurs when the application starts in the first 1-2 seconds. The crash also occurs when receiving records from the database.

This is just a disaster, please fix it.

Sorry, I don't believe those two pull request address the issue you are seeing.

If you can reliably reproduce can you please send us a sample project?

Besides adding a Mutex did you also try increasing the busy timeout with the PRAGMA? Can you please share more details how the database is being used? Are you starting a transaction and performing many @Update? Is the coroutine where the transaction occurring getting canceled and restarted? The theory here is maybe cancelation is not recycling the connection correctly and there is a lingering transaction that prevents other ones from finishing.

Can you also try and configure Room with the AndroidSQLiteDriver()? This would help us know if the issue is specific to the BundledSQLiteDriver or not.

I have yet to reproduce to issue, I've tried creating 'stress tests' like the following:

@Test
fun daoUsagesInTransaction() = runTest {
    List(100) {
        launch(Dispatchers.IO) {
            db.useWriterConnection { transactor ->
                transactor.immediateTransaction {
                    val e = SampleEntity(1)
                    db.dao().insert(e)
                    db.dao().update(e)
                    db.dao().delete(e)
                }
            }
            db.invalidationTracker.refreshAsync()
        }
    }.joinAll()
}

Hi. Thank you for your response.

1. My app have BroadcastReceivers and WorkManagers that reads and writes in the same database that the app does. Could it be the problem? But! The logic didn't change since I moved from android only to shared module. On android it was working just fine.
2. Currently I didn't succeed in reproducing it by myself.
3. Have I tried to reproduce it by stress tests? Yes, but I haven't get the crash.
4. "did you also try increasing the busy timeout with the PRAGMA?" - No, I didn't. I thought that it may be command just for my local device for testing. I google it see that I could execute it in query format when setting up Room. But I don't have so match long data read of write. I and thinking about it now.
5. "Can you please share more details how the database is being used?" - As example of my app: task tracking app. The user enters tasks via UI, tasks sync with other user device, task can show local alerts notification to remind.
6. "Are you starting a transaction and performing many @Update?" - It could do non-users operations when:

• Starts the app the notes are being sent or received (the App scope).
• Starts the app the other data updated via WorkManager (the WorkManager scope).
• Receives alert notification and BroadcastReceivers plans new notification and updates info about it in database (BroadcastReceiver scope).

7. "The theory here is maybe cancelation is not recycling the connection correctly and there is a lingering transaction that prevents other ones from finishing." - I am now trying to ger rid of WorkManagers because I heard that they may start in another process then the app. I will see if it would help.
8. "Can you also try and configure Room with the AndroidSQLiteDriver()?" - I tried to implement it but I don't understand how. My database placed in common module. The RoomDB generates implementation for each platform. How could I make my android implementation so I could use AndroidSQLiteDriver()?

1 & 6 - WorkManager workers and broadcast receiver interacting with the same Room database is fine. I was more curious on the operations being done, but it does seem to be write operations in transactions.

4 - You can configure the timeout in Room's onOpen callback, and these can be installed in the builder via addCallback(). In the onOpen just do: conenction.execSQL("PRAGMA busy_timeout = <time in ms>")

7 - You should check your AndroidManifest.xml, WorkManager could run in another process, but only if configured to do so, check if your app has multiple processes by checking the manifest and looking for android:process=

8 - Because AndroidSQLiteDriver is an Android only API you need to create an actual / expect function that return a SQLiteDriver along with the necessary Kotlin multiplatform source set configuration so that for Android it'll return AndroidSQLiteDriver while for other platforms I'll return BundledSQLiteDriver and you pipe that to `setDriver().

Hi. I have updated my app replaced BundledSQLiteDriver with AndroidSQLiteDriver as you suggested.

For now I don't see SQLException: Error code: 5, message: database is locked anymore but I see the new error java.lang.IllegalStateException: Cannot perform this operation because there is no current transaction.

Here is the stack trace.

Fatal Exception: java.lang.IllegalStateException: Cannot perform this operation because there is no current transaction.
       at android.database.sqlite.SQLiteSession.throwIfNoTransaction(SQLiteSession.java:917)
       at android.database.sqlite.SQLiteSession.endTransaction(SQLiteSession.java:400)
       at android.database.sqlite.SQLiteDatabase.endTransaction(SQLiteDatabase.java:588)
       at androidx.room.coroutines.AndroidSQLiteDriverPooledConnection.transaction(AndroidSQLiteDriverConnectionPool.android.kt:94)
       at androidx.room.coroutines.AndroidSQLiteDriverPooledConnection.access$transaction(AndroidSQLiteDriverPooledConnection.java:53)
       at androidx.room.coroutines.AndroidSQLiteDriverPooledConnection$transaction$1.invokeSuspend(AndroidSQLiteDriverConnectionPool.android.kt:12)
       at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33)
       at kotlinx.coroutines.DispatchedTask.run(DispatchedTask.kt:98)
       at kotlinx.coroutines.internal.LimitedDispatcher$Worker.run(LimitedDispatcher.java:113)
       at kotlinx.coroutines.scheduling.TaskImpl.run(Tasks.kt:89)
       at kotlinx.coroutines.scheduling.CoroutineScheduler.runSafely(CoroutineScheduler.java:586)
       at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.executeTask(CoroutineScheduler.kt:820)
       at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.runWorker(CoroutineScheduler.kt:717)
       at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.run(CoroutineScheduler.kt:704)
        

Hi. Happy New Year!

After week with AndroidSQLiteDriver I see no SQLException: Error code: 5, message: database is locked but see java.lang.IllegalStateException: Cannot perform this operation because there is no current transaction..

So I think your theory may be right that this is something with "Coroutines and RoomDB".