Repository navigation
Failed transaction unexpectedly leaves data in table #3736
Description
Describe the bug
Running several queries in a transaction with separate calls, one query having a conversion error, will fail at the commit step with an exception. Data inserted before the query having the conversion failure will be removed. Data inserted after the query having the conversion failure will still be present.
System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.
at Microsoft.Data.SqlClient.SqlTransaction.ZombieCheck()
at Microsoft.Data.SqlClient.SqlTransaction.Commit()
To reproduce
/*
The following two tables need to be present
create table tab1(
[Id] int identity(1,1) not null,
[Val] varchar(10) not null
) on [primary]
create table tab2(
[Id] int identity(1,1) not null,
[Val1] int not null,
[Val2] int not null
) on [primary]
insert into tab1 (Val) values ('12345')
insert into tab1 (Val) values ('42-43')
Note: The following works as I would expect in SSMS.
--set xact_abort off/on -- Makes no difference
begin transaction
insert into tab2 (Val1, Val2) values (42, 43)
select Id from tab1 where Val = 12345
insert into tab2 (Val1, Val2) values (100, 200)
commit transaction
At the end of transaction, nothing has been inserted.
Running the program below will report an error but still insert a row.
*/
using Microsoft.Data.SqlClient;
using System.Diagnostics.Tracing;
const string CONNECTION_STRING = "Data Source=...;Initial Catalog=...;User ID=...;Password=...;Encrypt=False";
try
{
using var listener = new SqlClientListener();
using SqlConnection connection = new(CONNECTION_STRING);
connection.Open();
using SqlTransaction transaction = connection.BeginTransaction();
SqlCommand cmd1 = new("insert into tab2 (Val1, Val2) values (42, 43)", connection, transaction);
cmd1.ExecuteNonQuery();
// This query will have a server response with the message;
//
// 'Conversion failed when converting the varchar value '42-43' to data type int.'
//
SqlCommand cmd2 = new("select Id from tab1 where Val = 12345", connection, transaction);
var id = cmd2.ExecuteScalar();
Console.WriteLine($"id={id}"); // 'id' is fine...
// This works
SqlCommand cmd3 = new("insert into tab2 (Val1, Val2) values (100, 200)", connection, transaction);
cmd3.ExecuteNonQuery();
// This fails with an exception
transaction.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
// At completion;
// The first "insert into..." *will not* be present in table "tab2"
// The second "insert into..." *will be* present in table "tab2"
public class SqlClientListener : EventListener
{
protected override void OnEventSourceCreated(EventSource eventSource)
{
if (eventSource.Name.Equals("Microsoft.Data.SqlClient.EventSource"))
{
EnableEvents(eventSource, EventLevel.Informational, EventKeywords.All);
}
}
protected override void OnEventWritten(EventWrittenEventArgs eventData)
{
Console.WriteLine(eventData.Payload?[0] ?? "<null>");
}
}
----- project-file
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net8.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Microsoft.Data.SqlClient" Version="6.0.1" />
</ItemGroup>
</Project>Expected behavior
I expected that no insertions would be present in table after the execution of the program.
Further technical details
.NET SDK:
Version: 8.0.318
Commit: e7c684c866
Workload version: 8.0.300-manifests.d6fa65f0
MSBuild version: 17.10.46+aa888d321
Runtime Environment:
OS Name: Windows
OS Version: 10.0.22631
OS Platform: Windows
RID: win-x64
.NET runtimes installed:
Microsoft.AspNetCore.App 3.1.32
Microsoft.AspNetCore.App 6.0.3
Microsoft.AspNetCore.App 6.0.30
Microsoft.AspNetCore.App 7.0.19
Microsoft.AspNetCore.App 7.0.20
Microsoft.AspNetCore.App 8.0.5
Microsoft.AspNetCore.App 8.0.21
Microsoft.NETCore.App 3.1.32
Microsoft.NETCore.App 6.0.3
Microsoft.NETCore.App 6.0.30
Microsoft.NETCore.App 7.0.19
Microsoft.NETCore.App 7.0.20
Microsoft.NETCore.App 8.0.5
Microsoft.NETCore.App 8.0.21
Microsoft.WindowsDesktop.App 3.1.32
Microsoft.WindowsDesktop.App 6.0.3
Microsoft.WindowsDesktop.App 6.0.30
Microsoft.WindowsDesktop.App 7.0.19
Microsoft.WindowsDesktop.App 7.0.20
Microsoft.WindowsDesktop.App 8.0.5
Microsoft.WindowsDesktop.App 8.0.21
Metadata
Metadata
Assignees
Labels
Type
Projects
- Status
Activity
priyankatiwari08 commentedon Nov 5, 2025
We have successfully reproduced the issue and confirmed it as a bug. Our team is working on a fix and will share further updates as soon as they are available.
samsharma2700 commentedon Jan 22, 2026
Hi @garfur42, thank you for bringing this to our attention.
I've finished initial investigation of the issue and identified the root cause. Here's what's happening:
Root Cause
When
ExecuteScalar()is called (cmd2 in your repro), the server sends both partial data and an error. However,ExecuteScalar()reads the first row/column value and closes theSqlDataReaderbefore the error packet is encountered. This causes the exception to be consumed internally rather than propagated to the caller.Key Findings
SqlDataReadercloses successfully before encountering the error (visible in logs:SqlDataReader.Closeoccurs beforeSqlError.ctor)Notes of Driver Behavior
ExecuteNonQuery()works correctly and raises the appropriate exceptionWorkaround
If this is blocking you, please use
ExecuteNonQuery()instead ofExecuteScalar()for now.Next Steps
I'm working on a fix to ensure exceptions are properly propagated in
ExecuteScalar(). I'll update this thread with more details soon.vonzshik commentedon Jan 23, 2026
This seems to be the exact same thing as #458, which is an almost 6 year old bug.
1 remaining item
Fix dotnet#3736 | Propagate Errors from ExecuteScalar (dotnet#3912)
edwardneal commentedon Feb 3, 2026
@vonzshik to back this up, I've just tested the reproduction case from #458. I could still reproduce the problem from the other issue before @samsharma2700 merged #3912, and it seems to be fixed afterwards.
samsharma2700 commentedon Feb 4, 2026
@edwardneal Thank you so much for the confirmation! Glad fix is working.
Add gh CLI commands for issue milestone updates
Update analysis with current GitHub state - all PRs complete!
Port #3912 to release/6.1