Native in-memory driver is not thread safe

[silverhammermba]

SQLDelight Version

1.5.4

Application Operating System

Native

Describe the Bug

When using an in-memory native DB, I reliably get executeNonQuery error | error code SQLITE_LOCKED when multiple threads are inserting/selecting simultaneously.

This test reproduces the exception. I cannot get it to happen when using inMemory = false. I also get no exception when using JDBC's in-memory DB on JVM, so it seems like the issue is specific to the native in-memory mode.

    @Test
    fun sharedDbAndDriver() = runTest {
        val name = "sharedDbAndDriver"
        val schema = Database.Schema
        val driver = NativeSqliteDriver(
            configuration = DatabaseConfiguration(
                name = name,
                version = schema.version,
                create = { connection ->
                    wrapConnection(connection) { schema.create(it) }
                },
                upgrade = { connection, oldVersion, newVersion ->
                    wrapConnection(connection) { schema.migrate(it, oldVersion, newVersion) }
                },
                // when testing with real DB, clean between tests:
                // rm ~/Library/Developer/CoreSimulator/Devices/*/data/Library/Application\ Support/databases/*
                inMemory = true
            )
        )
        val db = Database(driver)

        val scale = 100

        val inserts = (1..scale).map { num ->
            launch(Dispatchers.Default) {
                delay((num % 3).toLong())
                db.playerQueries.insert(num.toLong(), "Player $num")
            }
        }

        val selects = (1..scale).map { num ->
            async(Dispatchers.Default) {
                delay((num % 3).toLong() + 1)
                db.playerQueries.selectAll().executeAsList().map { it.full_name }
            }
        }

        // when inMemory = true, fails with executeNonQuery error | error code SQLITE_LOCKED
        inserts.joinAll()
        selects.awaitAll()
    }

It is unclear whether this is a SQLDelight or SQLiter bug since the inMemory stuff is implemented here. But maybe it's another limitation of the underlying driver. I have reported the bug against both projects.

Stacktrace

