SQL Server : is there any performance penalty for wrapping a SELECT query in a transaction?
<p>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.</p>

<p>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.</p>

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

<p>To somehow 'simulate' a UoW, I used <a href="http://blog.gauffin.org/2014/01/unit-of-work-sample-implementation-for-ado-net/" rel="nofollow">this class</a> provided by @jgauffin, but what's stopping me from using that class is that every time you create a new instance of that class (<code>AdoNetUnitOfWork</code>) you're automatically beginning a transaction and there a lot of cases where you only need to read data.</p>

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

<blockquote>
  <p>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</p>
</blockquote>

<p>This is the <code>AdoNetUnitOfWork</code> class:</p>

<pre><code>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 &amp;&amp; _ownsConnection)
        {
            _connection.Close();
            _connection = null;
        }
    }
}
</code></pre>

<p>And this is how I want to use the UoW in my repositories:</p>

<pre><code>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;
}
</code></pre>

<p>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?</p>