Sample Header Ad - 728x90

SELECT blocks UPDATE in Snapshot Isolation

1 vote
1 answer
727 views
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?
Asked by Frederik Vanderhaegen (2122 rep)
May 2, 2018, 06:59 PM
Last activity: May 2, 2018, 07:45 PM