If I have a User table:
id | name | age 1 | Mateus | 27
The first transaction executes an update, and leaves the transaction open, without committing or rolling back:
update User set name = 'John' where id = 1;
Meanwhile, the second transaction executes a select:
select * from User where id = 1;
This command will wait until the first transaction releases the lock, either by commit or rollback, unless the second transaction uses a table hint
with(nolock), like so:
select * from User with(nolock) where id = 1;
That will return the record without locking the transaction, but it will return the uncommitted value
John instead of the original
From what I know, there are only two ways to return a locked record without locking the current transaction, one can use
with(nolock) that will return the record but with the uncommitted value, and
with(readpast) that will just not return the record.
Is there a way I can return the record, without locking the table, and returning its "old" values?