You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Failed transaction commit can return a connection with an open native transaction to the pool #101
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:
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.
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.
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
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.
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:
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.
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.
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.
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.
If COMMIT fails, the dropped Transaction runs finish_(). That checks is_autocommit() (sqlite3_get_autocommit) and rolls back if the transaction is still open.
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.
Activity
developernotes commentedon Nov 4, 2026
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.
npars commentedon Nov 18, 2026
Fairly certain we encountered the same issue. Here's the AI summary:
Summary
If
COMMITfails, for example when aDEFERRABLE INITIALLY DEFERREDforeign key is violated,SQLite keeps the transaction open.
SQLiteSession.endTransactionUnchecked()removes its owntransaction record before it runs
COMMIT, then callsreleaseConnection()in afinallyblock and never rolls back. The connection goes back to the pool with the native transaction
still open.
Consequences:
SQLiteDatabase.inTransaction()returnsfalsewhile the native transaction is open.beginTransaction()on the connection fails withcannot start a transaction within a transaction (code 1). Every later write fails the sameway until something issues a raw
ROLLBACK.This happens with and without WAL, so it doesn't need a second connection or any concurrency.
Environment
net.zetetic:sqlcipher-android:4.17.04.20.0(latest release, equal tomasterHEAD) and5.0.0-beta.1sdk_gphone64_arm64), Android 16 / API 36Reproduction
An instrumentation test that uses only
net.zetetic.database.sqlcipherpublic APIs. It ran onthe environment above.
Observed output (
adb logcat -s Repro)Expected
After
endTransaction()throws, the connection should be clean: no native transaction open,count=0, and the nextbeginTransaction()should succeed.SQLiteDatabaseusers can't fix thisthemselves 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 toBEGIN) before the caller can issueROLLBACK.Root cause
SQLiteSession.java#L409-L454@ v4.17.0(the same code is at
v4.20.0):
This logic comes from AOSP and is unchanged there
(android-16.0.0_r1
SQLiteSession.java).So the framework
android.database.sqlitemost likely has the same bug. We only ran the reproagainst SQLCipher.
SQLite documents that the transaction stays open
"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."
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…"
"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 whilea 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(), ifCOMMITthrows, issueROLLBACKon the same connection,before
releaseConnection(). Add anyROLLBACKfailure as a suppressed exception and rethrowthe original
COMMITexception:A second safety net would be for the pool to check
sqlite3_get_autocommit() == 0beforerecycling a connection, and either roll back or discard the connection. That appears to be the
approach in PR #100. If the
ROLLBACKitself fails, the connection should be closed rather thanreturned to the pool.
Other libraries that already handle this
Each link is pinned to a commit.
sqlite3.go#L751-L764COMMITerror it callsROLLBACKon 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.ConnectionPoolImpl.kt#L461-L474if (delegate.inTransaction()) delegate.executeSQL("ROLLBACK TRANSACTION"). This is Google's own Android connection pool.Database.swift#L1543-L1560,#L1746-L1798commit()throws, it callsrollback(), which checkssqlite3_get_autocommit() == 0(isInsideTransaction) and runsROLLBACK TRANSACTION.SerializedDatabase.swift#L297-L308also fails hard if a transaction is still open when a database access ends.transaction.rs#L187-L248COMMITfails, the droppedTransactionrunsfinish_(). That checksis_autocommit()(sqlite3_get_autocommit) and rolls back if the transaction is still open.transaction.js#L64-L77COMMITruns inside thetry. Thecatchdoesif (db.inTransaction) undo.run()(ROLLBACK), then rethrows the original error.Impact
problem and issues a raw
ROLLBACK.ROLLBACKafter catching the failure fromendTransaction())can't be made reliable. The connection is back in the pool before the app's catch block runs.