Using TransactionScope to perform select

Question

I'm seeing alot of people at my company doing this:

var transactionOptions = new System.Transactions.TransactionOptions();
transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;

using (var transactionScope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required, transactionOptions))
{
    try
    {
        using (DefaultContext ctx = new DefaultContext())
        {
            return ctx.Item.Where(x => x.State == 1);
        }
    }
    catch (Exception err)
    {
        throw err;
    }
    finally
    {
        transactionScope.Complete();
    }
}

Do I really need to open a transaction to a Select and call Complete() method after all ?I thought that it were just for data modification...

Some one can explain to me if it is right? Is it a good or bad pratice? Is it not necessary ?

thanks

It seems completely unnecessary. It may run quickly, but I would not incur the overhead. I only use scoped txns with inserts and updates. I see that this does set the `IsolationLevel` to `ReadUncommitted`. They may be doing this for the NOLOCK effect that provides. — R. Richards, Jul 18 '16 at 13:48
`ReadUncommitted` allows dirty reads, meaning you may see data from uncommitted queries in your reads, (see https://stackoverflow.com/questions/2471055/why-use-a-read-uncommitted-isolation-level), which *isn't* the default, but that `catch/throw` construct is completely unnecessary. — Preston Guillot, Jul 18 '16 at 13:55
Did you ask anyone at your company why they do that? ;) I agree with @R. Richards, it looks to me like the only reason to do this is to use the Read Uncommitted isolation level. This suggests that there is (or at some point there was) some issue with locking that is/was hindering the application so ReadUncommitted was used to avoid taking read locks. IMO, this is a fairly big data access change (to use across the board) so I would hope someone would know the full story. Or perhaps one person did this once and now it's a copy/paste issue. — Randy Levy, Jul 18 '16 at 14:49

score 2 · Accepted Answer · answered Jul 18 '16 at 13:44

So what a transaction scope would be useful for is if you're doing a lot of things with the database. So say you are modifying 3 tables. You modify table 1, table 2, but then when you try to modify table 3 it fails. You don't want the changes made in tables 1 and 2 to keep if the 3rd failed. This is where you wrap it in a transaction scope because if there is an error, all of those changes get rolled back( or rather don't take) and you don't have to worry about it.

You can read more here.

Wrapping a query in a transaction scope just to get data however... I don't see any benefit. You are correct, there is no data manipulation so there really doesn't have to be a transaction scope. What I assume is some of your coworkers just saw someone else use one and the decided it would be a good idea if they did too.

Another oddity is the fact that they're completing the transaction scope in a finally. If there was an error thrown, you probably wouldn't want to complete a transaction.

You right. They just copy and past the transaction for every code block in the project... "They had no time to ask why". Your answer were very helpful, man. Thanks! — Gabriel Scavassa, Jul 18 '16 at 14:13

Using TransactionScope to perform select

1 Answers1