in memory DB is not thread-safe

[silverhammermba]

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

This test, based on the SQLDelight getting started guide 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 SQLiter's 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()
    }