Sqldelight: SQLException: database in auto-commit mode

[melnikovdv]

Hi. I use SqlDelight on JVM. A small http backend with a single database connection. When I do almost simultaneous queries I get a weird error.

With 1.3.0 and sqlite jdbc 3.27.2.1 it was: [SQLITE_ERROR] SQL error or missing database (cannot start a transaction within a transaction).

I decided to update SqlDelight and now with 1.4.0 and 3.32.3 (latest releases) I have java.sql.SQLException: database in auto-commit mode. I'm not sure, but seems I'm getting an issue somehow connected with 1706.

It't not 100% reproducible, 1 time of 3-5 queries.

My guess according to the code that after the first transaction JdbcDriver.kt changes autoCommit value to true and then second query can't finish it normally.

To be more precise:

• t1 enclosing transaction started
• t2 enclosing transaction started
• t1 commited and JdbcDriver.kt set connection.autoCommit = true in endTransaction method
• t2 commited and SQLiteConnection.java from sqlite-jdbc throws exception as it's in autoCommit state:

    @Override
    public void commit() throws SQLException {
        checkOpen();
        if (connectionConfig.isAutoCommit())
            throw new SQLException("database in auto-commit mode");
        db.exec("commit;", getAutoCommit());
        db.exec(connectionConfig.transactionPrefix(), getAutoCommit());
    }

Am I wrong that don't synchronize my queries? I thought database will handle it.

That's my code

var response: Response? = null
dbHelper.database.transaction {
    response = findSchedulesInternal(userId, uuid, scheduleId) // several select queries inside
}

That's what I get:

java.sql.SQLException: database in auto-commit mode
    at org.sqlite.SQLiteConnection.commit(SQLiteConnection.java:403)
    at com.squareup.sqldelight.sqlite.driver.JdbcDriver$Transaction.endTransaction(JdbcDriver.kt:96)
    at com.squareup.sqldelight.Transacter$Transaction.endTransaction$runtime(Transacter.kt:113)
    at com.squareup.sqldelight.TransacterImpl.transactionWithWrapper(Transacter.kt:223)
    at com.squareup.sqldelight.TransacterImpl.transaction(Transacter.kt:197)
    at com.squareup.sqldelight.Transacter$DefaultImpls.transaction$default(Transacter.kt:82)
    at com.eventsheep.sdk.event.service.dashboard.scheduleItem.FindScheduleItemService.findByScheduleId-NF5XIXo(FindScheduleItemService.kt:33)
    …

[AlecStrong]

it should leave autocommit open after the first transaction ends based on this code:

if (enclosingTransaction == null) {
        if (successful) {
          connection.commit()
        } else {
          connection.rollback()
        }
        connection.autoCommit = true
        closeConnection(connection)
      }

are you able to create a simple repro using your two transaction setup? you mentioned it doesn't happen every time, is there a way to tell from the stacktrace at what point in the transaction its crashing?

ie this crash line:

    at com.eventsheep.sdk.event.service.dashboard.scheduleItem.FindScheduleItemService.findByScheduleId-NF5XIXo(FindScheduleItemService.kt:33)

I wonder if that is happening in between the two transaction closes as you mentioned.

The only other thing I can think of is if your setup is multithreaded, do you have multiple connections open to the database with running queries?

[melnikovdv]

Thank you for your feedback.

My setup is several threads accessing singe Sqlite connection. So shouldn't be a multithreaded issues.

I'll try to make reproduction project in a couple of days with simple setup. If I not succeed I'll close this issue.

[melnikovdv]

Hi, I made a sample that shows exception I mentioned.

It's the same with my setup but hugely simplified:

• JVM app (console app for the sample)
• WAL journal mode
• single db connection
• several threads accessing the db connection

Sample inserts 100 records to each of three tables: tableA, tableB, tableC. Then I select all records from each table twice with two threads.

If I select records without database.transaction { … } it works.
If I select records with database.transaction { … } it fails.

[AlecStrong]

thank you! I will take a look

[melnikovdv]

Hi. You marked this issue as closed, but I updated my sample to 1.4.4 and it still fails. Now with cannot start a transaction within a transaction.

I checked your integration test, it seems to work, but still I'm getting an error. Could you try to run my sample with 1.4.4 and check?

