Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Write-Write Blocking and SP Transactions

Author  Topic 

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2013-05-06 : 21:43:00
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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-07 : 00:44:56
hmm...storing entire filter string a table column value? This is not a good method. WHy do you've design a system in this way? this will call for lots of dynamic sql

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2013-05-07 : 01:29:59
quote:
Originally posted by visakh16

hmm...storing entire filter string a table column value? This is not a good method. WHy do you've design a system in this way? this will call for lots of dynamic sql


"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."

Incidentally, because it is okay that this process occurs asynchronously I decided to feed it through service broker which seems to queue up these calls nicely reducing the amount of blocking.
Go to Top of Page
   

- Advertisement -