Sergei Dorogin

Hello.
I have an issue with interrupting a thread that executing sql-command.
Let's look at this code (.net 2.0, c#):
Code Snippet

using(SqlConnection con = createConnection())
{
con.Open();
SqlTransaction tran = con.BeginTransaction();
SqlCommand cmd = con.CreateCommand();
cmd.Transaction = tran;
cmd.CommandText = <some long T-SQL command>;
try {
cmd.ExecuteNonQuery();
....
cmd.Transaction.Commit();
}
catch(Exception)
{
// process the exception and rollback the transaction
tran.Rollback();
}
}


Is it correct code I suppose yes. We may hope that our transaction will be rollbacked in the case of any exception.
Now imagine we run this code in separate thread. Then we interupt that thread by calling Thread.Abort. ThreadAbortException occurs in the thread of sql-command execution.
This exception can be raised when our thread is in SqlCommand.ExecuteNonQuery.
We're interested only in this case.
What is happening in this case A catastrophe! Why
In the case ThreadAbortException in catch-block our SqlConnection will be closed! It's being closed in the internals of ExecuteNonQuery method. So we can't rollback our transaction (Rollback method executes without any error but it doen't really rollback the transaction. That can be seen in SqlProfiler).
That leads to a situation of all locks are set by the transaction are still pending.
I have detailed testcase showing this situation, but I can't find a way to attach any file to a post.

I look at implementation of SqlCommand/SqlConnection with help of Reflector.
SqlCommand.ExecuteNonQuery calls InternalExecuteNonQuery. This method (InternalExecuteNonQuery) in the case of ThreadAbortException calls SqlConnection.Abort.
Great! But what about transactions with their locks



Re: .NET Framework Data Access and Storage Lost pending locks of a transaction interrupted by Thread.Abort

pawel.stanek

I think that if you modify your catch statement you will solve the problem . This is what I would do:

int rowsAffected = 0;
try{

commit();
}
catch(SqlException excp)
{trans.Rollback();}
catch(ThreadAbortException excp)
{trans.Rollback();}
catch(...){}
finally
{conn.Close();}


If it does not help provide me/us with some code Smile
Cheers

You should remember that InvalidOperationException will be raised if you call RollBack or Commit twice.





Re: .NET Framework Data Access and Storage Lost pending locks of a transaction interrupted by Thread.Abort

Sergei Dorogin

Hi, pawel.stanek.
In my example I wrote "catch(Exception)", so it catches also ThreadAbortException (and the code tries to rollback the transaction). What's the sense in catching ThreadAbortException specifically
The problem is DB-connection is closed already when we're in a catch-block. In any catch block! catch(Exception) or catch(ThreadAbortException). It doesn't matter.

How can I provide you with test-case code




Re: .NET Framework Data Access and Storage Lost pending locks of a transaction interrupted by Thread.Abort

Matt Neerincx

From digging around the MSDN posts, I found some from Alazel that indicate IF a thread abort occurs, the connection is marked as aborted.

So my guess (I could be wrong) is that the connection is marked as aborted and put back in the pool, next time the connection is used the pooling code (server side) will reset the transactional state of the connection. But to be honest I am not sure about this behavior it would take me some time to investigate.

What version of .NET are you using Post back and I will try to investigate if there is a better way to handle this (I have the luxury of being able to go walk over and talk to the actual SQLClient developers).






Re: .NET Framework Data Access and Storage Lost pending locks of a transaction interrupted by Thread.Abort

Sergei Dorogin

Hi, Matt.
Thank you for answering!
I use .NET 2.0.
I'am looking forward to any news from you.
It's just very interisting what's major approach in hanlding connections and transactions in code, that can be aborted by another thread.
I took many tests. I can say when a process unload all locks are released. It's ok for client apps, but not for server where a process has long life.

Here's my test-case source code. May be it helps.

Code Snippet
using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading;

class WorkItem
{
private Thread m_executionThread;
private SqlConnection m_con;

public WorkItem(SqlConnection con)
{
m_con = con;
}

public Thread ExecutionThread { get { return m_executionThread; } }

public void Start()
{
m_executionThread = new Thread( new ThreadStart(threadFunc) );
m_executionThread.Start();
}

public void threadFunc()
{
SqlTransaction tran = null;
Console.WriteLine("threadFunc::starts");
try
{
m_con.Open();
Console.WriteLine("\tDB-connection state after Open(): " + m_con.State);
using(SqlCommand cmd = m_con.CreateCommand())
{
tran = m_con.BeginTransaction();
cmd.Transaction = tran;
cmd.CommandText = @"
declare @n int
declare @nCount int
begin
set @n = 100000
select @nCount = count(*) from t_test with(holdlock)
while @n > 0
begin
insert into t_test values (@n)
set @n = @n - 1
end
end";
Console.WriteLine("threadFunc::DB-command has ran");

cmd.ExecuteNonQuery();
Console.WriteLine("threadFunc::executing DB-command has completed");
Thread.Sleep(10000);
tran.Commit();
}
}
catch(Exception ex)
{
Console.WriteLine("threadFunc::catch : " + ex.Message);
Console.WriteLine("\tDB-connection state: " + m_con.State);
if (tran != null)
{
Console.WriteLine("\trolling back transaction");
tran.Rollback();
}
}
finally
{
Console.WriteLine("threadFunc::finally");
Console.WriteLine("\tDB-connection state: " + m_con.State);
m_con.Close();
Console.WriteLine("\tDB-connection state after Close(): " + m_con.State);
}
}
}

public class Test
{
private static string m_sConnectionString;
public static SqlConnection getConnection()
{
SqlConnection con = new SqlConnection(m_sConnectionString);
return con;
}

public static void Main(string[] args)
{
if (args.Length > 0)
m_sConnectionString = args[0];
else
m_sConnectionString = "Initial Catalog=master;Data Source=.;Persist Security Info=False;Integrated Security=SSPI;";

// create table t_test(x int)
if (!prepare())
return;

WorkItem w = new WorkItem(getConnection());
w.Start();
Thread.Sleep(1000);
//w.ExecutionThread.Interrupt();
w.ExecutionThread.Abort();
w.ExecutionThread.Join(5000);
Console.WriteLine("Main finished");

try
{
using(SqlConnection con = getConnection())
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandTimeout = 2; // 2 seconds
cmd.CommandText = "select count(*) from t_test";
Console.WriteLine( "Main::rows count : " + cmd.ExecuteScalar() );
}
}
catch(Exception ex)
{
Console.WriteLine("Catastrophe!\n" + ex.ToString());
}
}

public static bool prepare()
{
try
{
using(SqlConnection con = getConnection())
{
con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = @"
if exists (select 1 from information_schema.TABLES where TABLE_SCHEMA+'.'+TABLE_NAME='dbo.t_test' AND TABLE_TYPE='BASE TABLE')
DROP TABLE [dbo].[t_test]
create table t_test(x int)
declare @n int
begin
set @n = 1000
while @n > 0
begin
insert into t_test values (@n)
set @n = @n - 1
end
end
";
cmd.ExecuteNonQuery();
}
return true;
}
catch(Exception ex)
{
Console.WriteLine("Exception during preparing test enviroment: " + ex);
return false;
}
}
}

