|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Thursday, November 19, 2009 3:29 AM
Points: 1,
Visits: 8
|
|
Hi
I am getting the following error trying to commit transaction - This SqlTransaction has completed; it is no longer usable. The transaction involves saving a quite large object and its associated entities. The entire transaction involves multiple select and insert statements as well potentially a couple of updates. The code runs fine up until the point where the transaction is commited at which point the following exception is thrown
System.Data.SqlClient.SqlException was caught
Message="The transaction operation cannot be performed because there are pending requests working on this transaction."
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
The transaction object involved contains the following
'dbTrans.transaction.IsolationLevel' threw an exception of type 'System.InvalidOperationException'
This SqlTransaction has completed; it is no longer usable.
At the point prior to the commit statement, the connection is open and the transaction appears to be valid. I've gone through my code and I'm certain there is only one connection and one transaction object is created during the process.
Running the exact same code not wrapped in a transaction works fine, so this is probably not a problem with the database. I've also run DBCC CHECKDB command to verify this. SQL server profiler and logs also do not appear to yield any clues.
We are running SQL Server 9.0.3175 if that helps at all.
I'm no DBA, just a mere developer with only enough database knowledge to get by, so I hoping one of you serious SQL Server guys can help me out with some pointers on how to track this problem down!
Thanks in advance
Spencer
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Friday, March 19, 2010 11:50 AM
Points: 1,402,
Visits: 2,070
|
|
It might not be the right answer, but check your code for DDL operations inside of TRY/CATCH blocks. Even if your code runs perfectly without a transaction when it gets into the catch block, it won't run inside a transaction, because failure of DDL instructions makes the transaction non committable.
I don't remember if this is the exact error message associated with this scenario, but it could be something to investigate.
Regards
Gianluca
How to post for quicker answers
|
|
|
|
|
Hall of Fame
Group: General Forum Members
Last Login: 2 days ago @ 7:12 PM
Points: 3,082,
Visits: 12,545
|
|
Gianluca Sartori (11/18/2009)
It might not be the right answer, but check your code for DDL operations inside of TRY/CATCH blocks. Even if your code runs perfectly without a transaction when it gets into the catch block, it won't run inside a transaction, because failure of DDL instructions makes the transaction non committable.
I don't remember if this is the exact error message associated with this scenario, but it could be something to investigate.
Regards
Gianluca
This problem relates to either bad code or existing problem in SQL Server 2005 and below, if transactions are complex it is best to start with System.Transaction code passed to sqltransaction code which is converted to RDBMS transactions. But developers use short cuts so SQL Server and Oracle gets confused and end long running transactions. But ANSI SQL added transaction nesting at the point of connection which Microsoft implemented in SQL Server 2008 and VS2008. So either do more design work or upgrade to resolve this issue.
Kind regards,
Gift Peddie
|
|
|
|
