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 Mateus
.
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?