December 14, 2005 at 9:38 pm
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
December 16, 2005 at 3:33 am
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.
December 16, 2005 at 8:50 am
Thanks
Do you know the bug #? and where I can obtain the patch
-- Peter Lo
December 16, 2005 at 9:05 am
search at http://support.microsoft.com
Michelle
December 16, 2005 at 9:58 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply