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，我使用了 this class 由@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屋！