SQL Server : is there any performance penalty for wrapping a SELECT query in a transaction?

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.

  • In MVC4, how do I upload a file (an image) to SQL Server that's part of my domain model?
  • Procedure or function expects parameter which was not supplied - Parameter is in stored procedure(I think)
  • The multi-part identifier “usr.USERID” could not be bound
  • Create LocalDB for testing from Visual Studio SQL project
  • add SQL Server index but how to recompile only affected stored procedures?
  • Add a column to existing table and uniquely number them
  • 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;
            _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 = null;
        public void Dispose()
            if (_transaction != null)
                _transaction = null;
            if (_connection != null && _ownsConnection)
                _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?

  • Use SqlConnection.GetSchema to get Tables Only (No Views)
  • A connection was successfully established with the server, but then an error occurred during the pre-login handshake
  • How to retrieve what was inserted by one out of two insert queries
  • How do I pass a DBNull value to a parameterized SELECT statement?
  • Passing a boolean type into a bit parameter type in C# and MS SQL Server
  • How can I detect condition that causes exception before it happens?
  • One Solution collect form web for “SQL Server : is there any performance penalty for wrapping a SELECT query in a transaction?”

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.