SQL Server：在事务中包装SELECT查询是否会降低性能？ [英] SQL Server : is there any performance penalty for wrapping a SELECT query in a transaction?

问题描述

作为学习练习，在尝试使用任何ORM（例如EF）之前，我想使用ADO.NET和存储过程来构建个人项目。

As learning exercise and before trying to use any ORM (like EF) I want to build a personal project using ADO.NET and stored procedures.

因为我不知道不想让我的代码随着时间的流逝而变得一团糟，我想使用诸如存储库和UoW模式之类的模式。

Because I don't want my code to become a mess over time, I want to use some patterns like the repository and UoW patterns.

我已经弄清楚了几乎所有内容，除了事务处理外。

I've got almost everything figured it out, except for the transaction handling.

为了以某种方式模拟 UoW，我使用了，它是由@jgauffin提供的，但是阻止我使用该类的是，每次创建一个新的该类的实例（ AdoNetUnitOfWork ），您会自动开始事务，并且在很多情况下，您只需要读取数据即可。

To somehow 'simulate' a UoW, I used this class provided by @jgauffin, but what's stopping me from using that class is that every time you create a new instance of that class (AdoNetUnitOfWork) you're automatically beginning a transaction and there a lot of cases where you only need to read data.

在这方面，这是我在阅读过的一本SQL书籍中发现的：

In this regard this is what I found in one of the SQL books I've been reading:

事务中的SELECT语句可以在被引用的表上创建锁，从而可以阻止其他用户或会话执行工作或读取数据

Executing a SELECT statement within a transaction can create locks on the referenced tables, which can in turn block other users or sessions from performing work or reading data

这是 AdoNetUnitOfWork 类：

public class AdoNetUnitOfWork : IUnitOfWork
{
    public AdoNetUnitOfWork(IDbConnection connection, bool ownsConnection)
    {
        _connection = connection;
        _ownsConnection=ownsConnection;
        _transaction = connection.BeginTransaction();
    }

    public IDbCommand CreateCommand()
    {
        var command = _connection.CreateCommand();
        command.Transaction = _transaction;
        return command;
    }

    public void SaveChanges()
    {
        if (_transaction == null)
            throw new InvalidOperationException("Transaction have already been commited. Check your transaction handling.");

        _transaction.Commit();
        _transaction = null;
    }

    public void Dispose()
    {
        if (_transaction != null)
        {
            _transaction.Rollback();
            _transaction = null;
        }

        if (_connection != null && _ownsConnection)
        {
            _connection.Close();
            _connection = null;
        }
    }
}

这就是我想要的在我的存储库中使用UoW：

And this is how I want to use the UoW in my repositories:

public DomainTable Get(int id)
{
    DomainTable table;

    using (var commandTable = _unitOfWork.CreateCommand())
    {
        commandTable.CommandType = CommandType.StoredProcedure;
        //This stored procedure contains just a simple SELECT statement
        commandTable.CommandText = "up_DomainTable_GetById";

        commandTable.Parameters.Add(commandTable.CreateParameter("@pId", id));

        table = ToList(commandTable).FirstOrDefault();
    }

    return table;
}

我知道我可以稍微修改一下此代码，以便事务是可选的，但是由于我试图使该代码尽可能地独立于平台，并且据我所知，在诸如EF之类的其他持久性框架中，您不必手动管理事务，所以问题是，我是否会使用来创建某种瓶颈

I know I can tweak this code a bit so that the transaction would be optional, but since I trying to make this code as platform independent as possible and as far as I know in other persistence frameworks like EF you don't have to manage transactions manually, the question is, will I be creating some kind of bottleneck by using this class as it is, that is, with transactions always being created?

推荐答案

这一切都取决于交易隔离级别。使用默认的隔离级别（即读已提交），如果 SELECT 如果包装在事务中，则不会造成性能损失。如果尚未启动，SQL Server会在内部将语句包装在事务中，因此您的代码应具有几乎相同的行为。

It all depends on the transaction isolation level. Using the default isolation level (ie. read committed) then your SELECT should occur no performance penalty if is wrapped in a transaction. SQL Server internally wraps statements in a transaction anyway if one is not already started, so your code should behave almost identical.

但是，我必须问您为什么不使用已构建的语句-.Net TransactionScope ？这样，您的代码将与其他库和框架更好地交互，因为 TransactionScope 被普遍使用。如果您决定切换到该选项，我必须警告您，默认情况下， TransactionScope 使用SERIALIZABLE隔离级别，这会导致性能下降，请参见使用新的TransactionScope（）被认为有害。

However, I must ask you why not use the built-in .Net TransactionScope? This way your code will interact much better with other libraries and frameworks, since TransactionScope is universally used. If you do decide to switch to this I must warn you that, by default, TransactionScope uses SERIALIZABLE isolation level and this does result in performance penalties, see using new TransactionScope() Considered Harmful.

这篇关于SQL Server：在事务中包装SELECT查询是否会降低性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！