1640918 - Frequent deadlocks in CMS system database running on MS SQL server or Sybase ASE

• Frequently getting deadlocks in Central Management Server (CMS) database
• Following error in SQL Server database: "Transaction (Process ID ##) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
• Following query can be seen on the database:
  SELECT LOCKTABLE.PARENTID, LOCKTABLE.CHILDID,0,LOCKTABLE.ISMEMBER FROM dbo.CMS_LOCKS7 LOCKTABLE WHERE LOCKTABLE.CHILDID IN (******)
• ASE (Adaptive Server Enterprise) logs may show entries similar to the following:
  

  00:0002:00000:00272:2017/03/23 07:28:06.99 server Deadlock Id 6 detected
  Deadlock Id 6 detected. 1 deadlock chain(s) involved.

  Deadlock Id 6: Process (Familyid 0, Spid 268, Suid 1) was executing a UPDATE command in the procedure 'objid 1673963643' at line 1 at nested level 1.
  Deadlock Id 6: Process 268 was involved in application 'cms'.
  Deadlock Id 6: Process 268 was involved on host name ''.
  Deadlock Id 6: Process 268 was involved in transaction '$chained_transaction'.
  SQL Text: UPDATE CMS_InfoObjects7 SET Version = @1, LastModifyTime = @2, CRC = @3, Properties = NULL WHERE ObjectID = @5 AND Version = @6 D
  Executing procedure: *ss1673963643_0420890703ss*
  Subordinate SQL Text:
  UPDATE CMS_InfoObjects7 SET Version = @1, LastModifyTime = @2, CRC = @3, Properties = NULL WHERE ObjectID = @5 AND Version = @6
  Deadlock Id 6: Process (Familyid 0, Spid 272, Suid 1) was executing a SELECT command in the procedure 'objid 1689963700' at line 1 at nested level 1.
  Deadlock Id 6: Process 272 was involved in application 'cms'.
  Deadlock Id 6: Process 272 was involved on host name ''.
  SQL Text: SELECT CMS_InfoObjects7.ObjectID FROM CMS_InfoObjects7 WHERE TypeID = 16 AND SI_PLUGIN_OBJECT = 0
  Executing procedure: *ss1689963700_0846335303ss*
  Subordinate SQL Text:
  SELECT CMS_InfoObjects7.ObjectID FROM CMS_InfoObjects7 WHERE TypeID = 16 AND SI_PLUGIN_OBJECT = 0
  Deadlock Id 6: Process (Familyid 0, Spid 272) was waiting for a 'shared row' lock on row 0 page 255113 of the 'CMS_InfoObjects7' table in database 'master' but process (Familyid 0, Spid 268) already held a 'exclusive row' lock on it.
  Deadlock Id 6: Process (Familyid 0, Spid 268) was waiting for a 'exclusive row' lock on row 20 page 1067040 of the 'CMS_InfoObjects7' table in database 'master' but process (Familyid 0, Spid 272) already held a 'shared row' lock on it.

  Deadlock Id 6: Process (Familyid 0, Spid 268) was chosen as the victim.
  Victim process host = `', user = `sa' program name = `cms' host processes = `2268' .

Read more...

• SAP BusinessObjects Business Intelligence Platform 4.x (BI 4.0 / 4.1 / 4.2 / 4.3)
• Crystal Reports Server 2011 (2013 / 2016)
• Adaptive Server Enterprise (ASE) 16.x

SAP BusinessObjects Business Intelligence platform 4.0 ; SAP BusinessObjects Business Intelligence platform 4.1 ; SAP BusinessObjects Business Intelligence platform 4.2 ; SAP Crystal Server 2011 ; SAP Crystal Server 2011, feature pack 3

deadlock, cmsdb, mssql server, bi 4.0, bi 4.1, bi 4.2, bi 4.3, business objects, ADAPT01655349, ADAPT01655350, Deadlock, TE: 5000438360, sybase ASE, SAP ASE, Adaptive Enterise Server, CMS_LOCKS7 , KBA , db lock , deadlocks , BI-BIP-SRV , CMS / Auditing issues (excl. 3rd Party Authentication) , Problem