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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ferrethouse
Constraint Violating Yak Guru

352 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
52326 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

352 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  
 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.37 seconds. Powered By: Snitz Forums 2000