I've been getting quite a bit of write-write blocking with some code in one of my stored procs doing the blocking. Here is the code in question...WHILE (SELECT COUNT(*) FROM #SmartDepartments) > 0 BEGIN Select Top 1 @DepartmentID = DepartmentID From #SmartDepartments DECLARE @filters1 nvarchar(MAX) SELECT @filters1 = SmartDepartmentFlatFilters.SmartRules FROM SmartDepartmentFlatFilters WHERE SmartDepartmentFlatFilters.DepartmentID = @DepartmentID SET @SQL = 'DELETE FROM StudentDeparments WHERE DepartmentID = ' + CONVERT(NVARCHAR,@DepartmentID) + ' AND StudentID NOT IN (SELECT StudentsID FROM Students WHERE ClientID = ' + CONVERT(VARCHAR,@ClientID) + ' AND ' + @filters1 +')' + '; INSERT INTO StudentDeparments(DepartmentID,StudentID) SELECT ' + CONVERT(NVARCHAR,@DepartmentID)+ ',StudentsID FROM Students WHERE ClientID = ' + CONVERT(VARCHAR,@ClientID) + ' AND (' + @filters1 + ')' + 'AND StudentsID NOT IN (Select StudentID FROM STudentDeparments where DepartmentID =' + CONVERT(NVARCHAR,@DepartmentID) + ') ' EXEC sp_executesql @SQL; DELETE #SmartDepartments WHERE #SmartDepartments.DepartmentID = @DepartmentID END
Each loop general takes one second to complete and there can be up to 30 loops. I know the code is crap and it IS being rewritten as part of a larger rewrite but that won't be live for another 4 months so I need to address this locking in the meantime. If I begin and commit a transaction every pass through the loop will that allow other competing updates to execute in between so they won't be waiting for up to 30 seconds?