System.Transaction implicit transaction interaction with my other connections

I'm trying to use System.Transaction.TransactionScope to create a transaction to call multiple stored procedures, but it doesn't seem to get cleaned up after itself. Once the transaction is complete (completed or not, and the transaction scope object is deleted), subsequent connections to the database will open with a read commit level serializable, rather than read as usual.

I open and close the connection for every call (well closing and reversing the connection pool as usual in .NET), did I miss some way to explicitly reset the connection when I was done using it for a transaction? I thought the idea behind System.Transaction.TransactionScope was to hide all the complexity.

So the code I have is as follows:

            using (var scope = new TransactionScope())
            {
                ... make my 3 stored procedure calls ...

                scope.Complete();

                return returnCode;
            }

      

which I think is the usual way to do it. But if I look in the sqlserver profiler, I can see that connections are being opened with

set transaction isolation level serializable

      

which fiddles with transaction-related follow-up and apparently not that fast. I can get around this by setting a transaction option to explain the transaction with ReadCommited, but this is not ideal behavior for this operation in my opinion.

I also tried to explicitly create a Commitabletransaction object by creating explicit new transactions instead of using ambient and still no luck.

Any ideas on how to fix this would be much appreciated as any calls that use a serializable connection will throw an error if they try to use the readpast hint to block.

0


source to share


3 answers


Use TransactionOptions.IsolationLevel

By default, this is serialized



TransactionOptions transactionoptions1 = new TransactionOptions();
transactionoptions1.IsolationLevel = IsolationLevel.ReadCommitted;
using (var scope = new TransactionScope(TransactionScopeOption.Required, transactionoptions1))
{
    ... make my 3 stored procedure calls ...

    scope.Complete();

    return returnCode;
}

      

+1


source


You should also see reset ( sp_reset_connection

) between using the same connection in the pool; would not reset the isolation level? Have you tried to reproduce a serializable problem (like blocking blocking blocking)



0


source


This is a well-known design decision in SQL Server.

Also, using the new TransactionScope () is considered malicious (06/2010; avoid the default constructor)

0


source







All Articles