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 AS Comment,
WHEN 32767 THEN N'resource'
tran_begin_time = DTDT.database_transaction_begin_time,
WHEN 1 THEN 'read/write'
WHEN 2 THEN 'read only'
WHEN 3 THEN 'system'
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.'
FROM sys.dm_tran_current_transaction AS DTCT
JOIN sys.dm_tran_database_transactions AS DTDT
ON DTDT.transaction_id = DTCT.transaction_id;
Testing on AdventureWorks:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
EXECUTE dbo.#TranState @Comment = 'After Begin Tran';
SELECT TOP (1)
FROM Production.Product AS P
EXECUTE dbo.#TranState @Comment = 'After Select';
SET Name = N'New Blade'
WHERE Name = N'Blade';
EXECUTE dbo.#TranState @Comment = 'After Update';
-- Or Commit
EXECUTE dbo.#TranState @Comment = 'After Tran';
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.
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.