SELECT blocks UPDATE in Snapshot Isolation

Question

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. These are the queries at issue:

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?

Answer 1

if @@trancount <> 0 then that SELECT will run with the current transaction isolation level. Which (unless it already happens to be SNAPSHOT) will require S locks for reading data.

If you want all SELECTS using the default READ COMMITTED isolation level to use row versioning instead of S locks, set READ COMMITTED SNAPSHOT on the database.