SQL Server - How to achieve READCOMMITED and NOLOCK at the same time?

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?

What you're looking for is an optimistic isolation level, like Snapshot Isolation, or Read Committed Snapshot Isolation.

Code example:

USE Crap;

CREATE TABLE dbo.users (id INT, username NVARCHAR(40));

INSERT dbo.users ( id, username )
VALUES ( 1, N'Jimbo' )

/*To turn on Snapshot*/
ALTER DATABASE Crap SET ALLOW_SNAPSHOT_ISOLATION ON;

/*To turn on RCSI*/
ALTER DATABASE Crap SET READ_COMMITTED_SNAPSHOT ON;

UPDATE dbo.users 
SET username = 'Dimbo'
WHERE id = 1;

/*Snapshot needs this, RCSI doesn't*/
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT *
FROM dbo.users AS u
WHERE u.id = 1;

Things to be aware of:

• Row versioning uses space in tempdb (except SQL Server 2019 when Accelerated Database Recovery is configured - versions are stored with the user database, either in-row or in the Persisted Version Store)

• There can be race conditions where you depend on locking for queueing

Differences

One important difference between Snapshot Isolation and RCSI is inside transactions:

-
Under Snapshot Isolation, BEGIN TRAN marks the point when all queries inside the transaction will read from the version store.

-
Under RCSI, each statement after BEGIN TRAN will read the version store as of when the statement executes.

Another difference is that Snapshot Isolation can be applied to modification queries, where RCSI can't. More precisely, SI detects write conflicts and rolls one of the conflicting transactions back automatically. Updates under RCSI do not use row versions when locating data to update, but this only applies to the target table. Other tables in the same delete or update statement, including additional references to the target table, will continue to use row versions.

StackExchange Database Administrators Q#246453, answer score: 14