1920548 - A spid in 'latch sleep' that cannot be killed and causes blocking - SAP ASE

• Encountering a situation in ASE where a spid in 'latch sleep' cannot be killed and causes blocking that may disrupt the functioning of the server
  • Directed from KBA 3486102 - Central KBA for 'latch sleep' and latch contention in SAP ASE
    
    
• Many spids show in either
  • 'latch sleep' or
  • a sleep type other than 'recv sleep', for example 'lock sleep'
    
    
• ASE processes are persistently blocked, usually by a single other spid in in 'latch sleep'
• A dbcc stacktrace on the blocking spid in 'latch sleep' includes functions buf_get_latch and/or bufsearch_cache_getlatch

  dbcc stacktrace(1234)
  
  pc: 0x00000000017dad87 upsleepgeneric+0x517()
  pc: 0x00000000016acfd0 buf_get_latch+0x490()
  pc: 0x000000000169ac17 bufsearch_cache_getlatch+0xd87()

• Neither the blocking spid nor waiting spid(s) can be killed, even using the 'with force' option and returns error 6125:

  kill 17 with force

  Msg 6125, Level 16, State 1:
  You cannot kill spid '17' with force option as it is waiting for a latch on buffer 0x0x28f776e0c8 (blpageno: 12345678, Requested Latch mode: 0x1) held by spid '594'

• The blocking spid reported in the 'lacth held by' can be:
  • waiting on another valid spid that is not moving on
  • waiting on a latch held by itself, in a self-deadlock
  • waiting on 'spid: -1'
  • waiting on spid that no longer exist, which shows by returning error 6106 when trying to kill that spid:
    
    Msg 6106, Level 16, State 1:
    Process '594' is not an active process number.
    
    
• Because of the blocking situation, the ASE server cannot be shutdown cleanly
  
  
• What causes this type of 'latch sleep' situation ?
• Is there a way to resolve a blocking spid in 'latch sleep' other than restarting ASE ?

SAP Sybase Adaptive Server Enterprise (ASE) - All versions

non-transactional, synchronization, mechanisms, physical, consistency, page, latch, wait, housekeeper, dbcc, lock_release, dbreboot , self-deadlock , selfdeadlock , self deadlock , deadlock , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , BC-DB-SYB , Business Suite on Adaptive Server Enterprise , Problem