Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

This SqlTransaction has completed; it is no longer usable

I am using Linq to SQL with SQL Server 2008 SP2, my application is WinForm .net 4 I am importing into SQL Server 200,000 records. I am creating the objects and attach them to the datacontext on the "insertonsubmit". I then do "submitChanges" every 100 records. I have NO TRANSACTION what so ever.

I get this error

 "This SqlTransaction has completed; it is no longer usable." with stack
at System.Data.SqlClient.SqlTransaction.ZombieCheck()
   at System.Data.SqlClient.SqlTransaction.Rollback()
   at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
   at System.Data.Linq.DataContext.SubmitChanges()

Out of the blue. That error is not consistent, it may appear after 10,000 records or 27,200 records or XXX records.

Because error appears to be random, I was thinking it has something to do with the garbage collection...

Anyone has a clue?

like image 828
Varpi Avatar asked Feb 06 '11 09:02

Varpi


People also ask

Why this SqlTransaction has completed it is no longer usable?

You may get intermittent error from your application saying " This SqlTransaction has completed; it is no longer usable.". This may have to do with the way connection and transaction are handled in your application.

What is Zombie transaction?

A zombie transaction is a transaction that cannot be committed (due to an unrecoverable error) but is still open.


1 Answers

SubmitChanges has an implicit transaction. The problem here that your transaction is timing out - you are simply doing too much. For 200k rows I would use SqlBulkCopy into a staging table, and then run a command or SPROC to move that 200k into the actual table; perhaps with that command/SPROC in a transaction.

SqlBulkCopy is designed for this scenario - it is one continuous stream or raw TDS data, rather than however-may round-trips - and at the server end it is optimised too (but as a consequence, you must use a staging table, or you risk the inserts being non-logged).

like image 87
Marc Gravell Avatar answered Oct 06 '22 00:10

Marc Gravell