Should this open with SQLITE_OPEN_FULLMUTEX?

[TheKeeperOfPie]

It looks like Android's framework SQLite is thread safe (this defaults to true) using the serialized mode, which corresponds to SQLITE_OPEN_FULLMUTEX.

I have a 2 tab UI which runs SQLDelight's OffsetQueryPagingSource in parallel for 2 paged lists, and a direct swap from the SQLDelight AndroidSqliteDriver to this library's causes a crash on this screen.

Let me know if you need more specifics. Even if this isn't made the default mode, I think the README should clarify that it doesn't support any multi-threading by default and that it differs from the framework.

[TheKeeperOfPie]

Actually I may have spoke too soon, this doesn't seem to solve my problem. Even with this flag passed, I'm getting overlapping transactions, causing

14:49:36.585  D  prepare on 2511 BEGIN IMMEDIATE;
14:49:36.585  D  prepare on 2506 BEGIN IMMEDIATE;
14:49:36.587  D  prepare on 2511 SELECT COUNT(*) FROM seriesEntry WHERE has2025 = 1;
14:49:36.588  D  prepare on 2511 SELECT * FROM seriesEntry WHERE has2025 = 1 ORDER BY name COLLATE NOCASE LIMIT 75 OFFSET 0;
14:49:36.590  D  prepare on 2511 COMMIT;
14:49:36.592  E  FATAL EXCEPTION: main
                 Process: com.thekeeperofpie.artistalley2025, PID: 17265
                 android.database.SQLException: Error code: 1, message: cannot start a transaction within a transaction
                 	at androidx.sqlite.driver.bundled.BundledSQLiteStatementKt.nativeStep(Native Method)
                 	at androidx.sqlite.driver.bundled.BundledSQLiteStatementKt.access$nativeStep(Unknown Source:0)
                 	at kotlin.math.MathKt.execSQL(Unknown Source:14)
                 	at com.eygraber.sqldelight.androidx.driver.AndroidxSqliteDriver.newTransaction(Unknown Source:32)
                 	at app.cash.sqldelight.SuspendingTransacterImpl.transactionWithWrapper(Unknown Source:146)
                 	at com.thekeeperofpie.artistalleydatabase.alley.database.OffsetQueryPagingSource$load$2.invokeSuspend(Unknown Source:141)
                 	at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(Unknown Source:8)
                 	at kotlinx.coroutines.DispatchedTask.run(Unknown Source:114)
                 	at kotlinx.coroutines.internal.LimitedDispatcher$Worker.run(Unknown Source:3)
                 	at kotlinx.coroutines.scheduling.TaskImpl.run(Unknown Source:2)
                 	at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.run(Unknown Source:93)
                 	Suppressed: kotlinx.coroutines.internal.DiagnosticCoroutineContextException: [LazyStandaloneCoroutine{Cancelling}@6550187, Dispatchers.Main.immediate]

Using the framework/AndroidSqliteDriver, I don't get this, although it might be this is entirely a timing coincidence:

13:50:05.593  V  /data/user/0/com.thekeeperofpie.artistalley2025.debug/databases/alleyUser: "BEGIN IMMEDIATE;"
13:50:05.598  V  /data/user/0/com.thekeeperofpie.artistalley2025.debug/databases/alleyUser: "SELECT COUNT(*) FROM seriesEntry WHERE has2025 = 1"
13:50:05.601  V  /data/user/0/com.thekeeperofpie.artistalley2025.debug/databases/alleyUser: "SELECT * FROM seriesEntry WHERE has2025 = 1 ORDER BY name COLLATE NOCASE LIMIT 75 OFFSET 0"
13:50:05.610  V  /data/user/0/com.thekeeperofpie.artistalley2025.debug/databases/alleyUser: "COMMIT;"
13:50:05.628  V  /data/user/0/com.thekeeperofpie.artistalley2025.debug/databases/alleyUser: "BEGIN IMMEDIATE;"
13:50:05.628  V  /data/user/0/com.thekeeperofpie.artistalley2025.debug/databases/alleyUser: "SELECT COUNT(*) FROM merchEntry WHERE has2025 = 1"
13:50:05.629  V  /data/user/0/com.thekeeperofpie.artistalley2025.debug/databases/alleyUser: "SELECT * FROM merchEntry WHERE has2025 = 1 ORDER BY name COLLATE NOCASE LIMIT 75 OFFSET 0"
13:50:05.630  V  /data/user/0/com.thekeeperofpie.artistalley2025.debug/databases/alleyUser: "COMMIT;"

[eygraber]

Looking into it.

[eygraber]

I have a fix for this coming shortly. I'll also add something about making sure to use SQLITE_OPEN_FULLMUTEX for native and bundled if you use multithreading.

[Nek-12]

Actually this seems to be from sqldelight

sqldelight/sqldelight#4361

[eygraber]

That's interesting because @TheKeeperOfPie said this wasn't a problem with AndroidSqliteDriver, and before I implemented the locking solution I tested my library with AndroidSQLiteDriver instead of BundledSQLiteDriver and it worked.

I'll add a comment on that issue for more visibility.

[Nek-12]

I am suspicious of some of the code in #26 as it looks like it skips caching of statements and uses a mutex where that may not be necessary. Indeed sqldelight needs manual setup to use only one thread for write operations it seems, but we are running an experiment introducing this driver only because we WANTED parallel access to the database according to https://blog.p-y.wtf/parallelism-with-android-sqlite

Are you sure about this?

[Nek-12]

The big question of this issue is - why is there a transaction for read operations? Transactions should be used for write operations only!

[eygraber]

Skipping statement caching is only when running the schema create or migrate since it is unlikely that those statements would need to be cached.

The mutex is the only way I can get it to work, and I am writing up why I think that is the case in sqldelight/sqldelight#4361

[TheKeeperOfPie]

Oh, interesting. I didn't look into whether it was a SQLDelight problem because the framework driver seemed to work, but I didn't verify that extensively.

The paging should still work by removing the read transaction. OffsetQueryPagingSource executes twice here and here, but it can probably be done without the transaction. Subsequent pages will not be in the transaction, so data consistency isn't guaranteed this way anyways.

I actually have OffsetQueryPagingSource copied out to work with wasmJs, so I can make that change locally if you end up reverting the mutex change made for this.

[eygraber]

You can avoid the mutex for now by not using explicit transactions (i.e. for reads), or by setting isAccessMultithreaded = false (i.e. if you only have single threaded access).

Eventually I will remove the mutex, and let the user know that they are responsible for making sure they follow PY's take-aways (perhaps the library can be modified to make that easier, although I'm not sure how).

Any other ideas?

[Nek-12]

If we open the database in fullmutex mode this basically disables WAL and any performance gains, throwing us back into 2014 and Android 5. If I understood the docs correctly, that's what the FULLMUTEX does.

The entire point of us using the bundled driver was to be able to use more than one thread.

[eygraber]

That's only short term. I have some ideas to make this better:

1. Make the driver async so that concurrency can be managed by dispatchers (i.e. a single thread dispatcher for transactions and writes, and a pool dispatcher for reads)
2. Create a connection per thread so that NOMUTEX could be used

I'm pretty sure 2 would work, still not so sure about 1.