Help with a deadlock involving sys.sysschobjs

Question

Help with a deadlock involving sys.sysschobjs

DBADave

SSChampion

Points: 10962
More actions
April 21, 2016 at 9:32 am

#325755

We are receiving deadlocks in our Update Statistics job while inserting a record into a history table. The deadlocks are being caused by a Microsoft Service Manager process. We're trying to determine where within the INSERT statement sys.sysschobjs is being locked. Any ideas?
Here is the resource info from the log
keylock hobtid=281474978938880
dbid=10
objectname=OMDWDataMart.sys.sysschobjs
indexname=clst
id=lock8723d1200
mode=U
associatedObjectId=281474978938880
Below is the INSERT statement.
Insert Into DBA_HOME.dbo.Table_Statistics
(
ExecutionId,
DBName,
SchemaName,
ObjectName,
Objecttype,
StatsName,
StatsType,
StatsDesc,
StatsId,
CreateDate,
Duration,
Elapsed_Time,
RecordCount,
ScanSample
)
SELECT@ExecutionId,
@DBName,
LEFT(sch.name,50),-- Schema Name
obj.name,-- Object Name
obj.type,-- Object Type
LEFT(st.name,150),
CASE
WHEN ind.type is NULLThen 0
Else ind.type
END,-- Stats Type
CASE
WHEN ind.type IS NOT NULLTHEN LEFT(ind.type_desc,30)
WHEN ind.type IS NULL AND auto_created = 1THEN 'STATISTIC - AUTO CREATED'
WHEN ind.type IS NULL AND user_created = 1THEN 'STATISTIC - USER CREATED'
ELSE '*** UNKNOWN ***'
END,
stats_id,
STATS_DATE(obj.object_id, stats_id),
CASE
WHEN STATS_DATE(obj.object_id, stats_id) IS NOT NULL
THEN CASE
WHEN (select min(stats_id) from sys.stats where object_id = obj.object_id) = st.stats_id
THEN CONVERT(varchar(12),STATS_DATE(obj.object_id, stats_id) - @StartTime, 114)
ELSE CONVERT(varchar(12),STATS_DATE(obj.object_id, stats_id) - (select STATS_DATE(obj.object_Id, (select max(stats_id) from sys.stats where object_id = obj.object_id and stats_id < st.stats_id))), 114)
END
ELSE -- No Stats Date, which means statistcs were not updated. Only store the overall duration in the last stats_id
CASE
When st.stats_id= (select max(stats_id) from sys.stats where object_id = obj.object_id)
THEN CONVERT(varchar(12),@EndTime - @StartTime, 114)-- Only save duration for the last stats_id since no stats were updated
ELSE ''
END
END as Duration,
CASE
WHEN STATS_DATE(obj.object_id, stats_id) IS NOT NULL
THEN CONVERT(varchar(12),STATS_DATE(obj.object_id, stats_id) - @StartTime, 114)
ELSE
CASE
When st.stats_id= (select max(stats_id) from sys.stats where object_id = obj.object_id)
THEN CONVERT(varchar(12),@EndTime - @StartTime, 114)-- Only save duration for the last stats_id since no stats were updated
ELSE ''
END
END as Elapsed_Time,
(Select sum(row_count) From sys.dm_db_partition_stats Where object_id = obj.object_id and index_id in (0,1)),
@Sample
FROM sys.objects obj
JOIN sys.stats st
ON obj.object_id = st.object_id
LEFT JOIN sys.indexes ind
ON obj.object_id = ind.object_id
AND st.name = ind.name
LEFT JOIN sys.schemas sch
ON obj.schema_id = sch.schema_id
WHERE Object_Name(obj.object_id) = @ObjectName
And sch.name = @SchemaName
Service Manager has all sorts of deadlock issues, constantly deadlocking its own processing.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

sysschobjs is a base table that lies behind sys.objects, sys.indexes, and a bunch of other system views, so all the references to sys.objects, sys.indexes and such are why that INSERT would take out locks on sysschobjs.

However, I'm not sure why that query would be taking any U locks.

Is it possible for you to post the full deadlock xml?

Cheers!

