SQL Server : is there any performance penalty for

Question

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.

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.

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.

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

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

This is the AdoNetUnitOfWork class:

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;
        }
    }
}

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;
}

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?

Answer 1

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.

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.