Skip to content

Failed transaction commit can return a connection with an open native transaction to the pool #101

Open

Description

@virtanAnton

Description

When an outer managed transaction's COMMIT fails, SQLCipher Android can release its connection to the pool while SQLite still has the native transaction open. A later operation using that connection can observe changes from the failed transaction even though the managed session reports that it is no longer in a transaction.

Reproduction

Using a fresh, file-backed SQLiteDatabase with foreign keys enabled:

db.setForeignKeyConstraintsEnabled(true);
db.execSQL("CREATE TABLE parent(id INTEGER PRIMARY KEY)");
db.execSQL("CREATE TABLE child(id INTEGER REFERENCES parent(id) " +
        "DEFERRABLE INITIALLY DEFERRED)");

db.beginTransaction();
db.execSQL("INSERT INTO child VALUES (1)");
db.setTransactionSuccessful();
try {
    db.endTransaction(); // COMMIT fails: parent 1 does not exist
    throw new AssertionError("Expected a constraint exception");
} catch (android.database.sqlite.SQLiteConstraintException expected) {
    // db.inTransaction() is false here.
}

try (android.database.Cursor cursor = db.rawQuery(
        "SELECT count(*) FROM child", (String[]) null)) {
    cursor.moveToFirst();
    int count = cursor.getInt(0); // observed 1; expected 0
}

The issue can also affect a subsequent operation on another thread when it acquires the same pooled connection. Observing the row on that connection does not establish that the failed commit was durable.

Expected behavior

After the commit failure, a connection returned for reuse should not still expose an unfinished transaction. The original failure should be reported, and the connection or pool should be made safe before reuse.

Environment and evidence

  • SQLCipher Android net.zetetic:sqlcipher-android:4.17.0.
  • Reproduced on Pixel 8 and Pixel 9 API 36 ARM64 emulators with a deferred foreign-key violation.
  • A regression test and proposed recovery are in Recover pooled connections after failed transaction completion #100. The PR also covers cancellation, automatically rolled-back transactions, in-memory and temporary databases, and pool waiters.

A failed COMMIT does not by itself determine whether changes became durable; applications may need to reconcile the transaction's outcome separately.

