September 8, 2008 at 9:17 am
OK,
Here is the problem. I have a SPID that is blocking itself and presently in a state KILLED/ROLLBACK in sp_who2 active. When you try and kill this SPID it returns the message;
"SPID 120: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds"
When looking at this SPID in sys.sysprocesses I can see that the blocking SPID is itself. The last wait type is LCK_M_SCH_M with a wait type of 0x0002.
When looking at the locks by process i can see that in tempdb Owner ID 2063795945 has taken out a Sch-S lock on Object ID 958657192. In the same process there is Owner ID 2063803448 which is waiting to obtain a Sch-M lock on the same object (958657192). This lock request has a Request Status of WAIT.
This SPID executed a stored procedure which calls a user defined table value function that splits a comma delimited string into a table variable and returns a table with an integer Column type. The code for this can be seen at the foot of this post.
BOL states that Schema stability (Sch-S) locks are used when compiling queries. This lock does not block any transactional locks, but when the Schema stability (Sch-S) lock is used, the DDL operations cannot be performed on the table. Because the table variable definition cannot be changed after the initial DECLARE statement and you cannot drop a table variable WHY is it trying to obtain a Sch-M on that object?!
It is the table variable within this function that the process is trying to obtain the Sch-M lock.
With this in mind, I have a few of questions.
1. Is there any other way of removing this SPID without restarting the service, obviously the KILL statement is not going to work.
2. What does the Owner ID in Activity Monitor "Lock By Process" refer to? Is there any way i can trace this back further to establish what has happened
3. How can more than one Owner ID exist within the same process when in BOL it states that the owner id is “The owner ID associated with the process”
4. How can a process block itself on a table variable in tempdb?
Thanks in advance for any help you might be able to offer.
Phil Harbour
FYI
---------------------------------------------
dbcc inputbuffer (120,2)
RPC Event 0 dbo. ;1
---------------------------------------------
---------------------------------------------
sp_lock returns
spiddbidObjId IndIdTypeResource ModeStatus
12050 0DB SGRANT
12050 0MD4(1:0:0) Sch-SGRANT
120515598848240TAB Sch-SGRANT
12029586571920TAB Sch-SGRANT
12029586571920TAB Sch-MWAIT
12052226238360TAB Sch-SGRANT
120510532988620TAB Sch-SGRANT
12055864458590TAB Sch-SGRANT
120500MD1(d44f85c:0:0) Sch-SGRANT
12059252984060TAB Sch-SGRANT
12058745382490TAB Sch-SGRANT
Table Function
declare @separator char(1)
select @separator = ','
declare @separator_position int
declare @array_value varchar(1000)
set @array = @array + ','
while patindex('%,%' , @array) <> 0
begin
select @separator_position = patindex('%,%' , @array)
select @array_value = left(@array, @separator_position - 1)
insert @IntTable
select (cast(@array_value as int))
select @array = stuff(@array, 1, @separator_position, '')
end
September 8, 2008 at 11:35 am
While I'm not sure I can answer the questions on solving the lock, I can offer these few data that might help.
First, a table variable can end up with schema locks on it. They're created in tempdb (and even move to disk if there isn't enough RAM available for them), and handled just like any other table in tempdb.
I'm not sure how it ends up locking with itself, but I think I've read that connection sharing can do that. I haven't had to deal with it, so I'm not sure of the solution.
Next, I recommend replacing that string parsing method. Either use a Numbers table, or XML, for the string split. It's faster, more efficient, and less lock-prone.
Here's a sample of a Numbers table version:
ALTER function [dbo].[StringParser]
(@String_in varchar(max),
@Delimiter_in char(1))
returns table
as
return(
SELECT top 100 percent
SUBSTRING(@String_in+@Delimiter_in, number,
CHARINDEX(@Delimiter_in, @String_in+@Delimiter_in, number) - number) as Parsed,
row_number() over (order by number) as Row
FROM numbers
WHERE number <= LEN(@String_in)
AND SUBSTRING(@Delimiter_in + @String_in, number, 1) = @Delimiter_in
ORDER BY number
)
And here's an XML version:
ALTER function [dbo].[StringParserXML]
(@String_in varchar(max),
@Delimiter_in char(10))
returns @Parsed table (
Row int,
Parsed varchar(100))
as
-- This one is faster than StringParser (Numbers), but it doesn't correctly handle
-- XML-specific characters, such as "<" or "&". StringParser2 will handle those
-- without difficulty.
begin
if right(@string_in, 1) = @delimiter_in
select @string_in = left(@string_in, len(@string_in) - len(delimiter))
declare @XML xml
select @xml = ' _i_' + replace(@string_in, @delimiter_in, '_/i__i_') + '_/i_'
insert into @parsed(row, parsed)
select row_number() over (order by x.i.value('.', 'varchar(100)')), x.i.value('.', 'varchar(100)')
from @xml.nodes('//i') x(i)
return
end;
I had to use something other than carrets in the XML string, so I used underscores. You'll need to replace those with carrets. This forum won't display the XML otherwise.
The XML one can be modified to handle XML-specific characters with a few replace commands, but I haven't bothered. One advantage it has that I like is that it can deal with multi-character delimiters, not just single-character ones.
The XML version is slightly faster than the Numbers one. Both are much faster than the looped string function versions.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 9, 2008 at 2:38 am
Thanks for the response, I'll take a look at replacing the split function. Thanks very much for the sample code.
Still struggling to understand how more than one owner id is able to exist in the same process? I don't think even Microsoft would be able to answer what has happened here!
Thanks again for the reply, if any one else has any other view i would love to hear them
September 9, 2008 at 9:16 am
SPIDs are re-used and can have different owners very easily. Might be as simple as that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 14, 2008 at 3:31 pm
Similar situation occured here today. This time it was a table variable used in an instead-of insert trigger. SPID blocking itself and the contended resource was the table variable. Waited for KILL several hours (0%, 0 seconds remaining). Eventually cycled the server.
The calling proceess was ADO.net's INSERT BULK, with connection pooling enabled.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply