Geeks With Blogs
Denis Pitcher Denis Pitcher's Tech Blog

Ok, felt this bug was worth noting.

I'm maintaining some code that was originally written to utilize a sql connection code generator called bonebox.  I was notified of an error that occured where a few reports that were running were timing out.  Upon reviewing my log messages I discovered that the sql server appeared to be blocking during connection attempts.  (as a side note: I was informed later that another application I'm not responsible for was stuck and was eating up most of the processing power of our sql box)

So I proceeded to run an sp_who2 sproc on my sql database to watch the number of connections that were being made.  I noted that there were a rediculus number of connections, some 150 from just one copy of my application.  This seemed like an absolutely rediculus number of connections for a single instance of an application.

So, I proceeded to go through the code of the generator and I made a few changes.  Wherever a connection was implicitly opened, I closed it when it appeared the code would be finished with it.

I also changed some DataReader commands from

cmd.ExecuteReader(CommandBehavior.Default);

to

cmd.ExecuteReader(CommandBehavior.CloseConnection);

Primarily because a DataReader does not close connections when it's finished unless you add this parameter (apparently in my reading it was suggested that even when specified, it may not close it, but I did not encounter this in my efforts)

So, after re-runing my report and checking the sp_who2 sproc on my sql box, I happily noted that there were at most 3-4 connections now where there used to be 150. 

A job well done... or so I thought.

Shortly thereafter I was notified of an error that occured during the processing of a different part of the application that utilizes transactions to save a bulk of data.

The error that was thrown:

The SqlTransaction has completed; it is no longer usable

Googling and searching yielded little help on this issue as most of the suggestions I could find didn't relate to my problem.  Seeing that the only recent change I had made was the closing of connections, I assumed that this must be the cause of my error, I just didn't know where to go to solve it.

After much digging and putting a watch on the transaction as it passed through various methods, I noted that the IsolationLevel property of the transaction was displaying the following message after a few operations

IsolationLevel <error: an exception of type: {System.InvalidOperationException} occurred> System.Data.IsolationLevel

So after too much line by line debugging, I realised that the connection shouldn't be closed during the operation of a transaction or it probably would invalidate it, which would explain the transaction completed error.  So, I went back through my code and added checks to determine whether or not a transaction was in use, if so, I left the connection open, if not, I closed it.

Example:

if ( trans != null )
{

cmd = new SqlCommand("select Scope_Identity()", conn, trans);
reader = cmd.ExecuteReader(CommandBehavior.Default);

}
else
{

cmd = new SqlCommand("select Scope_Identity()", conn);
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

}

...  and at the end of the method when finished with the reader ...

reader.Close();

if (conn.State == ConnectionState.Open && trans == null)

conn.Close();

 

Finally, I set the connection to be closed when a transaction is committed to ensure that connections arn't left open and I run into pooling issues when I've got multiple users trying to use the application at once.

In summary, it's been a pain in the butt to track this one down and I'm hoping that I've now solved the issue.  I've of course noted it here incase anyone else runs into a similar error and needs to save themselves a few hours of banging their head on a desk

 

Posted on Monday, May 22, 2006 3:50 PM | Back to top


Comments on this post: The SqlTransaction has completed; it is no longer usable

# re: The SqlTransaction has completed; it is no longer usable
Requesting Gravatar...
I got the same error message for a totally different reason. I hope this helps someone else.
The reason I got "This SqlTransaction has completed; it is no longer usable" was because I built a training database incorrectly. The production database was huge, so I didn't want to make a copy of the whole thing. I made a script in Enterprise Manager and forgot to check off the boxes to get the constraints. (I am but an egg; I took the SQL class in June and built the training DB in October.) I got my 1000 records into the table I work with and their related records into the other tables in my training DB. My ASP.NET application references some .DLLs written by another programmer. In my transaction, I set up a new record and pass it off to the other guy's code to insert it into the DB. On the production DB, it works fine (it has since last year, when I wrote it). On my training DB it blew up. It blew up in the other guy's code because some fields could not be null and my DB did not have the defaults set up that the production DB had. The insert failed, which apparently completes the transaction, so when I tried to do the commit it wouldn't let me.
Left by The Terminated on Oct 20, 2006 6:44 PM

# re: The SqlTransaction has completed; it is no longer usable
Requesting Gravatar...
I had a similar kind of problem, after seeing your explanation I found Im closing the connection in one place without checking the transaction's instance availability.

Your posting helped me a lot.

THANKS very much.
Left by Shankar Raj on Feb 12, 2007 8:37 AM

# re: The SqlTransaction has completed; it is no longer usable
Requesting Gravatar...
the code of sql transaction
Left by nempal on Apr 10, 2007 6:58 AM

# re: The SqlTransaction has completed; it is no longer usable
Requesting Gravatar...
Dear All,

Run the below command
DBCC CHECKDB
You will find errors occurs in Tables.
Findout the Tables and Run the following command.
This error is due to Table Index is corrupted.
If the database is in Muti-User Format change to Single User Format and run the following command
Once You ru DBCC Checktable ('TableName',REPAIR_REBUILD). The above mentioned reasons are not correct.

Regards,
Arun
[AMARA RAJA BATTERIES LTD., TIRUPATI]
Left by Arun Kumar on Dec 03, 2007 5:33 AM

