search

sqldelight / sqldelight

SQLDelight - Generates typesafe Kotlin APIs from SQL
https://sqldelight.github.io/sqldelight/
Apache License 2.0
6.21k stars 516 forks source link

Nested queries hang on 2.0.0 #4478

Open MJegorovas opened 1 year ago

MJegorovas commented 1 year ago

SQLDelight Version

2.0.0

Application Operating System

Only tested native

Describe the Bug

On 1.x it was possible to do this:

database.xQueries.selectX() { x ->
    val y = database.xQueries.selectY().executeAsOne()
    ... // construct data class
 }.executeAsList()

After migrating to 2.0.0 functions like this freeze the app on selectY(). Is this intentional? I understand that I could execute these queries separately, but then unnecessary sqlDelight objects get created, instead of just mapping returned values to final object.

Stacktrace

No response

dellisd commented 1 year ago

I tried this on the JVM and it worked normally, and I was able to reproduce it with the native driver. I believe the native driver underwent some changes in 2.0 to better support the new memory model, and it looks like it's deadlocking.

Lasombras commented 1 year ago

Hi, I also noticed that In 2.0.0, the native driver seems limited to single active request. The mapper is executed before the close of the request. So in your case, the mapper start a new request inside an active one. So a deadlock appear... the driver wait the end of the first request.

You can bypass this limitation by run this select inside a transactionWithResult.

But yes, I also changed a lot of my code and I am very afraid that someone create by mistake a deadlock in my project with this behavior.

laenger commented 5 months ago

You can increase the size of the reader pool to avoid such deadlocks

NativeSqliteDriver(
    schema = Database.Schema,
    name = "app.db",
    maxReaderConnections = 4, // find a value > 1 that matches your needs
)