SQL statement causes itself to be blocked

[jisaacson]

When I run the sql statement shown below,and then look at the current activity - locks/process id in enterprise manager, I find that the statement blocks itself.

INSERT INTO TempRptisaacson (YB_MKTCode, YB_ADCode, YB_STCode, YB_MediaType, YB_ADName, YB_AGCode, YB_AGName, YB_INCode, YB_INSubCode)
SELECT REV_MktCode, REV_ADCode, REV_STCode, REVMediaType, Min(ADName), REV_AGCode, Min(AGName), Min(AD_INCode), Min(AD_INSubCode)
FROM Revenue WITH (NOLOCK) INNER JOIN Advertisers WITH (NOLOCK) on REV_ADCode = ADCode INNER JOIN Agencies WITH (NOLOCK) on REV_AGCode = AGCode
WHERE REV_MktCode = 169 AND AD_INCODE <> 40 AND REV_Period BETWEEN '2005/01' AND '2006/07'
GROUP BY REV_MktCode, REV_ADCode, REV_STCode, REVMediaType, REV_AGCode
ORDER BY REV_ADCode, REV_STCode, REV_AGCode


Is this normal? Is there something I can do to prevent it from blocking? This does cause a problem when two or more people are running the job at the same time.

The revenue table has over 17 million rows of data.
The advertiser table has a little over a million rows.
The agency table has about 235,000 rows.

Enterprise manager current activity shows Process ID 54 5 times with context ids from 0 to 4. Context ID 4 shows it is blocked by Process ID 54, and is sleeping. Context id 3 shows it is "runnable". Context id's 0,1,and 2 are also sleeping.

Under Locks/Process ID, spid 54 shows (Blocked by 54)
Then it shows spid 54 as (Blocking).

I am running under Microsoft SQL Server 2000 8:00.2039
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

 

[SQLDenis]

That is SP4 behaviour, before SP4 you would never see a process that blocked itself

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[mrdenny]

What Denis didn't say is that when a SPID is blocking it self, it means that the process is waiting for the disk to respond. (It's actually more complex than that, but the basic issue is that the query is waiting for the disk to respond to a request.)

This issue has already existed, however it didn't show it self. This query blocking it self was first shown to us in SQL 2000 SP4.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[jisaacson]

Thanks for the info. I appreciate the quick response

No wonder I never saw this before.
However, is there something I can do with the sql statement to avoid this? It does cause problems with multiple users