Read Committed Data Without Waiting in SQL Server

Question

Is it possible in SQL Server to read committed data with NoWait when the row is already locked for update?

I created a table named "Tbl"

CREATE TABLE [dbo].[Tbl](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Detail] [varchar](50) NULL)

Inserted Few Rows.

insert into Tbl(Detail) values ('1D')
insert into Tbl(Detail) values ('2D')
insert into Tbl(Detail) values ('3D')
insert into Tbl(Detail) values ('4D')

Now, I have set implicit transactions to true in SSMS. (Tools -> Options -> SQL Server -> ANSI.

Then, In one session(Opened New Query Window) executed update statement, Which should put an Exclusive Lock on the row.

UPDATE dbo.tbl  SET Detail = '9D' where Id = 1

Now, In another session I want to select committed rows. If I execute,

SELECT * FROM dbo.tbl with(READCOMMITTED,NOWAIT) , It throws exception(Lock Request Timeout Period Exceed)

It's probably trying to apply Shared Lock on the row where another session already applied Exclusive Lock. Thus getting exception.

Now, This is a common scenario where One Transaction is taking long time to update huge rows. I should be able to see committed data in another session without waiting.

What type of HINT is required to achieve this in SQL Server?

Answer 1

This is only available in form of SNAPSHOT isolation (or read-committed-snapshot mode). You need to enable it on the database first.

Running a transaction under SNAPSHOT isolation means that neither does it take locks nor does it wait for them. The tran sees a point-in-time snapshot of the database.