Sql – System.Transaction implicit transaction messing with the other connections

msdtcnetsql servertransactions

I'm trying to use System.Transaction.TransactionScope to create a transaction to call a few stored procedures but it doesn't seem to clean up after itself. Once the transaction is finished (commited or not and the transaction scope object is disposed) subsequent connections to the database open up with the read commit level of serializable instead of read commited like they normally would.

I'm opening and closing a connection for each call (well closing and returning to a pool of connections like normal in .NET), am I missing some way to explicitly reset the connection when I'm 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 looks like this:

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

                scope.Complete();

                return returnCode;
            }

which I guess is the normal way to do it. But then if I look in sqlserver profiler I can see connections being opened with

set transaction isolation level serializable

which is messing with subsequent non-transaction related database activity and also is apparently not as fast. I can get around this by setting a transaction option to explicity do the transaction with ReadCommited but this is not the ideal behaviour for this operation in my opinion.

I've also tried explicitly creating a Commitabletransaction object, creating explict new transactions instead of using the ambient one and still no luck.

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

Best Answer

Use TransactionOptions.IsolationLevel

By default, it's serializable

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;
}

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

C# Interfaces. Implicit implementation versus Explicit implementation

Implicit is when you define your interface via a member on your class. Explicit is when you define methods within your class on the interface. I know that sounds confusing but here is what I mean: IList.CopyTo would be implicitly implemented as:

public void CopyTo(Array array, int index)
{
    throw new NotImplementedException();
}

and explicitly as:

void ICollection.CopyTo(Array array, int index)
{
    throw new NotImplementedException();
}

The difference is that implicit implementation allows you to access the interface through the class you created by casting the interface as that class and as the interface itself. Explicit implementation allows you to access the interface only by casting it as the interface itself.

MyClass myClass = new MyClass(); // Declared as concrete class
myclass.CopyTo //invalid with explicit
((IList)myClass).CopyTo //valid with explicit.

I use explicit primarily to keep the implementation clean, or when I need two implementations. Regardless, I rarely use it.

I am sure there are more reasons to use/not use explicit that others will post.

See the next post in this thread for excellent reasoning behind each.

Best Answer

Related Solutions

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

Example:

Notes:

C# Interfaces. Implicit implementation versus Explicit implementation

Related Topic