# re: The SqlTransaction has completed; it is no longer usable
Requesting Gravatar...
I had the same error produced. The problem was inside because my code was like

try{
...code...
trans.commit();
sendemail();
}
catch{
trans.rollback();
}
finally
{
conn.close()
}

now because trans.commit will occur and if problem occurs in sendemail, it will goto catch block and do trans.rollback, and hence I was getting the problem. So inside the try block I did

try{
..code..
try{
sendemail();
trans.commit();
}
catch{
..}
}
catch{
trans.rollback();
}
finally{
conn.close()
}


and now everythings fine.
Left by Somebody on Dec 06, 2007 3:19 PM

# re: The SqlTransaction has completed; it is no longer usable
Requesting Gravatar...
I had the same problem, your article helpmed to solve that issue and not to bank my head on my laptop rather I solved the issue in minutes because of this article. I am very much appreciated for this. Keep it up.
--- Phani
Left by Phani Nadigadda on Feb 18, 2008 10:59 AM

# re: The SqlTransaction has completed; it is no longer usable
Requesting Gravatar...
But your condition will never commit the transaction as the for first time it wont be null.
Left by Madhan on Jun 10, 2008 5:04 AM

# re: The SqlTransaction has completed; it is no longer usable
Requesting Gravatar...
I got the same error because of a dropped table in a UNION clause.
Left by Dogan Erdogant on Sep 16, 2008 9:46 AM

# re: The SqlTransaction has completed; it is no longer usable
Requesting Gravatar...

SAME error but this time some has used

GRANT EXECUTE ON [dbo].[usp_XXXX] TO [public] inside a update usp. will add more...
Left by Hemant on Dec 04, 2008 12:44 PM

# re: The SqlTransaction has completed; it is no longer usable
Requesting Gravatar...
USE XXX
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_xxx]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_xxx]
GO




CREATE procedure dbo.usp_xxxxxx
AS
BEGIN


BEGIN

--lOGIC XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

END

--ERROR ONLY WHEN PUT HERE -- one GO for 2 diff. type of stmts one DML & one DCL!!!
GRANT EXECUTE ON [dbo].[usp_GetTariffRateForBillLine] TO [public]
GO

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


-- IF WE PUT SAME CODE HERE NO ERROR
GRANT EXECUTE ON [dbo].[usp_GetTariffRateForBillLine] TO [public]
GO
Left by Hemant on Dec 04, 2008 1:00 PM

# re: The SqlTransaction has completed; it is no longer usable
Requesting Gravatar...
.Net code justs rollbacks current tran. & starts new with without letting any one know! Any guesses WHY?
Code will give error only if you want to access old tran. (which has been rolledback!!) in your new tran.
Left by Hemant Batra on Dec 04, 2008 1:38 PM

# re: The SqlTransaction has completed; it is no longer usable
Requesting Gravatar...
i had the same error inserting from visual basic .net but i got the error just in single cases, so the response that i found was a bad relationship between 3 tables, i deleted them and it works!!

the error is from sql server


(sorry about my bad english)
Left by Angel Arguijo on May 15, 2009 2:23 PM

# re: The SqlTransaction has completed; it is no longer usable
Requesting Gravatar...
I had the same problem... but only sporadically! It eventually turned out that I was occasionally getting deadlocks in the database and SqlServer was choosing a deadlock victim and killing this transaction.

Unfortunately, the code to handle this condition was not rethrowing the exception and so the rest of my method carried on assuming that the transaction was still valid. This meant any writes after the deadlock were outside the scope of a transaction and so instantly committed to the database (very bad!!) and also I only got the above error much later in the code when I tried to commit or rollback the transaction.

The lesson for me is: If you can't handle an exception, throw it!

By the way - does anyone know of a way in .NET to test if a SqlTransaction object is still valid - i.e. hasn't been rolled back by the database for any reason?

Thanks
Left by Vassago on Jun 05, 2009 9:38 AM

# re: The SqlTransaction has completed; it is no longer usable
Requesting Gravatar...
Great post, I had a similiar problem.
I changed my data provider to this and it corrected my error.
Thank you

If _transaction Is Nothing Then
reader = command.ExecuteReader(CommandBehavior.CloseConnection)
Else
reader = command.ExecuteReader(CommandBehavior.Default)
End If
Left by Jason R on Jun 19, 2009 8:02 PM

# re: The SqlTransaction has completed; it is no longer usable
Requesting Gravatar...
I had the exact same problem, and it's solved with the exact same solution.

Thank you very much
Left by Hayke G on Sep 10, 2009 1:02 PM

# re: The SqlTransaction has completed; it is no longer usable
Requesting Gravatar...
Thanks, I was facing similar problem, Your post helped me resolve it. Thanks a lot.
Left by Abhijeet Nagre on Apr 28, 2010 6:04 AM

# re: The SqlTransaction has completed; it is no longer usable
Requesting Gravatar...
Thanks! I think I have the exact same problem, hopefully this will help me fix it. :)
Left by Hansol on Jul 20, 2010 8:11 AM

# re: The SqlTransaction has completed; it is no longer usable
Requesting Gravatar...
you really needed hours to find out this? This is like basic programming
Left by peet on Oct 27, 2011 8:58 AM

Your comment:
 (will show your gravatar)


Copyright © Denis Pitcher | Powered by: GeeksWithBlogs.net