[melnikovdv]

Hi, @AlecStrong.

According to this issue it seems nobody uses SqlDelight within JVM except me) I'd like to use it in the production, but this bug ruins everything. I tried to look into the code, but it's not so obvious without some expertise.

Could you look into the issue again?

[Thomas-Vos]

I am getting the same error in my project. Stack trace:

Exception in thread "DefaultDispatcher-worker-23" org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (cannot start a transaction within a transaction)
        at org.sqlite.core.DB.newSQLException(DB.java:909)
        at org.sqlite.core.DB.newSQLException(DB.java:921)
        at org.sqlite.core.DB.execute(DB.java:825)
        at org.sqlite.jdbc3.JDBC3PreparedStatement.execute(JDBC3PreparedStatement.java:53)
        at com.squareup.sqldelight.sqlite.driver.JdbcSqliteDriver.newTransaction(JdbcSqliteDriver.kt:46)
        at com.squareup.sqldelight.logs.LogSqliteDriver.newTransaction(LogSqliteDriver.kt:56)
        at com.squareup.sqldelight.TransacterImpl.transactionWithWrapper(Transacter.kt:208)
        at com.squareup.sqldelight.TransacterImpl.transaction(Transacter.kt:197)
        at com.squareup.sqldelight.Transacter$DefaultImpls.transaction$default(Transacter.kt:82)
        at com.superthomaslab.hueessentials.common.huesdk.bridge.lifx.Lifx.updateLightFromLan(Lifx.kt:194)
        at com.superthomaslab.hueessentials.common.huesdk.bridge.lifx.Lifx$updateLightFromLan$1.invokeSuspend(Lifx.kt)
        at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33)
        at kotlinx.coroutines.internal.ScopeCoroutine.afterResume(Scopes.kt:33)
        at kotlinx.coroutines.AbstractCoroutine.resumeWith(AbstractCoroutine.kt:113)
        at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:46)
        at kotlinx.coroutines.DispatchedTask.run(DispatchedTask.kt:106)
        at kotlinx.coroutines.scheduling.CoroutineScheduler.runSafely(CoroutineScheduler.kt:571)
        at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.executeTask(CoroutineScheduler.kt:750)
        at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.runWorker(CoroutineScheduler.kt:678)
        at kotlinx.coroutines.scheduling.CoroutineScheduler$Worker.run(CoroutineScheduler.kt:665)
Exception in thread "DefaultDispatcher-worker-12" org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (cannot start a transaction within a transaction)
        at org.sqlite.core.DB.newSQLException(DB.java:909)
        at org.sqlite.core.DB.newSQLException(DB.java:921)
        at org.sqlite.core.DB.execute(DB.java:825)
        at org.sqlite.jdbc3.JDBC3PreparedStatement.execute(JDBC3PreparedStatement.java:53)
        at com.squareup.sqldelight.sqlite.driver.JdbcSqliteDriver.newTransaction(JdbcSqliteDriver.kt:46)
        at com.squareup.sqldelight.logs.LogSqliteDriver.newTransaction(LogSqliteDriver.kt:56)
        at com.squareup.sqldelight.TransacterImpl.transactionWithWrapper(Transacter.kt:208)
        at com.squareup.sqldelight.TransacterImpl.transaction(Transacter.kt:197)
        at com.squareup.sqldelight.Transacter$DefaultImpls.transaction$default(Transacter.kt:82)
        at com.superthomaslab.hueessentials.common.huesdk.bridge.lifx.Lifx.updateLightFromLan(Lifx.kt:194)
        at com.superthomaslab.hueessentials.common.huesdk.bridge.lifx.Lifx$updateLightFromLan$1.invokeSuspend(Lifx.kt)
        at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33)
        at kotlinx.coroutines.internal.ScopeCoroutine.afterResume(Scopes.kt:33)
        at kotlinx.coroutines.AbstractCoroutine.resumeWith(AbstractCoroutine.kt:113)
        at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:46)
        at kotlinx.coroutines.DispatchedTask.run(DispatchedTask.kt:106)
        at kotlinx.coroutines.scheduling.CoroutineScheduler.runSafely(CoroutineScheduler.kt:571)