将TransactionScope与未提交的读取一起使用-是否需要在SQL中使用(nolock)? [英] Use of TransactionScope with read uncommitted - is with (nolock) in SQL necessary?

问题描述

我正在使用FluentNHibernate，并且有记录列表，映射到SQL Server 2008视图.对我来说，脏读是可以的，不锁定表是优先事项.

I am using FluentNHibernate, and I have a list of records, mapped to an SQL Server 2008 view. Dirty reads are OK with me, not locking the tables is a priority.

视图中的SQL查询没有任何(nolock)，但是，我正在使用以下方法...

The SQL Query inside the view, does not have any with (nolock), however, I am using the following approach...

using (var txScope = new TransactionScope(TransactionScopeOption.Suppress, new TransactionOptions() { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
{
   ... The reading of records from the view is done here, through Fluent NHibernate...
}

是否在应用程序层将隔离级别设置为读取未提交，并应用(nolock)应用于在该上下文中生成的查询?

Does setting the isolation level at application layer to read uncommitted, apply with (nolock) to the queries which are generated within that context?

推荐答案

简短答案:否

仅定义TransactionScope并不定义将在事务内调用任何读取或写入.

Just defining the TransactionScope does not define that any read or write will be invoked within a transaction.

要在事务中执行某些操作，您仍然必须打开并提交事务！

To run something within a transaction, you still have to open and commit a transaction!

Timeout和IsolationLevel的TransactionScope的TransactionOptions仅定义范围内创建的任何事务的默认值，而无需显式设置这些选项.实际上，TransactionScope确实创建了一个Transaction，但是如果不打开新的Transaction，它将不会处于活动状态.在内部，这会做一些复杂的事情，克隆事务等...所以让我们忽略它...

The TransactionOptions of the TransactionScope for Timeout and IsolationLevel just define the defaults for any transaction created within the scope without those options explicitly set. Actually the TransactionScope does create a Transaction but it will not be active without opening a new Transaction. Internally this will do some complex stuff, cloning the transaction etc... so lets ignore this...

如果没有事务，则无法定义隔离级别，任何select语句都将使用IsolationLevel.ReadCommitted运行，因为这是SQL Server的默认设置.

Without a transaction you cannot define the isolation level, any select statement will be run with IsolationLevel.ReadCommitted because this is the default of SQL Server.

您还可以查询session.Transaction.IsActive来查看会话中当前是否有交易！

You can also query session.Transaction.IsActive to see if a transaction is currently active for the session!

让我们看一下下面的代码，我添加一些注释以使其更加清晰

Lets have a look at the following code, I put some comments to make it a little bit more clear

using (var scope = new TransactionScope(TransactionScopeOption.Required,
                    new TransactionOptions()
                    {
                        IsolationLevel = IsolationLevel.ReadUncommitted
                    }))
{

    using (var session = sessionFactory.OpenSession())
    {
        // outside any transaction...
        var x = session.Transaction.IsActive; // false;

        // read will be done with SQL Server default (ReadCommited)
        var pp = session.Query<Page>().Where(p => p.Photos.Count() > 1).ToList();

        using (var transaction = session.BeginTransaction())
        {
            // will use ReadUncommitted according to the scope
            var y = session.Transaction.IsActive; // true;

            var p1 = session.Get<Page>(1);

            transaction.Commit();
        }
        using (var transaction = session.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
        {
            // will use ReadCommitted according to the transaction initialization
            var y = session.Transaction.IsActive; // true;

            var p1 = session.Get<Page>(1);

            transaction.Commit();
        }

        scope.Complete();
    }
}

您还可以使用SQL Server Profiler观察SQL Server对这些设置的反应.

You can also watch how SQL Server reacts to those settings by using the SQL Server Profiler.

只需创建一个新的跟踪并注意Audit Login事件，该事件的文本将包括隔离级别，您可以看到每次创建事务时它实际上都会执行Audit Login，例如

Just create a new Trace and watch out for the Audit Login event, the text of the event will include the isolation level and you can see that it actually does a Audit Login each time a transaction is created, for example

 set transaction isolation level read uncommitted

-

如果其中任何信息可能有误，请纠正我，我是自己一个人弄清楚的，因此可能会出现一些失败的可能性;)

这篇关于将TransactionScope与未提交的读取一起使用-是否需要在SQL中使用(nolock)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！