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
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
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?