Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Self Blocks with SQL Server 2000 SP 4

 
Rajesh MadhanGopal
Ranch Hand
Posts: 70
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

Am using MS SQL Server 2000 SP4 and having few issues with Deadlocks happening in my Database. My Block Logs mention that the Blocker (sp_cursorclose) and the Blockee (my query)have the same process ID, this is reported as a bug with MS SQL Server and has been fixed with Service pack 4.

However the Fix says that it is for Process IDs less than 50 and shows a self block, my logs show a self block for process IDs greater than 50.

Can anyone confirm if this is still a bug with SQL Server?

Appreciate any pointers on this..
 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can't answer about the bug, but there are programmatic ways to avoid deadlocks. One way is that in transactions that modify more than one table, to always access the tables in a set order. This way, there's never a loop in the access of tables.
[ July 23, 2007: Message edited by: Scott Selikoff ]
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Rajesh MadhanGopal:
Hi all,

Am using MS SQL Server 2000 SP4 and having few issues with Deadlocks happening in my Database. My Block Logs mention that the Blocker (sp_cursorclose) and the Blockee (my query)have the same process ID, this is reported as a bug with MS SQL Server and has been fixed with Service pack 4.

However the Fix says that it is for Process IDs less than 50 and shows a self block, my logs show a self block for process IDs greater than 50.

Can anyone confirm if this is still a bug with SQL Server?

Appreciate any pointers on this..


These are almost certainly not deadlocks. SQL Server should recognise a deadlock and recover. It is much more likely these are blocks, and blocks are usually caused the client (a better database wouldn't suffere from this issue). The most common cause of client caused blocks from app. servers in my experience is mistakes in transaction handling or code being run in debug mode and hitting a break point. What is your application doing when the block occurs?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic