avoiding blocking 1

[azwaan]

hi..

i have a periodic job running on a table. the job script includes a transaction. there is a separate .NET application accessing the table for data reads. due to the very large frequency of data reads, i need to avoid blocking by the job. how can i achieve this?

the following is a sample script.. which i use to test this scenario.. i have noticed that despite using the various isolation levels, the .NET application is blocked from reading data and times out.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;
declare @count as int;
BEGIN TRANsaction;
SET @count=1;
WHILE @count<100
BEGIN
Update MasterVersion set VersionID= VersionID +1 ;
WAITFOR DELAY '00:01';
END
COMMIT transaction;
GO


any suggestions?

 

[bboffin]

Do the SELECT statements used by the .NET application include the hint WITH (NOLOCK)?

This might help.

Bob Boffin

 

[azwaan]

Thanks , i also found out if you are actually doing the reads using a SP. you could use the following to avoid a block.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
Select * from MasterVersion

however u need to enable SnapShots at the DB level prior to this using

ALTER DATABASE DbName SET ALLOW_SNAPSHOT_ISOLATION ON