CODEWITHSUNDEEP
c#asp.netsql-server-2008linq-to-sqltransactionscope
Denis
Denis

Reputation: 3757

Assign properties inside or outside of TransactionScope?

I am using SQL Server 2008 and Linq-to-SQL.

a is object materialized from database using Linq-to-SQL. All its properties has Auto-Sync set to Always. GetXxxxx() is HTTP request to Webservice. It does not contain any nested transactions. Usually it takes not more than few seconds, but sometimes it can take up to 3-4-5-10 minutes.

So... Which one is preferred?

using (var t = new TransactionScope())
{
    a.Xxxxx = GetXxxxx();
    a.UpdatedOn = DateTime.UtcNow;
    database.SubmitChanges();
    a.Signature = CalculateSignature(a);
    database.SubmitChanges();
    t.Complete();
}

Or:

a.Xxxxx = GetXxxxx();
a.UpdatedOn = DateTime.UtcNow;
using (var t = new TransactionScope())
{
    database.SubmitChanges();
    a.Signature = CalculateSignature(a);
    database.SubmitChanges();
    t.Complete();
}

1st variant looks clearer. But what if GetXxxxx() will take 3-4-5-10 minutes? TransactionScope will block whole website in this case? So correct one is 2nd?

Upvotes: 1

Views: 441

Answers (2)

usr
usr

Reputation: 171246

A System.Transactions.Transaction starts on the first database call made while it is active. This means that both variants do the same this assuming that the two lines you moved do not call the database.

That makes this a matter of style. Pick what makes your intent the clearest.

Serializable isolation does not block the whole database. I consider this out of scope for the question but you can easily find information about this on the web.

Upvotes: 1

teo van kot
teo van kot

Reputation: 12491

Denise Skidmore says about problems with UpdatedOn field that you can faces when use 2ed approach.

But if you worry about blocking whole DB you can change IsolationLevel of TransactionScope. Default isolation level is Serializable

Volatile data can be read but not modified, and no new data can be added during the transaction.

So to avoid blocking you can change it to ReadCommitted

Volatile data cannot be read during the transaction, but can be modified.

Or even ReadUncommitted

Volatile data can be read and modified during the transaction.

You can do it like this:

var transactionOptions = new TransactionOptions();
transactionOptions.IsolationLevel = IsolationLevel.ReadCommitted;
using (var t = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
{
    a.Xxxxx = GetXxxxx();
    a.UpdatedOn = DateTime.UtcNow;
    database.SubmitChanges();
    a.Signature = CalculateSignature(a);
    database.SubmitChanges();
    t.Complete();
}

Upvotes: 1

Related Questions