iOS: Creating scheme throws SQLiteExceptionErrorCode: transaction within a transaction

[tamimattafi]

Problem

After updating to 0.1.0-alpha06, iOS apps started failing during scheme creation:

SQLiteExceptionErrorCode: Sqlite operation failure cannot start a transaction within a transaction

Stack Trace:

co.touchlab.sqliter.interop.SQLiteExceptionErrorCode: Sqlite operation failure cannot start a transaction within a transaction
    at 0   ComposeApp                          0x108a31cef        kfun:kotlin.Throwable#<init>(kotlin.String?){} + 119 
    at 1   ComposeApp                          0x108a2b05f        kfun:kotlin.Exception#<init>(kotlin.String?){} + 115 
    at 2   ComposeApp                          0x1089e933b        kfun:co.touchlab.sqliter.interop.SQLiteException#<init>(kotlin.String;co.touchlab.sqliter.interop.SqliteDatabaseConfig){} + 115 
    at 3   ComposeApp                          0x1089e942f        kfun:co.touchlab.sqliter.interop.SQLiteExceptionErrorCode#<init>(kotlin.String;co.touchlab.sqliter.interop.SqliteDatabaseConfig;kotlin.Int){} + 199 
    at 4   ComposeApp                          0x1089e54a3        kfun:co.touchlab.sqliter.interop.ActualSqliteStatement#resetStatement(){} + 1127 
    at 5   ComposeApp                          0x1089fecd3        kfun:co.touchlab.sqliter.interop.SqliteStatement#resetStatement(){}-trampoline + 91 
    at 6   ComposeApp                          0x1089fb333        kfun:co.touchlab.sqliter.native.NativeStatement#resetStatement(){} + 531 
    at 7   ComposeApp                          0x1089fa623        kfun:co.touchlab.sqliter.native.NativeStatement#execute(){} + 679 
    at 8   ComposeApp                          0x1089fd38b        kfun:co.touchlab.sqliter.Statement#execute(){}-trampoline + 91 
    at 9   ComposeApp                          0x1089f838f        kfun:co.touchlab.sqliter.native.NativeDatabaseConnection.beginTransaction$lambda$0#internal + 67 
    at 10  ComposeApp                          0x1089f876b        kfun:co.touchlab.sqliter.native.NativeDatabaseConnection.$beginTransaction$lambda$0$FUNCTION_REFERENCE$0.invoke#internal + 79 
    at 11  ComposeApp                          0x1089f87e3        kfun:co.touchlab.sqliter.native.NativeDatabaseConnection.$beginTransaction$lambda$0$FUNCTION_REFERENCE$0.$<bridge-UNNN>invoke(co.touchlab.sqliter.Statement){}#internal + 99 
    at 12  ComposeApp                          0x108b7ce8f        kfun:kotlin.Function1#invoke(1:0){}1:1-trampoline + 107 
    at 13  ComposeApp                          0x1089dc3a3        kfun:co.touchlab.sqliter#withStatement__at__co.touchlab.sqliter.DatabaseConnection(kotlin.String;kotlin.Function1<co.touchlab.sqliter.Statement,0:0>){0§<kotlin.Any?>}0:0 + 279 
    at 14  ComposeApp                          0x1089f76bb        kfun:co.touchlab.sqliter.native.NativeDatabaseConnection#beginTransaction(){} + 307 
    at 15  ComposeApp                          0x1089fce7b        kfun:co.touchlab.sqliter.DatabaseConnection#beginTransaction(){}-trampoline + 91 
    at 16  ComposeApp                          0x108a074cf        kfun:app.cash.sqldelight.driver.native.ThreadConnection#newTransaction(){}app.cash.sqldelight.Transacter.Transaction + 275 
    at 17  ComposeApp                          0x108a0691f        kfun:app.cash.sqldelight.driver.native.SqliterWrappedConnection#newTransaction(){}app.cash.sqldelight.db.QueryResult<app.cash.sqldelight.Transacter.Transaction> + 159 
    at 18  ComposeApp                          0x10a534977        kfun:app.cash.sqldelight.db.SqlDriver#newTransaction(){}app.cash.sqldelight.db.QueryResult<app.cash.sqldelight.Transacter.Transaction>-trampoline + 99 
    at 19  ComposeApp                          0x10a53150f        kfun:app.cash.sqldelight.SuspendingTransacterImpl.$transactionWithWrapperCOROUTINE$0.invokeSuspend#internal + 767 
    at 20  ComposeApp                          0x10a531f23        kfun:app.cash.sqldelight.SuspendingTransacterImpl.transactionWithWrapper#internal + 323 
    at 21  ComposeApp                          0x10a5310ff        kfun:app.cash.sqldelight.SuspendingTransacterImpl#transaction#suspend(kotlin.Boolean;kotlin.coroutines.SuspendFunction1<app.cash.sqldelight.
    SuspendingTransactionWithoutReturn,kotlin.Unit>;kotlin.coroutines.Continuation<kotlin.Unit>){}kotlin.Any + 151 
    at 22  ComposeApp                          0x10a534703        kfun:app.cash.sqldelight.SuspendingTransacter#transaction#suspend(kotlin.Boolean;kotlin.coroutines.SuspendFunction1<app.cash.sqldelight.SuspendingTransactionWithoutReturn,kotlin.Unit>;kotlin.coroutines.Continuation<kotlin.Unit>){}kotlin.Any-trampoline + 127 
    at 23  ComposeApp                          0x10a52eb8f        kfun:app.cash.sqldelight.SuspendingTransacter#transaction$default#suspend(kotlin.Boolean;kotlin.coroutines.SuspendFunction1<app.cash.sqldelight.SuspendingTransactionWithoutReturn,kotlin.Unit>;kotlin.Int;kotlin.coroutines.Continuation<kotlin.Unit>){}kotlin.Any + 211 
    at 24  ComposeApp                          0x10a54246b        kfun:com.attafitamim.kabin.core.database.KabinSqlSchema.$create$lambda$2COROUTINE$3.invokeSuspend#internal + 1319 
    at 25  ComposeApp                          0x10a542787        kfun:com.attafitamim.kabin.core.database.KabinSqlSchema.create$lambda$2#internal + 331 
    at 26  ComposeApp                          0x10a543afb        kfun:com.attafitamim.kabin.core.database.KabinSqlSchema.$create$lambda$2$FUNCTION_REFERENCE$1.invoke#internal + 115 
    at 27  ComposeApp                          0x108b826eb        kfun:kotlin.coroutines.SuspendFunction0#invoke#suspend(kotlin.coroutines.Continuation<1:0>){}kotlin.Any?-trampoline + 107 
    at 28  ComposeApp                          0x10a532b77        kfun:app.cash.sqldelight.db.QueryResult.AsyncValue#await#suspend(kotlin.coroutines.Continuation<1:0>){}kotlin.Any? + 175 
    at 29  ComposeApp                          0x1089ced57        kfun:app.cash.sqldelight.async.coroutines.object-1.create$lambda$0#internal + 243 
    at 30  ComposeApp                          0x1089cf0b7        kfun:app.cash.sqldelight.async.coroutines.object-1.$create$lambda$0$FUNCTION_REFERENCE$0.invoke#internal + 139 
    at 31  ComposeApp                          0x108b8242b        kfun:kotlin.Function2#invoke(1:0;1:1){}1:2-trampoline + 115 
    at 32  ComposeApp                          0x108a3a3b7        kfun:kotlin.coroutines.intrinsics.object-4.invokeSuspend#internal + 731 
    at 33  ComposeApp                          0x108b81d47     

It seems like scheme's create method is already wrapped inside a transaction by sqldelight, and kabin tries to wrap it again which leads to the exception above. Further investigation should include the migrate method.

[tamimattafi]

By studying sqldelight code, there are no signs of an explicit transaction during create or migrate. It seems like the SQLite helper on the iOS side uses transactions for these operations under the hood, while the android side doesn't throw such an exception.

UPD: NativeSqlDatabase uses wrapConnection on create and upgrade unlike other drivers, which seems like creating a transaction for such matter

[tamimattafi]

create and migrate methods are safe by design (Android runs them inside a transaction too), therefore, there is no need to have additional safety measures other than PRAGMA calls. However, a transaction is still used for clear method.

Changed here: 38ddd17