Read Committed Data Without Waiting in SQL Server
sql-server
Solution
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.
Problem
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?