Select blocks update in snapshot isolation

by Frederik Vanderhaegen   Last Updated May 02, 2018 19:06 PM

We have a database running on SQL Server 2016 SP1 Enterprise and on which Allow Snapshot Isolation is enabled. In this scenario readers do not block writers and writers do not block readers.

Today we saw that a SELECT-query was blocking an UPDATE-query. This are de queries:

First query:

IF @@TRANCOUNT = 0 SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT TOP 10 Field1, Field2, Field3 FROM [dbo].[TABLE1] 
WHERE ([Field1] = @SV1 AND (1=1))
ORDER BY A.[Field1] ASC,A.[Field2] ASC
OPTION (KEEP PLAN, KEEPFIXED PLAN, LOOP JOIN)

Second query:

UPDATE [dbo].[Table1] SET [Field4]= @V128, [Field5]= @V140, [Field6]= @V141 WHERE [Field3] = @ID0

If a understand versioning correctly, a SELECT-query do not use any locks because it works on a snapshot of the data (taken at the start of the transaction). How is it possible that the SELECT-query blocks the UPDATE?



Related Questions