KNOWN ISSUE: Getting deadlocks in the stored procedure "sp_Inv_AeX_SWD_Status_Summary_clean"

Article:TECH194040

Created: 2012-07-30

Updated: 2012-07-30

Article URL http://www.symantec.com/docs/TECH194040

NOTE: If you are experiencing this particular known issue, we recommend that you to receive email notification each time this article is updated. Subscribers will be the first to learn about any releases, status changes, workarounds or decisions made.

Article Type
Technical Solution

Product(s)

Show all

Languages

Show all

Problem

The customer reported that in a regular basis he get deadlocks in his NS Logs, even though his SQL Server doesn't show any busy transactions.

Error

The SQL Logs showed the following:

deadlock-list
2011-11-18 00:19:28.31 spid25s      deadlock victim=process68a54c8
2011-11-18 00:19:28.31 spid25s       process-list
2011-11-18 00:19:28.31 spid25s        process id=process68a54c8 taskpriority=0 
logused=456 waitresource=KEY: 24:72057601226833920 (e77bf98e79a2) waittime=690 
ownerId=4123474084 transactionname=2e239d64c72548dcb4d1d38d4befe6f4 
lasttranstarted=2011-11-18T00:19:27.620 XDES=0x170549950 lockMode=S 
schedulerid=15 kpid=11492 status=suspended spid=101 sbid=0 ecid=0 priority=0 
trancount=1 lastbatchstarted=2011-11-18T00:19:27.623 lastbatchcompleted=2011-11-
18T00:19:27.623 clientapp=.Net SqlClient Data Provider hostname=SMP-NS2 
hostpid=9424 loginname=Domain\ServiceAltiris isolationlevel=serializable (4) 
xactid=4123474084 currentdb=24 lockTimeout=4294967295 clientoption1=673185824 
clientoption2=128056
2011-11-18 00:19:28.31 spid25s         executionStack
2011-11-18 00:19:28.31 spid25s          frame 
procname=Symantec_CMDB.dbo.sp_Inv_InstalledSoftware_clean line=8 stmtstart=268 
stmtend=710 sqlhandle=0x0300180075de38382c743701079f00000100000000000000
2011-11-18 00:19:28.31 spid25s     select _id = max( _id )
2011-11-18 00:19:28.31 spid25s     from dbo. [Inv_InstalledSoftware] with (readcommitted)  2011-11-18 00:19:28.31 spid25s     where  [_ResourceGuid] = @ResourceGuid  2011-11-18 00:19:28.31 spid25s     group by   [_ResourceGuid] , [_SoftwareComponentGuid]  2011-11-18 00:19:28.31 spid25s     having count (_id) > 1     
2011-11-18 00:19:28.31 spid25s          frame procname=adhoc line=1 
sqlhandle=0x0100180087a2143b40529685000000000000000000000000
2011-11-18 00:19:28.31 spid25s     exec dbo.
[sp_Inv_InstalledSoftware_clean]  '5a51d76b-9ea9-42d1-8dc9-2325b0462ac9'     
2011-11-18 00:19:28.31 spid25s         inputbuf
2011-11-18 00:19:28.31 spid25s     exec dbo.
[sp_Inv_InstalledSoftware_clean]  '5a51d76b-9ea9-42d1-8dc9-2325b0462ac9'     
2011-11-18 00:19:28.31 spid25s        process id=process1f5070088 
taskpriority=0 logused=456 waitresource=KEY: 24:72057601226833920 (1e3792199cae)

Environment

Symantec Management Platform 7.1 SP1

Cause

Known Issue. This stored procedure required some optimization as well some of the processes that required access to this stored procedure.

Solution

This issue has been reported to the Symantec Development team. A fix will be provided in our next release (currently targeted for SMP 7.1 SP2 MP1 and 7.5 releases)

There is a point fix available for this issue. Please see the attached file called "Pointfix_eTrack2643261.zip"

REQUIREMENTS:
This point fix was created to be installed on top of SMP 7.1 SP1

HOW TO INSTALL THIS POINTFIX:
1.     Retrive files from the archive to the NS hard drive.
2.     Run as administrator PFinstaller.EXE, click on ‘Install’ button
3.     Old binaries will be automatically backuped to the same location where PFinstaller is and replaced with the new ones.

HOW TO UNINSTALL:
Point Fix Uninustall is not available – it won’t restore old stored procedures.

Attachments

Pointfix_eTrack2643261.zip (979 kBytes)

Supplemental Materials

Source	ETrack
Value	2643261