Posted by Dimitri Furman on 08/31/07 23:03

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 operator> dbo.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):

SPID Status BlkBy Command CPUTime DiskIO LastBatch
63 SUSPENDED 63 SELECT 29282 683 08/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:

spid dbid ObjId IndId Type Resource Mode Status
63 2 1316624641 0 TAB Sch-S GRANT
63 2 1316624641 0 TAB Sch-M WAIT

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

[Back to original message]