Some comments to source code:
- I use table "t_test" for executing long tsql-command.
- this table is created by method "prepare"
- when you run .exe without command-line parameters this connection string is being used: "Initial Catalog=master;Data Source=.;Persist Security Info=False;Integrated Security=SSPI;" (change database on yours)
- you can specify connection string as command-line parameter
- Main-method creates instance of "WorkItem" class for running async command. That async command executes long tsql-command with acquiring exclusive locks on all rows of t_test table. During executing that command it's aborted.
- After Main has aborted async command, it tries to select all rows of t_test table. And here we get SqlTimeoutException as all rows of t_test are locked by the first connection which was aborted





Re: .NET Framework Data Access and Storage Lost pending locks of a transaction interrupted by Thread.Abort

pawel.stanek

Yep. you are right my mistake Sad

I would try to call Cancel() (later trans.Rollaback() and then conn.Close() ) on long running SqlCommand (which is in separate Thread , so using Monitor or something alike would be needed) before calling Thread.Abort();






Re: .NET Framework Data Access and Storage Lost pending locks of a transaction interrupted by Thread.Abort

CommonGenius.com

This appears to be a signficant bug in the SqlClient. The connection is "doomed", which appears to be an intermediate state between open and closed, such that no operations can be performed on the connection (including rollback), but the resources of the collection are not released, and any transactions stay open. I would suggest filing a bug at https://connect.microsoft.com/VisualStudio/Feedback. Post the url to the bug report here and I will validate it.




Re: .NET Framework Data Access and Storage Lost pending locks of a transaction interrupted by Thread.Abort

Sergei Dorogin

The bug report:
https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx FeedbackID=276528