SqlTransactions and Sql timeout errors

[Adam]

Dear all,

I seem to be having a strange problem when an sql statement times out
when it is running inside a transaction. I've written a small example
program of the problem (see below).

If the command text is changed from one that will timeout, to one that
is just invalid SQL (both throw SqlException's) the transaction
rollback behaviour is different.

In the case of a timeout I see get the following
InvalidOperationException when I try and rollback the transaction.
"This SqlTransaction has completed; it is no longer usable."

And in the case of invalid SQL the rollback complete's OK.

It seems as though the Sql timeout causes an automatic rollback, but
the invalid sql does not. This is a bit of a pain, as you can't tell
which of these have occured by looking at the exception type, but have
to examain the exception message.

Has anyone else seen any problems like this? Have I found a bug (well
quirk...) in ADO.Net or am I missing something!

TIA

Adam Pridmore


static void Main(string[] args)
{
try
{
FakeTest2();
Console.WriteLine("ok");
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
}

public static void FakeTest2()
{
SqlConnection connection = new SqlConnection(ConnectionString);
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = new SqlCommand();
command.CommandText = "WAITFOR TIME '00:00:30'";
//command.CommandText = "INVALID SQL'";
command.CommandTimeout = 3;
command.Connection = connection;
command.Transaction = transaction;
try
{
command.ExecuteNonQuery();
}
catch(SqlException ex)
{
transaction.Rollback();
}
}

 

[Mary Chipman]

Perhaps you could explain what you are trying to achieve with this
sample. Invalid SQL can't participate in a transaction, it's a syntax
error. Transactions are meaningful when you want separate operations
to execute as a single unit of work. I fail to see the point of how a
WAITFOR statement and invalid SQL syntax could reasonably constitute a
single unit of work.

--Mary

 

[Adam Pridmore]

The code I posted is a cut down version of the problem!

In the 'real' app we have a transaction component that handles database
activity, but if there is an error by default it rollsback the transaction.
But in the case when it was an timeout, it throws another error when trying
to rollback as it has already automatically been rolled back seemingly by
ADO.Net.

We can't easily tell what has happened to the transaction in the component
as we handle any commits, and rollbacks etc and keep track of the
transaction state. But if it gets rollback for use we don't know about it...

I don't believe it is possible to get the transition state from the
transaction or connection it is associated with.

Adam

Perhaps you could explain what you are trying to achieve with this
sample. Invalid SQL can't participate in a transaction, it's a syntax
error. Transactions are meaningful when you want separate operations
to execute as a single unit of work. I fail to see the point of how a
WAITFOR statement and invalid SQL syntax could reasonably constitute a
single unit of work.

--Mary

Dear all,

I seem to be having a strange problem when an sql statement times out
when it is running inside a transaction. I've written a small example
program of the problem (see below).

If the command text is changed from one that will timeout, to one that
is just invalid SQL (both throw SqlException's) the transaction
rollback behaviour is different.

In the case of a timeout I see get the following
InvalidOperationException when I try and rollback the transaction.
"This SqlTransaction has completed; it is no longer usable."

And in the case of invalid SQL the rollback complete's OK.

It seems as though the Sql timeout causes an automatic rollback, but
the invalid sql does not. This is a bit of a pain, as you can't tell
which of these have occured by looking at the exception type, but have
to examain the exception message.

Has anyone else seen any problems like this? Have I found a bug (well
quirk...) in ADO.Net or am I missing something!

TIA

Adam Pridmore


static void Main(string[] args)
{
try
{
FakeTest2();
Console.WriteLine("ok");
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
}

public static void FakeTest2()
{
SqlConnection connection = new SqlConnection(ConnectionString);
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = new SqlCommand();
command.CommandText = "WAITFOR TIME '00:00:30'";
//command.CommandText = "INVALID SQL'";
command.CommandTimeout = 3;
command.Connection = connection;
command.Transaction = transaction;
try
{
command.ExecuteNonQuery();
}
catch(SqlException ex)
{
transaction.Rollback();
}
}

 

[Mary Chipman]

You'll need to wrap your Rollback in its own try/catch block since
you'll just be triggering a runtime error if you try to roll it back
and it's not active. One way to keep track of what's going on is to
create a variable that gets set to different values in your various
Catch blocks. You can see an example in the Rollback help topic in the
Help file -- instead of console.writeline shown here, just write to
your variable:
ms-help://MS.VSCC.2003/MS.MSDNQTR.2004APR.1033/cpref/html/frlrfSystemDataSqlClientSqlTransactionClassRollbackTopic.htm

--Mary

The code I posted is a cut down version of the problem!

In the 'real' app we have a transaction component that handles database
activity, but if there is an error by default it rollsback the transaction.
But in the case when it was an timeout, it throws another error when trying
to rollback as it has already automatically been rolled back seemingly by
ADO.Net.

We can't easily tell what has happened to the transaction in the component
as we handle any commits, and rollbacks etc and keep track of the
transaction state. But if it gets rollback for use we don't know about it...

I don't believe it is possible to get the transition state from the
transaction or connection it is associated with.

Adam

Perhaps you could explain what you are trying to achieve with this
sample. Invalid SQL can't participate in a transaction, it's a syntax
error. Transactions are meaningful when you want separate operations
to execute as a single unit of work. I fail to see the point of how a
WAITFOR statement and invalid SQL syntax could reasonably constitute a
single unit of work.

--Mary

Dear all,

I seem to be having a strange problem when an sql statement times out
when it is running inside a transaction. I've written a small example
program of the problem (see below).

If the command text is changed from one that will timeout, to one that
is just invalid SQL (both throw SqlException's) the transaction
rollback behaviour is different.

In the case of a timeout I see get the following
InvalidOperationException when I try and rollback the transaction.
"This SqlTransaction has completed; it is no longer usable."

And in the case of invalid SQL the rollback complete's OK.

It seems as though the Sql timeout causes an automatic rollback, but
the invalid sql does not. This is a bit of a pain, as you can't tell
which of these have occured by looking at the exception type, but have
to examain the exception message.

Has anyone else seen any problems like this? Have I found a bug (well
quirk...) in ADO.Net or am I missing something!

TIA

Adam Pridmore


static void Main(string[] args)
{
try
{
FakeTest2();
Console.WriteLine("ok");
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
}

public static void FakeTest2()
{
SqlConnection connection = new SqlConnection(ConnectionString);
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = new SqlCommand();
command.CommandText = "WAITFOR TIME '00:00:30'";
//command.CommandText = "INVALID SQL'";
command.CommandTimeout = 3;
command.Connection = connection;
command.Transaction = transaction;
try
{
command.ExecuteNonQuery();
}
catch(SqlException ex)
{
transaction.Rollback();
}
}

 

[Adam]

Thanks.

Just another quick questions, but is there a reason why sql timeout
exceptions cause it to rollback automatically? Is it to do with the
way MS SQL server handles timeout errors as apposed to other runtime
SQL errors?

Adam

 

[Mary Chipman]

Basically the way transactions work in SQL Server is that the first
step is that the server acquires the necessary locks so that it can
commit the transaction. In committing the transaction, the server
guarantees that it will pass the ACID test (atomicity, consistency,
isolation and durability). If it fails any one of these, then the
transaction will be rolled back in its entirety, preserving data
integrity. If a transaction times out, it usually means that the
server has failed to acquire the necessary locks. Since the server
cannot guarantee any of ACID properties in a timeout, the entire
transaction gets rolled back. Anything that causes an DML operation to
fail will cause a rollback of a transaction. One thing to bear in mind
is that this alone is not considered to be a runtime error by the
server. For example, if an Update statement fails in a batch, then SQL
Server moves on to the next statement in the batch. You need to catch
this by checking @@Rowcount as well as @@error. In an explicit
transaction, you then issue either a commit or a rollback based on
your findings. I personally prefer coding explicit transactions in a
stored procedure, rather than in client code, where all of the
procedural and DML code is executing directly on the server. You then
just pass back the success/failure messages in output parameters.

--Mary

 

[Guest]

Dear all,

I seem to be having a strange problem when an sql statement times out
when it is running inside a transaction. I've written a small example
program of the problem (see below).

If the command text is changed from one that will timeout, to one that
is just invalid SQL (both throw SqlException's) the transaction
rollback behaviour is different.

In the case of a timeout I see get the following
InvalidOperationException when I try and rollback the transaction.
"This SqlTransaction has completed; it is no longer usable."

And in the case of invalid SQL the rollback complete's OK.

It seems as though the Sql timeout causes an automatic rollback, but
the invalid sql does not. This is a bit of a pain, as you can't tell
which of these have occured by looking at the exception type, but have
to examain the exception message.

Has anyone else seen any problems like this? Have I found a bug (well
quirk...) in ADO.Net or am I missing something!

TIA

Adam Pridmore


static void Main(string[] args)
{
try
{
FakeTest2();
Console.WriteLine("ok");
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
}

public static void FakeTest2()
{
SqlConnection connection = new SqlConnection(ConnectionString);
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = new SqlCommand();
command.CommandText = "WAITFOR TIME '00:00:30'";
//command.CommandText = "INVALID SQL'";
command.CommandTimeout = 3;
command.Connection = connection;
command.Transaction = transaction;
try
{
command.ExecuteNonQuery();
}
catch(SqlException ex)
{
transaction.Rollback();
}

User submitted from AEWNET (http://www.aewnet.com/)

 

[phavlik]

Dear all,

I seem to be having a strange problem when an sql statement times out
when it is running inside a transaction. I've written a small example
program of the problem (see below).

If the command text is changed from one that will timeout, to one that
is just invalid SQL (both throw SqlException's) the transaction
rollback behaviour is different.

In the case of a timeout I see get the following
InvalidOperationException when I try and rollback the transaction.
"This SqlTransaction has completed; it is no longer usable."

And in the case of invalid SQL the rollback complete's OK.

It seems as though the Sql timeout causes an automatic rollback, but
the invalid sql does not. This is a bit of a pain, as you can't tell
which of these have occured by looking at the exception type, but have
to examain the exception message.

Has anyone else seen any problems like this? Have I found a bug (well
quirk...) in ADO.Net or am I missing something!

TIA

Adam Pridmore


static void Main(string[] args)
{
try
{
FakeTest2();
Console.WriteLine("ok");
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
}

public static void FakeTest2()
{
SqlConnection connection = new SqlConnection(ConnectionString);
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = new SqlCommand();
command.CommandText = "WAITFOR TIME '00:00:30'";
//command.CommandText = "INVALID SQL'";
command.CommandTimeout = 3;
command.Connection = connection;
command.Transaction = transaction;
try
{
command.ExecuteNonQuery();
}
catch(SqlException ex)
{
transaction.Rollback();
}
}

User submitted from AEWNET (http://www.aewnet.com/)

 

[phavlik]

Hi there, I have found a similar problem, but it seems to be withdeadlocks. In other words a timeout allows a normal controlledTransaction.Rollback, but when a deadlock occurs it implicitlyrolls back the transaction. We check for a deadlock error and donot attempt a rollback if the error was a deadlock error. Theextract from the catch block is as follows:

catch(SqlException e)
{
bool deadlockError = ((e.Number == 1205) && (e.Class == 13));
bool timeoutError = ((e.Number == -2) && (e.Class == 10));

if (!deadlockError)
{
transaction.Rollback();
}

//etc. etc. .. we also implement retries on deadlock andtimeout here but that's
//out of the scope of the question
}

Dear all,

I seem to be having a strange problem when an sql statementtimes out
when it is running inside a transaction. I've written a smallexample
program of the problem (see below).

If the command text is changed from one that will timeout, toone that
is just invalid SQL (both throw SqlException's) thetransaction
rollback behaviour is different.

In the case of a timeout I see get the following
InvalidOperationException when I try and rollback thetransaction.
"This SqlTransaction has completed; it is no longer usable."

And in the case of invalid SQL the rollback complete's OK.

It seems as though the Sql timeout causes an automaticrollback, but
the invalid sql does not. This is a bit of a pain, as you can'ttell
which of these have occured by looking at the exception type,but have
to examain the exception message.

Has anyone else seen any problems like this? Have I found a bug(well
quirk...) in ADO.Net or am I missing something!

TIA

Adam Pridmore


static void Main(string[] args)
{
try
{
FakeTest2();
Console.WriteLine("ok");
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
}

public static void FakeTest2()
{
SqlConnection connection = newSqlConnection(ConnectionString);
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
SqlCommand command = new SqlCommand();
command.CommandText = "WAITFOR TIME '00:00:30'";
//command.CommandText = "INVALID SQL'";
command.CommandTimeout = 3;
command.Connection = connection;
command.Transaction = transaction;
try
{
command.ExecuteNonQuery();
}
catch(SqlException ex)
{
transaction.Rollback();
}
}

User submitted from AEWNET (http://www.aewnet.com/)