DBADave SSChampion Points: 10962 More actions · Answer 2

That's the part that confuses me. The deadlock graph shows MODE: U for the victim, but the Service Manager process is MODE: X. Not sure why my insert would be an Update lock.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 3

That's why I'd like to see the full deadlock graph, if possible. Just from this information the only thing I could guess is that it's run in a transaction and a previous statement had taken the U locks.

Cheers!

DBADave SSChampion Points: 10962 More actions · Answer 4

<deadlock-list>

<process-list>

Insert Into DBA_HOME.dbo.Table_Statistics

(

ExecutionId,

DBName,

SchemaName,

ObjectName,

Objecttype,

StatsName,

StatsType,

StatsDesc,

StatsId,

CreateDate,

Duration,

Elapsed_Time,

RecordCount,

ScanSample

)

SELECT@ExecutionId,

@DBName,

LEFT(sch.name,50),-- Schema Name

obj.name,-- Object Name

obj.type,-- Object Type

LEFT(st.name,150),

CASE

WHEN ind.type is NULLThen 0

Else ind.type

END,-- Stats Type

CASE

WHEN ind.type IS NOT NULLTHEN LEFT(ind.type_desc,30)

WHEN ind.type IS NULL AND auto_created = 1THEN 'STATISTIC - AUTO CREATED'

WHEN ind.type IS NULL AND user_created = 1THEN 'STATISTIC - USER CREATED'

ELSE '*** UNKNOWN ***'

END,

stats_id,

STATS_DATE(obj.object_id, stats_id),

-- If processing the first stats_id for the current object, subtract from the start time

-- For all other stats_id subtract the current STATS_DATE from the STATS_DATE of the previo </frame>

unknown </frame>

sp_executesql </frame>

EXECUTE @Return_Status=sp_executesql @SQL </frame>

EXEC DBA_HOME.dbo.usp_wb_Update_Statistics </frame>

</executionStack>

Print 'Starting Update Statistics: ' + Convert(varchar(23),getdate(),121)

EXEC DBA_HOME.dbo.usp_wb_Update_Statistics </inputbuf>

</process>

ALTER INDEX [UniqueIndex] ON [dbo].[ActivityDim] REBUILD WITH (ONLINE=ON, FILLFACTOR=100) </frame>

EXECUTE (@OnlineStatement) </frame>

EXEC DomainTableIndexOptimize

@DatasetId = @DatasetId

,@BlockingMaintenanceStartTime = @BlockingMaintenanceStartTime

,@BlockingMaintenanceDurationMinutes = @BlockingMaintenanceDurationMinutes

--********************************************************

-- Insert new optimization work items

-- groom optimization history </frame>

</executionStack>

Proc [Database Id = 10 Object Id = 1893581784] </inputbuf>

</process>

</process-list>

<resource-list>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</objectlock>

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</keylock>

</resource-list>

</deadlock>

</deadlock-list>

</TextData>)

DBADave SSChampion Points: 10962 More actions · Answer 5

Here is the deadlock graph from our most recent deadlock. The log information from my earlier post is for the same type of deadlock, but at a different time of day.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

Ah, ok, as I read it that makes more sense.

The INSERT only has an S lock.

The REBUILD has the U lock and is trying to convert it to an X lock.

Cheers!

EDIT: That graph in the jpg seems to be a different sort of deadlock. It's on a user table, not sys.sysschobjs.

It also has two held X locks blocking two requests for S locks. The one with the INSERT and REBUILD had the REBUILD's Sch-M lock blocking the INSERT...SELECT's Sch-S on ActivityDim (the table being REBUILT; the Sch-S taken by the INSERT...SELECT is from STATS_DATE()), and the INSERT...SELECT's S lock blocking the REBUILD's U-to-X conversion.

It's a different object and a different locking pattern.

DBADave SSChampion Points: 10962 More actions · Answer 7

I completely missed the REBUILD. Now it makes sense. It didn't show up in previous deadlocks, but I enabled a couple TRACE flags today for more details so apparently that helped. We'll check with our System Center team to see why it's generating so many REBUILDs. Sounds like an incorrect configuration.

Thanks again