Apparent DB Engine Bug In SQL Server 2005

SQL Server 2005 SP2 (build 3054)

Consider the following scenario:

- A complex multi-statement table valued function is created. Let's call
it dbo.tfFunc(@Param1, @Param2)
- A SELECT statement is executed, that calls the above function twice,
each time with a different set of parameters. In pseudocode:

SELECT <column list>
FROM dbo.tfFunc(1, 2) AS f1
<some JOIN operatordbo.tfFunc(3, 4) AS f2
ON f1.col = f2.col
INNER JOIN dbo.Table1 AS t1
ON ...
etc.

The exact statement is probably irrelevant, as long as the same table-
valued function is called twice (I have observed the issue in two very
different statements calling the same function). The statement is
executed in a SNAPSHOT isolation level transaction, although this may
also be irrelevant.

- The statement continues executing for a long time. If sp_who2 is run at
that time, the following row is returned for the statement connection
(only relevant columns are shown):

SPIDStatusBlkByCommandCPUTimeDiskIOLastBatch
63 SUSPENDED63SELECT2928268308/31 18:17:37

The statement appears to be blocked by itself. If sp_lock is run at that
time, the following rows are returned:

spiddbidObjIdIndIdTypeResourceModeStatus
63213166246410TABSch-SGRANT
63213166246410TABSch-MWAIT

It appears that SQL Server waits indefinitely trying to obtain a schema-
modification lock on a resource which already has a schema-stability lock
placed on it by the same connection.

The following is pure speculation, but it seems reasonable to assume that
the server has materialized the result of the first call to the function
using a temporary table in tempdb, and is trying to materialize the
result of the second call using the same temporary table (same ObjId in
sp_lock results).

I do not know why this does not cause a deadlock error.

Unfortunately, I do not have a simple repro script for this. The actual
code is rather complex. While I can devise a workaround, this does look
like a bug. I am posting it here before submitting a bug on Connect, in
case anyone can shed some light. Thanks.

--
remove a 9 to reply by email