1970441 - How to find the index id (indid) from monDeadLock MDA table in ASE? | SAP Knowledge Base Article

SAP Knowledge Base Article - Preview

1970441 - How to find the index id (indid) from monDeadLock MDA table in ASE?

Symptom

  • How to find the index id (indid) of a deadlock from the monDeadLock MDA table in SAP ASE when the indid is not provided?
     
  • Information from the monDeadLock MDA table does not include the indid, but it does include the page number and the object number:

select DB_name(HeldProcDBID) HeldDBName, Object_name(HeldProcedureID,HeldProcDBID),
DeadlockID, VictimKPID, InstanceID, ObjectID, PageNumber, HeldFamilyID, HeldSPID,
ObjectDBName, HeldLockType, HeldCommandfrom monDeadLock

HeldDBName HeldProcName DeadlockID VictimKPID InstanceID ObjectDBID ObjectID PageNumber HeldFamilyID HeldSPID HeldLockType HeldCommand
MYDB TheProcName 1 935397142 0 35 1691866063 13071 0 47 exclusive page INSERT
MYDB TheProcName 1 935397142 0 35 1691866063 13036 0 353 update page UPDATE
  • The information below is from the errorlog; it shows that the indid information that is not shown in the monDeadLock MDA table.

Deadlock Id 1 detected
Deadlock Id 1 detected. 1 deadlock chain(s) involved.
Deadlock Id 1: Process (Familyid 0, Spid 47, Suid 5451) was executing a INSERT command in the procedure 'TheProcName' at line 49 at nested level 1.
Deadlock Id 1: Process 47 was involved in application 'TheApp'.
Deadlock Id 1: Process 47 was involved on host name 'host.myserver.com'.
Deadlock Id 1: Process 47 was involved in transaction '$ins'.
SQL Text: MYDB..TheProcName
Executing procedure: TheProcName
Deadlock Id 1: Process (Familyid 0, Spid 353, Suid 5451) was executing a UPDATE command in the procedure 'TheProcName' at line 45 at nested level 1.
Deadlock Id 1: Process 353 was involved in application 'TheApp'.
Deadlock Id 1: Process 353 was involved on host name 'host.myserver.com'.
Deadlock Id 1: Process 353 was involved in transaction '$upd'.
SQL Text: MYDB..TheProcName
Executing procedure: TheProcName
Subordinate SQL Text: UPDATE MYDB.dbo.PUSERNOAUDIT
SET LastLoginDate = GETDATE()
WHERE UniqueID = @uniqueIdMatch
Deadlock Id 1: Process (Familyid 0, Spid 353) was waiting for a 'update page' lock on page 13071 of table 'PUSERNOAUDIT' in database 'MYDB' but process (Familyid 0, Spid 47) already held a 'exclusive page' lock on it.
Deadlock Id 1: Process (Familyid 0, Spid 47) was waiting for a 'exclusive page' lock on page 13036 of table 'PUSERNOAUDIT' , indid 2 in database 'MYDB' but process (Familyid 0, Spid 353) already held a 'update page' lock on it.
Deadlock Id 1: Process (Familyid 0, Spid 47) was chosen as the victim.
Victim process host = `host.myserver.com', user = `myuser' program name = `TheProcName' host processes = `' .
End of deadlock information.


Read more...

Environment

  • SAP Adaptive Server Enterprise 15.7
  • SAP Adaptive Server Enterprise 16.0

Product

SAP Adaptive Server Enterprise 15.7

Keywords

KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , How To

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.