DuckDBTransaction object gets into bad state when transaction commit fails #301
Description
When this line is hit:
and the query throws in duckdb, the DuckDBTransaction object gets into a state where all three methods Commit, Rollback, and Dispose will always throw an exception, and these lines can never be reached:
DuckDB.NET/DuckDB.NET.Data/DuckDBTransaction.cs
Lines 42 to 43 in c5c7ff8
which means even if the exceptions are caught, the connection remains in an unusable state until its Transaction property is set to null (which doesn't seem permitted by the api). It also means disposal of the transaction object throws an exception.
I think this should be handled by catching exceptions from the finalizer execution and setting finished = true and connection.Transaction = null even on exception, but only if the transaction was aborted. The only thing I'm not sure about is how to best check that the exception was one that caused a transaction abort, as opposed to one like a IO error.
Repro
// this line is for a file-based app
#:package DuckDB.NET.Data.Full@1.4.1
using DuckDB.NET.Data;
// ignore this block of code, it's just to make the transaction commit fail
using var conn1 = new DuckDBConnection("Data Source=my.db");
await conn1.OpenAsync();
using var cmd1 = conn1.CreateCommand();
cmd1.CommandText = "CREATE TABLE IF NOT EXISTS test_table (id INTEGER PRIMARY KEY);";
await cmd1.ExecuteNonQueryAsync();
cmd1.CommandText = "INSERT OR IGNORE INTO test_table VALUES (1);";
await cmd1.ExecuteNonQueryAsync();
using var tx1 = conn1.BeginTransaction();
cmd1.Transaction = tx1;
cmd1.CommandText = "SELECT id FROM test_table LIMIT 1;";
using var reader1 = await cmd1.ExecuteReaderAsync();
if (await reader1.ReadAsync())
{
var readRecord = reader1.GetInt32(0);
Console.WriteLine($"Read record in tx1: {readRecord}");
}
using var conn2 = new DuckDBConnection("Data Source=my.db");
await conn2.OpenAsync();
using var cmd2 = conn2.CreateCommand();
cmd2.CommandText = "UPDATE test_table SET id = 1 WHERE id = 1;";
await cmd2.ExecuteNonQueryAsync();
Console.WriteLine("First update completed");
using var tx2 = conn2.BeginTransaction();
cmd2.Transaction = tx2;
cmd2.CommandText = "UPDATE test_table SET id = 1 WHERE id = 1;";
await cmd2.ExecuteNonQueryAsync();
Console.WriteLine("Ran second update statement");
// This is where the issue is: after the first commit which fails due to an index limitation in duckdb (https://github.com/duckdb/duckdb/issues/17802),
// the transaction object gets into a state where nothing can be done with it without throwing an exception,
// because duckdb considers the transaction "finalized" (aborted) but DuckDB.NET does not yet consider it finalized
try
{
tx2.Commit();
}
catch (Exception ex)
{
Console.WriteLine($"Caught exception on commit: {ex.Message}");
}
try
{
tx2.Commit();
}
catch (Exception ex)
{
Console.WriteLine($"Caught exception on commit: {ex.Message}");
}
try
{
tx2.Rollback();
}
catch (Exception ex)
{
Console.WriteLine($"Caught exception on rollback: {ex.Message}");
}
try
{
tx2.Dispose();
}
catch (Exception ex)
{
Console.WriteLine($"Caught exception on dispose: {ex.Message}");
}Output:
Read record in tx1: 1
First update completed
Ran second update statement
Caught exception on commit: TransactionContext Error: Failed to commit: write-write conflict on key: "1"
Caught exception on commit: TransactionContext Error: cannot commit - no transaction is active
Caught exception on rollback: TransactionContext Error: cannot rollback - no transaction is active
Caught exception on dispose: TransactionContext Error: cannot rollback - no transaction is active
Unhandled exception. DuckDB.NET.Data.DuckDBException (0x0000000A): TransactionContext Error: cannot rollback - no transaction is active
at DuckDB.NET.Data.PreparedStatement.PreparedStatement.Execute(DuckDBParameterCollection parameterCollection, Boolean useStreamingMode)
at DuckDB.NET.Data.PreparedStatement.PreparedStatement.PrepareMultiple(DuckDBNativeConnection connection, String query, DuckDBParameterCollection parameters, Boolean useStreamin
gMode)+MoveNext()
at DuckDB.NET.Data.DuckDBCommand.ExecuteNonQuery()
at DuckDB.NET.Data.Extensions.DbConnectionExtension.ExecuteNonQuery(IDbConnection connection, String sql)
at DuckDB.NET.Data.DuckDBTransaction.FinishTransaction(String finalizer)
at DuckDB.NET.Data.DuckDBTransaction.Rollback()
at DuckDB.NET.Data.DuckDBTransaction.Dispose(Boolean disposing)
at Program.<Main>$(String[] args)
at Program.<Main>$(String[] args)
at Program.<Main>(String[] args)
(note also the final Unhandled exception, thrown from the cleanup of using var tx2)