1

I understand that starting a transaction under the SNAPSHOT isolation level means that any subsequent SELECT statement will provide data as it was when the transaction was started.

The following statement, for example, will return data from the table without blocking writers as well as ignoring subsequent changes:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRANSATION

SELECT  col1, col2, [something here to tell me the row is out of date]
FROM    dbo.SomeTable st
WHERE   (st.SomeForeignKey = @SomeValue)

COMMIT TRANSATION

Is there a performant way of knowing whether a record retrieved from this SELECT statement has been modified since the transaction was started (without running the entire thing again)?

3
  • mmm i'm not sure of what are you asking. The concept of 'isolation' means that the query has to live in its snapshot, free from other query activities, so unaware of changes...
    – MBuschi
    Jan 29, 2021 at 8:32
  • Combination of a rowversion column and compare value to a self-joined table using nolock maybe? Jan 31, 2021 at 4:43
  • I was considering something NOLOCK based but was mindful of the side-effects that can cause with allocation scans, page splits, index rebuilds etc. Perhaps being combined with FORCESEEK, ROWLOCK will overcome those...
    – Dan Def
    Feb 2, 2021 at 12:51

1 Answer 1

1

As far as I know there is no way to do this, you can see how many rows are in tempdb using a query like the below

SELECT  version_ghost_record_count
  FROM  sys.dm_db_index_physical_stats
    (
        DB_ID(),
        OBJECT_ID(N'dbo.sometable', N'U'),
        1,
        NULL,
        N'DETAILED'
    )
  WHERE index_level = 0

but this will only tell you at a table level (or partition if you are using those) & you want to get down to row level, more info here :-

https://www.red-gate.com/simple-talk/sql/performance/read-committed-snapshot-isolation-high-version_ghost_record_count/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.