SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Write-Write Blocking and SP Transactions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ferrethouse
Constraint Violating Yak Guru

340 Posts

Posted - 05/06/2013 :  21:43:00  Show Profile  Reply with Quote
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?

Edited by - ferrethouse on 05/06/2013 22:06:21

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/07/2013 :  00:44:56  Show Profile  Reply with Quote
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

340 Posts

Posted - 05/07/2013 :  01:29:59  Show Profile  Reply with Quote
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.

Edited by - ferrethouse on 05/07/2013 01:32:49
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000