An uncommitted transaction updates a column; how can a different transaction see the old column value?

Question

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... — Justin Cave, May 14 '19 at 19:20
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. — John Eisbrener, May 14 '19 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, May 15 '19 at 09:23
You may try readpast as lock hint to get last committed record however RCSI is the best possible option. — Learning_DBAdmin, May 15 '19 at 10:37
So basically the only option available is changing the DB settings globally, no way to do it via query level? — Mateus Viccari, May 16 '19 at 10:17

score 4 · Accepted Answer · answered May 14 '19 at 20:32

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.

An uncommitted transaction updates a column; how can a different transaction see the old column value?

1 Answers1