executeNonQuery error | error code SQLITE_LOCKED
co.touchlab.sqliter.interop.SQLiteExceptionErrorCode: executeNonQuery error
Invalid connection: com.apple.coresymbolicationd
    at 0   test.kexe                           0x100f7c239        kfun:kotlin.Throwable#<init>(kotlin.String?){} + 105 (/opt/buildAgent/work/5f69639f351c4725/kotlin/kotlin-native/runtime/src/main/kotlin/kotlin/Throwable.kt:25:37)
    at 1   test.kexe                           0x100f75167        kfun:kotlin.Exception#<init>(kotlin.String?){} + 103 (/opt/buildAgent/work/5f69639f351c4725/kotlin/kotlin-native/runtime/src/main/kotlin/kotlin/Exceptions.kt:23:44)
    at 2   test.kexe                           0x101195d83        kfun:co.touchlab.sqliter.interop.SQLiteException#<init>(kotlin.String;co.touchlab.sqliter.interop.SqliteDatabaseConfig){} + 115 (/Users/runner/work/SQLiter/SQLiter/sqliter-driver/src/nativeCommonMain/kotlin/co/touchlab/sqliter/interop/SQLiteException.kt:3:89)
    at 3   test.kexe                           0x101195ecf        kfun:co.touchlab.sqliter.interop.SQLiteExceptionErrorCode#<init>(kotlin.String;co.touchlab.sqliter.interop.SqliteDatabaseConfig;kotlin.Int){} + 271 (/Users/runner/work/SQLiter/SQLiter/sqliter-driver/src/nativeCommonMain/kotlin/co/touchlab/sqliter/interop/SQLiteException.kt:5:109)
    at 4   test.kexe                           0x101195a28        kfun:co.touchlab.sqliter.interop.ActualSqliteStatement#executeNonQuery(){}kotlin.Int + 1976 (/Users/runner/work/SQLiter/SQLiter/sqliter-driver/src/nativeCommonMain/kotlin/co/touchlab/sqliter/interop/ActualSqliteStatement.kt:137:44)
    at 5   test.kexe                           0x1011924eb        kfun:co.touchlab.sqliter.interop.ActualSqliteStatement#execute(){} + 59 (/Users/runner/work/SQLiter/SQLiter/sqliter-driver/src/nativeCommonMain/kotlin/co/touchlab/sqliter/interop/ActualSqliteStatement.kt:87:9)
    at 6   test.kexe                           0x1011ac881        kfun:co.touchlab.sqliter.native.NativeStatement#execute(){} + 657 (/Users/runner/work/SQLiter/SQLiter/sqliter-driver/src/nativeCommonMain/kotlin/co/touchlab/sqliter/native/NativeStatement.kt:33:29)
    at 7   test.kexe                           0x101189a49        kfun:co.touchlab.sqliter.concurrency.ConcurrentDatabaseConnection.ConcurrentStatement#execute(){} + 569 (/Users/runner/work/SQLiter/SQLiter/sqliter-driver/src/nativeCommonMain/kotlin/co/touchlab/sqliter/concurrency/ConcurrentDatabaseConnection.kt:<unknown>)
    at 8   test.kexe                           0x1011b6c10        kfun:com.squareup.sqldelight.drivers.native.ConnectionWrapper.execute$lambda$0#internal + 544 (/Users/runner/work/sqldelight/sqldelight/drivers/native-driver/src/nativeMain/kotlin/com/squareup/sqldelight/drivers/native/NativeSqlDatabase.kt:44:17)
    at 9   test.kexe                           0x1011b77cb        kfun:com.squareup.sqldelight.drivers.native.ConnectionWrapper.$execute$lambda$0$FUNCTION_REFERENCE$4.invoke#internal + 107 (/Users/runner/work/sqldelight/sqldelight/drivers/native-driver/src/nativeMain/kotlin/com/squareup/sqldelight/drivers/native/NativeSqlDatabase.kt:32:5)
    at 10  test.kexe                           0x1011b796d        kfun:com.squareup.sqldelight.drivers.native.ConnectionWrapper.$execute$lambda$0$FUNCTION_REFERENCE$4.$<bridge-UNNN>invoke(-1:0){}#internal + 93 (/Users/runner/work/sqldelight/sqldelight/drivers/native-driver/src/nativeMain/kotlin/com/squareup/sqldelight/drivers/native/NativeSqlDatabase.kt:32:5)
    at 11  test.kexe                           0x1011bead9        kfun:com.squareup.sqldelight.drivers.native.Pool#access(kotlin.Function1<1:0,0:0>){0§<kotlin.Any?>}0:0 + 569 (/Users/runner/work/sqldelight/sqldelight/drivers/native-driver/src/nativeMain/kotlin/com/squareup/sqldelight/drivers/native/Pool.kt:66:7)
    at 12  test.kexe                           0x1011b9685        kfun:com.squareup.sqldelight.drivers.native.NativeSqliteDriver#accessConnection(kotlin.Boolean;kotlin.Function1<com.squareup.sqldelight.drivers.native.ThreadConnection,0:0>){0§<kotlin.Any?>}0:0 + 1413 (/Users/runner/work/sqldelight/sqldelight/drivers/native-driver/src/nativeMain/kotlin/com/squareup/sqldelight/drivers/native/NativeSqlDatabase.kt:216:25)
    at 13  test.kexe                           0x1011b67c9        kfun:com.squareup.sqldelight.drivers.native.ConnectionWrapper#execute(kotlin.Int?;kotlin.String;kotlin.Int;kotlin.Function1<com.squareup.sqldelight.db.SqlPreparedStatement,kotlin.Unit>?){} + 441 (/Users/runner/work/sqldelight/sqldelight/drivers/native-driver/src/nativeMain/kotlin/com/squareup/sqldelight/drivers/native/NativeSqlDatabase.kt:32:5)
    at 14  test.kexe                           0x100357cc8        kfun:com.blah.PlayerQueriesImpl.insert#internal + 424

[kpgalligan]

Reviewing. It's not "not thread safe" so much as the in-memory modes of sqlite are somewhat different than the disk modes, and the other drivers probably do more management of connections and threading directly (I am intimately familiar with the android driver, and it does do that).

Also, there have been updates to the sqliter in-memory management and I'll need to correlate sqldelight version with sqliter version to see if those are in there.

However, those updates are just for no-name in-memory driver. Supplying a name allows multiple connections and shared memory "disk", but that has different rules than no-name in-memory, and both act differently in certain cases than a disk connection.

On "not thread safe", that generally implies a data corruption issue, which this is not. It's a resource contention issue. It's an issue, but just want to be clear, it's not a data corruption problem.