sql-server add tag
mateus viccari (imported from SE)
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?
Top Answer
meme (imported from SE)
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*/
    /*To turn on RCSI*/
    UPDATE dbo.users 
    SET username = 'Dimbo'
    WHERE id = 1;
    /*Snapshot needs this, RCSI doesn't*/
    SELECT *
    FROM dbo.users AS u
    WHERE u.id = 1;

Things to be aware of:

 - Row versioning [uses space in tempdb][1] (except SQL Server 2019 when [Accelerated Database Recovery][2] is configured - versions are stored with the user database, either in-row or in the Persisted Version Store)
 - There can be [race conditions][3] where you depend on locking for queueing

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][4] 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.

  [1]: https://www.sqlshack.com/snapshot-isolation-in-sql-server/
  [2]: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-accelerated-database-recovery#adr-recovery-components
  [3]: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/optimistic-concurrency
  [4]: https://sqlperformance.com/2014/05/t-sql-queries/data-modifications-under-rcsi

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.