Is there a performance difference in committing and rolling back a read-only transaction?

Question

I open a (repeatable read) transaction (BEGIN TRAN) to do some work on certain records. First thing I do is checking if the data I need to change is in the database. In some cases there will be and then I proceed to my changes. But in some cases there will be nothing to do. In this case I either COMMIT TRAN or ROLLBACK TRAN and return from the stored procedure. At this time no changes were done to the data yet, so the effect of commit and rollback is the same.

Are there any consideration I should be aware about to chose between commit and rollback? Is there different performance cost? Other considerations?

Answer 1

Having run this through a debugging session (to refresh my failing memory):

• Rollback does more checks than a commit, but it shouldn't result in additional work or have a noticeable affect on performance in the situation you describe.
• The read-write transaction doesn't truly begin unless and until a data modification is made.

You can see much of this using DMVs, for example:

-- Temporary procedure to show the state of the transaction
CREATE PROCEDURE #TranState
    @Comment varchar(100)
AS
BEGIN
    SELECT 
        @Comment AS Comment,
        DTCT.transaction_id,
        database_name =
            CASE DTDT.database_id
                WHEN 32767 THEN N'resource'
                ELSE DB_NAME(DTDT.database_id)
            END,
        tran_begin_time = DTDT.database_transaction_begin_time,
        tran_type =
            CASE DTDT.database_transaction_type
                WHEN 1 THEN 'read/write'
                WHEN 2 THEN 'read only'
                WHEN 3 THEN 'system'
            END,
        tran_state =
            CASE DTDT.database_transaction_state
                WHEN 1 THEN 'The transaction has not been initialized.'
                WHEN 3 THEN 'The transaction has been initialized but has not generated any log records.'
                WHEN 4 THEN 'The transaction has generated log records.'
                WHEN 5 THEN ' The transaction has been prepared.'
                WHEN 10 THEN 'The transaction has been committed.'
                WHEN 11 THEN 'The transaction has been rolled back.'
                WHEN 12 THEN 'The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted.'
            END
    FROM sys.dm_tran_current_transaction AS DTCT
    JOIN sys.dm_tran_database_transactions AS DTDT
        ON DTDT.transaction_id = DTCT.transaction_id;
END;

Testing on AdventureWorks:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

EXECUTE dbo.#TranState @Comment = 'After Begin Tran';

SELECT TOP (1)
    P.Name
FROM Production.Product AS P
ORDER BY 
    P.Name;

EXECUTE dbo.#TranState @Comment = 'After Select';

UPDATE Production.Product
SET Name = N'New Blade'
WHERE Name = N'Blade';

EXECUTE dbo.#TranState @Comment = 'After Update';

-- Or Commit
ROLLBACK TRANSACTION;

EXECUTE dbo.#TranState @Comment = 'After Tran';

Output:

From a purely practical point of view (as Aaron noted in a comment), it is probably safer to issue a rollback to guarantee no changes are made, in case the code is modified in future. So, it's all about intent: no changes = rollback.

In passing, REPEATABLE READ is an unusual isolation level to choose; it does not always work how people would intuitively expect. Depending on your requirements, you might find SNAPSHOT isolation is a better fit.