Let's assume the following case.

There is a students table:

id | name
1  | Mateus

A "User A" executes the following statement without committing the transaction:

update students set name = 'Gustavo' where id = 1

Then a "User B" executes the following query:

select * from students with (nolock) where id = 1

User B will get the name Gustavo, that's the expected behavior. The DB will return the uncommitted value because of the with(nolock) instruction.

Is there a way to get the old name, Mateus (the previously committed value), even when there's an uncommitted transaction?

  • Do you mean to have a with (nolock) in your select? It sounds from the text hat you do but the actual query does not... Commented May 14, 2019 at 19:20
  • 7
    It sounds like you may be thinking of Optimistic Isolation Levels such as SNAPSHOT or READ COMMITTED SNAPSHOT isolation levels where a read is not blocked by a prior uncompleted transaction. Commented May 14, 2019 at 19:22
  • I edited your question to make it clearer and also more presentable (although I'll agree the latter is often subjective). Please feel free to edit further as you see fit.
    – Andriy M
    Commented May 15, 2019 at 9:23
  • You may try readpast as lock hint to get last committed record however RCSI is the best possible option. Commented May 15, 2019 at 10:37
  • So basically the only option available is changing the DB settings globally, no way to do it via query level? Commented May 16, 2019 at 10:17

1 Answer 1


You can use Read Committed Snapshot Isolation (RCSI) on your database, which uses space in TempDB to keep track of committed versions of the data.

Be aware that RCSI adds 14 bytes to every versioned row, so you might see some additional page splits in the database. It also requires that TempDB's space and I/O be monitored to make sure it's not slowing things down.

Read more by Paul White and in this question.

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.