Incorrect handling of errors that cause automatic rollbacks in SQLite

[firenero]

Bug description

Some SQLite errors can cause automatic rollback when errors occur within a transaction. The way SqliteTransaction in Microsoft.Data.Sqlite handles this case makes usage of using pattern for SqliteConnection and SqliteTransaction impossible.

The bug happens the most often for SQLITE_FULL error. Consider the following code:

private static void Save(string connectionString)
{
    using var conn = new SqliteConnection(connectionString);
    conn.Open();
    
    using var transaction = conn.BeginTransaction();
    const string sql = "insert into TestEntities (Key, Value) values (@Key, @Value)"; 
    conn.Execute(sql, new { Key = "1", Value = "val_1" }, transaction);
    conn.Execute(sql, new { Key = "2", Value = "val_2" }, transaction);
    
    transaction.Commit();
}

If there is not enough space to commit the transaction, transaction.Commit() will throw a SQLite Error 13: 'database or disk is full'. error, caused by this line internally. However, this error will be swallowed and SQLite Error 1: 'cannot rollback - no transaction is active'. will be thrown instead because rollback is executed during Dispose call despite transaction being closed by SQLite automatically.

Proposed solution

Use sqlite3_get_autocommit() to determine if transaction has been closed already and execute the rollback only if it's still open.
I haven't run any benchmarks yet but potential optimization to this solution is to catch exception during commit and only call sqlite3_get_autocommit() if commit failed due to one of four errors that can close the transaction automatically.

I can submit a PR with the fix if this solution sounds good and likely to be merged.

Your code

Code like this is the common scenario that causes the issue:

private static void Save(string connectionString)
{
    using var conn = new SqliteConnection(connectionString);
    conn.Open();
    
    using var transaction = conn.BeginTransaction();
    const string sql = "insert into TestEntities (Key, Value) values (@Key, @Value)"; 
    conn.Execute(sql, new { Key = "1", Value = "val_1" }, transaction);
    conn.Execute(sql, new { Key = "2", Value = "val_2" }, transaction);
    
    transaction.Commit();
}

I've also created a full solution that reproduces the issue locally: https://github.com/firenero/SqliteSpaceErrorRepro

Stack traces

Propagated exception stack trace:
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'cannot rollback - no transaction is active'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at Microsoft.Data.Sqlite.SqliteConnectionExtensions.ExecuteNonQuery(SqliteConnection connection, String commandText, SqliteParameter[] parameters)
   at Microsoft.Data.Sqlite.SqliteTransaction.RollbackInternal()
   at Microsoft.Data.Sqlite.SqliteTransaction.Dispose(Boolean disposing)
   at SqliteSpaceErrorRepro.DapperWriter.RunWriteLoop(String connectionString)


Real exception stack trace:
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 13: 'database or disk is full'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at Microsoft.Data.Sqlite.SqliteConnectionExtensions.ExecuteNonQuery(SqliteConnection connection, String commandText, SqliteParameter[] parameters)
   at Microsoft.Data.Sqlite.SqliteTransaction.Commit()
   at SqliteSpaceErrorRepro.DapperWriter.RunWriteLoop(String connectionString)

Microsoft.Data.Sqlite version

9.0.8

Target framework

.NET 8

Operating system

Windows 11

[cincuranet]

I don't think sqlite3_get_autocommit is even needed. We already do sqlite3_rollback_hook, so properly using that should be enough.