Activity

  1. developernotes commented on Nov 4, 2026

    @developernotes
    Member

    Hello @virtanAnton,

    Thanks for bringing this to our attention, along with the pull request. We will investigate this further and reach back out when we have more to share.

  2. npars commented on Nov 18, 2026

    @npars

    Fairly certain we encountered the same issue. Here's the AI summary:

    Summary

    If COMMIT fails, for example when a DEFERRABLE INITIALLY DEFERRED foreign key is violated,
    SQLite keeps the transaction open. SQLiteSession.endTransactionUnchecked() removes its own
    transaction record before it runs COMMIT, then calls releaseConnection() in a finally
    block and never rolls back. The connection goes back to the pool with the native transaction
    still open.

    Consequences:

    • SQLiteDatabase.inTransaction() returns false while the native transaction is open.
    • Reads on that connection see the rows the failed transaction tried to commit.
    • The next beginTransaction() on the connection fails with
      cannot start a transaction within a transaction (code 1). Every later write fails the same
      way until something issues a raw ROLLBACK.

    This happens with and without WAL, so it doesn't need a second connection or any concurrency.

    Environment

    Library net.zetetic:sqlcipher-android:4.17.0
    Also affected (by source inspection) 4.20.0 (latest release, equal to master HEAD) and 5.0.0-beta.1
    Device Android emulator, Pixel 9a AVD (sdk_gphone64_arm64), Android 16 / API 36
    Journal modes tested WAL and the default (rollback journal)

    Reproduction

    An instrumentation test that uses only net.zetetic.database.sqlcipher public APIs. It ran on
    the environment above.

    package repro
    
    import android.util.Log
    import androidx.test.platform.app.InstrumentationRegistry
    import net.zetetic.database.sqlcipher.SQLiteDatabase
    import org.junit.Test
    
    class SqlCipherFailedCommitRepro {
        @Test fun wal() = repro(true)
        @Test fun noWal() = repro(false)
    
        private fun repro(wal: Boolean) {
            System.loadLibrary("sqlcipher")
            val file = InstrumentationRegistry.getInstrumentation().targetContext
                .getDatabasePath("repro.db").also { it.parentFile?.mkdirs(); it.delete() }
            val db = SQLiteDatabase.openOrCreateDatabase(file, "", null, null)
            if (wal) db.enableWriteAheadLogging()
            db.setForeignKeyConstraintsEnabled(true)
            db.execSQL("CREATE TABLE parent(id INTEGER PRIMARY KEY)")
            db.execSQL("CREATE TABLE child(id INTEGER PRIMARY KEY, " +
                "parent_id INTEGER REFERENCES parent(id) DEFERRABLE INITIALLY DEFERRED)")
            fun count() = db.rawQuery("SELECT count(*) FROM child", null)
                .use { it.moveToFirst(); it.getInt(0) }
            fun log(s: String) = Log.i("Repro", "wal=$wal $s")
    
            db.beginTransaction()
            db.execSQL("INSERT INTO child VALUES (1, 999)") // parent 999 does not exist
            db.setTransactionSuccessful()
            try { db.endTransaction() } catch (e: Exception) { log("commit: $e") }
            log("inTransaction=${db.inTransaction()} count=${count()}")
            try { db.beginTransaction() } catch (e: Exception) { log("begin: $e") }
            db.rawExecSQL("ROLLBACK") // workaround
            db.beginTransaction(); db.setTransactionSuccessful(); db.endTransaction()
            log("after ROLLBACK: count=${count()}")
            db.close()
        }
    }

    Observed output (adb logcat -s Repro)

    wal=true  commit: android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787)
    wal=true  inTransaction=false count=1
    wal=true  begin: android.database.sqlite.SQLiteException: cannot start a transaction within a transaction (code 1)
    wal=true  after ROLLBACK: count=0
    wal=false commit: android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787)
    wal=false inTransaction=false count=1
    wal=false begin: android.database.sqlite.SQLiteException: cannot start a transaction within a transaction (code 1)
    wal=false after ROLLBACK: count=0
    

    Expected

    After endTransaction() throws, the connection should be clean: no native transaction open,
    count=0, and the next beginTransaction() should succeed. SQLiteDatabase users can't fix this
    themselves in a reliable way. The connection has already been returned to the pool by the time
    endTransaction() throws, so another thread can acquire it (and see uncommitted rows, or fail to
    BEGIN) before the caller can issue ROLLBACK.

    Root cause

    SQLiteSession.java#L409-L454 @ v4.17.0
    (the same code is at
    v4.20.0):

    mTransactionStack = top.mParent;   // L432: session forgets the transaction...
    recycleTransaction(top);
    ...
    } else {
        try {
            if (successful) {
                mConnection.execute("COMMIT;", null, cancellationSignal); // L442: ...then COMMIT may throw
            } else {
                mConnection.execute("ROLLBACK;", null, cancellationSignal);
            }
        } finally {
            releaseConnection(); // L447: connection returned with the native txn still open
        }
    }

    This logic comes from AOSP and is unchanged there
    (android-16.0.0_r1 SQLiteSession.java).
    So the framework android.database.sqlite most likely has the same bug. We only ran the repro
    against SQLCipher.

    SQLite documents that the transaction stays open

    • Foreign keys, §4.2 Deferred Foreign Key Constraints:
      "COMMIT will fail as long as foreign key constraints remain in violation." The example on that
      page also says: "The following COMMIT fails, as the database is in a state that does not satisfy
      the deferred foreign key constraint. The transaction remains open."
    • Transactions: "An attempt to execute COMMIT might
      also result in an SQLITE_BUSY return code if another thread or process has an open read
      connection. When COMMIT fails in this way, the transaction remains active…"
    • Response to errors within a transaction:
      "If the transaction has already been rolled back automatically by the error response, then the
      ROLLBACK command will fail with an error, but no harm is caused by this." So rolling back
      when you aren't sure the transaction is still open is safe.
    • sqlite3_get_autocommit(): returns 0 while
      a transaction is open. "The only way to find out whether SQLite automatically rolled back the
      transaction after an error is to use this function." This is the exact check for the state
      described above.

    Suggested fix

    In endTransactionUnchecked(), if COMMIT throws, issue ROLLBACK on the same connection,
    before releaseConnection()
    . Add any ROLLBACK failure as a suppressed exception and rethrow
    the original COMMIT exception:

    try {
        if (successful) {
            try {
                mConnection.execute("COMMIT;", null, cancellationSignal);
            } catch (RuntimeException commitFailure) {
                try {
                    mConnection.execute("ROLLBACK;", null, null);
                } catch (RuntimeException rollbackFailure) {
                    commitFailure.addSuppressed(rollbackFailure);
                }
                throw commitFailure;
            }
        } else {
            mConnection.execute("ROLLBACK;", null, cancellationSignal);
        }
    } finally {
        releaseConnection();
    }

    A second safety net would be for the pool to check sqlite3_get_autocommit() == 0 before
    recycling a connection, and either roll back or discard the connection. That appears to be the
    approach in PR #100. If the ROLLBACK itself fails, the connection should be closed rather than
    returned to the pool.

    Other libraries that already handle this

    Each link is pinned to a commit.

    Library What it does
    mattn/go-sqlite3: sqlite3.go#L751-L764 On a COMMIT error it calls ROLLBACK on the same connection before returning. Its comment says: "sqlite3 may leave the transaction open in this scenario. However, database/sql considers the transaction complete once we return from Commit() - we must clean up to honour its semantics… according to sqlite's docs, there is no harm in calling ROLLBACK unnecessarily." This is the closest match: a connection pool that treats the transaction as finished.
    androidx Room 3 (room3-runtime): ConnectionPoolImpl.kt#L461-L474 Before recycling a connection into the pool: "Perform a rollback in case there is an active transaction so that the connection is in a clean state when it is recycled." if (delegate.inTransaction()) delegate.executeSQL("ROLLBACK TRANSACTION"). This is Google's own Android connection pool.
    GRDB.swift: Database.swift#L1543-L1560, #L1746-L1798 If commit() throws, it calls rollback(), which checks sqlite3_get_autocommit() == 0 (isInsideTransaction) and runs ROLLBACK TRANSACTION. SerializedDatabase.swift#L297-L308 also fails hard if a transaction is still open when a database access ends.
    rusqlite: transaction.rs#L187-L248 If COMMIT fails, the dropped Transaction runs finish_(). That checks is_autocommit() (sqlite3_get_autocommit) and rolls back if the transaction is still open.
    better-sqlite3: transaction.js#L64-L77 COMMIT runs inside the try. The catch does if (db.inTransaction) undo.run() (ROLLBACK), then rethrows the original error.

    Impact

    • The pool's primary connection is poisoned: every later write fails until the app finds the
      problem and issues a raw ROLLBACK.
    • Readers that get the poisoned connection can see uncommitted (and never-to-be-committed) rows.
    • An app-level workaround (raw ROLLBACK after catching the failure from endTransaction())
      can't be made reliable. The connection is back in the pool before the app's catch block runs.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions

      ⌂ Proxy