Stop transaction in a thread

Question

I use a TransactionScope in a thread to dump data in my SQL Server database.

using (TransactionScope scope = new TransactionScope())
{
    // Dump data in database
    scope.Complete();
}

The transaction is a long transaction (40 secondes) because data are bigs : that's normal.

When I do an Abort() to stop the thread during this transaction, SQL Server seems to be locked during a few minutes.

What's happened ?

How can I avoid that ?

Answer 1

Simply said: you can not.

Server seems to be locked during a few minutes.

No. SQL Server is busy undoing your transaction. It is optimized fully to COMMIT transactions, not to undo them. Undoing LARGE updates is a SLOW operation. Depending on your server layout and the amount of data it can take some minutes or even hours.

All you can do is... not rollback / abort. Or wait.