Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

A blocking problem Expand / Collapse
Author
Message
Posted Wednesday, December 14, 2005 9:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 17, 2014 11:11 AM
Points: 7, Visits: 284

In my production environment, I have a stored procedure which was called by an application several time. This stored procedure was used to return a new sequence number for the 'key' passed as an argument. For example, if appliication needs to add a new customer record, this stored procedure was called and returned a new cust_num for the new record. The 'last_sequence_oid' table has the column keyname and the column storing the last sequence number used for the 'keyname'.

Recently, we experienced a blocking problem with the 'last_sequence_oid' table. By examing the sysprocesses table, I found that the blocker was the apps calling the stored procedure. The blocker sysprocesses record has shown that the blocked column was 0, waittime was 0, waittype was 0x000, and the status was 'AWATING FOR COMMAND'.

If I killed the application process, all the login sessions blocked were able to continue.

We could not reproduce the blocking problem in test environment. And blocking problem occasionally occurred in production environment. SQL Profiler trace did not show errors or exceptions.

Can anyone share his experience with me to investigate this problem.

 

Regards,

-- Peter Lo

 

 

 




Post #244355
Posted Friday, December 16, 2005 3:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 7:04 AM
Points: 278, Visits: 304
There is an issue (bug) with "SELF BLOCKING" which arose with either SP3 or SP4 of SQL2K.  Search here (or sqlteam.com) for info on same.  A patch is available.  Paul Randal (of Microsoft SQL Dev Team) would have posted the info.


Post #244779
Posted Friday, December 16, 2005 8:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 17, 2014 11:11 AM
Points: 7, Visits: 284

Thanks

Do you know the bug #? and where I can obtain the patch

 

-- Peter Lo




Post #244882
Posted Friday, December 16, 2005 9:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 28, 2012 6:13 AM
Points: 380, Visits: 52
search at http://support.microsoft.com



Michelle
Post #244886
Posted Friday, December 16, 2005 9:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
There are two issues around blocking in SP4. One's a real bug, the other isn't.

The first one is a bug in the lock manager that manifests during DBCC INDEXDEFRAG. The lock manager erroneously holds onto NULL mode locks when INDEXDEFRAG drops its page locks, eventually resulting in escalation to a table lock. This will block other queries. There is a hotfix available. Details can be found at http://support.microsoft.com/default.aspx/kb/907250


The other issue is an enhancement to the blocking info displayed. It may look like a SPID is blocking itself but this is in fact normal during synchronous IO. More info at http://support.microsoft.com/default.aspx/kb/906344

AFAIK there are no issues that would cause the problem you describe - recommend you call PSS to assist you.

Thanks



Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #244909
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse