SQLiteException: cannot start a transaction within a transaction

[Nek-12]

SQLDelight Version

2.0.0-rc2

Application Operating System

Android

Describe the Bug

When running multiple queries in parallel, each in a separate transaction, an exception is thrown sometimes.

The code I'm running is

val table1Data = async { /* ...(1)... */ } 
val table2Data = async { /* ...(1)... */ } 
// (1) : 
withContext(Dispatchers.IO) { queries.transactionWithResult { queries.getAllUpdatedAfter().executeAsOne() } } // noEnclosing = false

Is there anything wrong with my setup? This issue appeared recently after migrating from alpha to rc. But I suppose I may have made breaking changes myself while I was migrating.

Stacktrace

Non-fatal Exception: android.database.sqlite.SQLiteException: cannot start a transaction within a transaction (code 1 SQLITE_ERROR)
       at android.database.sqlite.SQLiteConnection.nativeExecute(SQLiteConnection.java)
       at android.database.sqlite.SQLiteConnection.execute(SQLiteConnection.java:730)
       at android.database.sqlite.SQLiteSession.beginTransactionUnchecked(SQLiteSession.java:321)
       at android.database.sqlite.SQLiteSession.beginTransaction(SQLiteSession.java:300)
       at android.database.sqlite.SQLiteDatabase.beginTransaction(SQLiteDatabase.java:738)
       at android.database.sqlite.SQLiteDatabase.beginTransactionNonExclusive(SQLiteDatabase.java:672)
       at androidx.sqlite.db.framework.FrameworkSQLiteDatabase.beginTransactionNonExclusive(FrameworkSQLiteDatabase.kt:59)
       at app.cash.sqldelight.driver.android.AndroidSqliteDriver.newTransaction(AndroidSqliteDriver.java:114)
       at app.cash.sqldelight.TransacterImpl.transactionWithWrapper(TransacterImpl.java:360)
       at app.cash.sqldelight.TransacterImpl.transactionWithResult(TransacterImpl.java:356)
       at app.cash.sqldelight.Transacter$DefaultImpls.transactionWithResult$default(Transacter.java:83)
       at com.app.util.DatabaseUtilKt$suspendTransaction$2.invokeSuspend$$forInline(DatabaseUtil.kt:18)
       at com.app.util.dao.DaoImpl$getAllUpdatedAfter$$inlined$invoke$default$1.invokeSuspend(DatabaseUtil.kt:18)
       at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33)
       at kotlinx.coroutines.DispatchedTask.run(DispatchedTask.kt:108)
       at kotlinx.coroutines.internal.LimitedDispatcher$Worker.run(LimitedDispatcher.java:115)
       at kotlinx.coroutines.scheduling.TaskImpl.run(Tasks.kt:103)
       at kotlinx.coroutines.scheduling.CoroutineScheduler.runSafely(CoroutineScheduler.java:584)
       at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.executeTask(CoroutineScheduler.kt:793)
       at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.runWorker(CoroutineScheduler.kt:697)
       at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.run(CoroutineScheduler.kt:684)

[AlecKazakova]

I'm not sure how this would result in nested transactions, is the method you're calling this from itself in a transaction?

Although SQLDelight does some protection from inner transactions (with the noEnclosing stuff) so that also surprised me. Are you able to make a repro project we can look in to?

[Nek-12]

Setting up a repro project would be a lot of work. If I have the free time, I can try to create one. No, the method itself is not in a transaction. This issue is reproduced in roughly 5% calls run

[Lasombras]

fun foo() = runBlocking {
        async(Dispatchers.IO) {
            queries.transactionWithResult {
                 //Nothing
            }
        }

        async(Dispatchers.IO) {
            queries.transactionWithResult {
                 //Nothing
            }
        }
}

Just open 2 transactions in async and the driver raise this error. It's like the driver try to run the transactions in a same thread and open a new transaction inside the first one.

Same with threads, just start 2 transactions in 2 threads, and the driver run the second transaction in the first one.

        thread {
            queries.transactionWithResult { }
        }

        thread {
            queries.transactionWithResult { }
        }
        Thread.sleep(500)

Exception in thread "Thread-6" org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (cannot start a transaction within a transaction)

In SQLDelight 2.0.0, to prevent this issue, I used a custom Dispatcher for my database request, limited to one parallelism.

        val databaseDispatcher = Dispatchers.IO.limitedParallelism(1)

        val table1Data = async(databaseDispatcher) { ... } 
        val table2Data = async(databaseDispatcher) { ... } 

But, of course, I break the parallelism of async in this temporary solution. And only one request can run at the same time. I will return easily to the Dispatcher.IO when the fix will be done.

[Nek-12]

True, I am also still seeing this happening often still. Thanks, didn't expect the repro to be that simple

[Nek-12]

@AlecKazakova may I request this issue to be triaged? There is no more info to add and this issue currently affects a very large portion of our users in production

This issue alone has brought our project below the GP bad behavior threshold already

[DanielNovak]

It could be also a platform bug, but maybe triggered by SqlDelight. We have seen a similar bug in Room too. Here is a link to the "platform bug": https://issuetracker.google.com/issues/37001653

[Nek-12]

But this was not reproducible on alpha01 for example and started happening after migrating to rc. How can this be possible if it's a platform bug?

UPD: I indeed started using DEFERRABLE INITIALLY DEFERRED in my code. I'll try removing that

[Nek-12]

@AlecKazakova Any updates on this? Still using singleThread dispatcher for all queries

[Nek-12]

@AlecKazakova Any updates?