SAP Knowledge Base Article - Preview

2226027 - Online Reorg rebuild is blocked/deadlocked with an OPEN CURSOR when traceflag is enabled - SAP ASE

Symptom

  • When Trace flag 2792 enabled, the 'REORG REBUILD WITH ONLINE' command executes first. Approximately 1 minute later, the READ ONLY CURSOR
    begins processing the same table.  Initially, the OPEN CURSOR command is in 'sleeping' status (expected behavior when 2792 is enabled), so that the
    REORG can acquire the needed exclusive locks.

 

  • When the REORG reaches a non-blocking phase , the OPEN CURSOR will acquire and hold a blocking 'shared intent' lock on the target table that will stop the
    REORG from re-acquiring an exclusive table lock. Eventually, the REORG REBUILD  command will get placed into LOCK SLEEP and both processes are then
    stalled as if it were a deadlock.  However, because ASE does not recognize the deadlock, the processes will continue to block one another.  Although OPEN CURSOR
    cannot be killed (sleeping), it is possible to kill the REORG REBUILD command and the OPEN CURSOR will then free up.

 

  • Note: Trace Flag 2792 was introduced to cause processes to be blocked while attempting to access a table undergoing REORG REBUILD, rather than abort with an 8233 error.

 

  • Sample output

1> select spid,status,cmd,blocked from sysprocesses where spid in (18,23)
2> go
 spid   status
         cmd                                                          blocked
 ------ ------------------------
         ------------------------------------------------------------ -------
     18 lock sleep
         Online REORG                                                      23
     23 sleeping
         OPEN CURSOR                                                        0


1> select * from monLocks
2> go
 SPID        InstanceID KPID        DBID        ParentSPID  LockID
         Context
         DBName
         ObjectID    LockState
         LockType
         LockLevel
         WaitTime    PageNumber  RowNumber
         BlockedState                                                          
         BlockedBy
         SourceCodeID
 ----------- ---------- ----------- ----------- ----------- -----------
         -----------
         ------------------------------------------------------------
         ----------- ----------------------------------------
         ----------------------------------------
         ------------------------------------------------------------
         ----------- ----------- -----------
        

----------------------------------------------------------------------------------------------------

----------------------------
         -----------
         ------------------------------------------------------------
          18          0     8192063           5           0          36
                   0
         testdb
           576002052 Requested
         exclusive table
         TABLE
                1974        NULL        NULL
         Blocked                                                                
                  46
         onlutils.c:405
          18          0     8192063           5           0          36
                   0
         testdb
           576002052 Granted
         exclusive intent
         TABLE
                NULL        NULL        NULL
         NULL                                                                  
                NULL
         seq_lock.c:1736
          23          0     8454146           5           0          46
                   0
         testdb
           576002052 Granted
         shared intent
         TABLE
                NULL        NULL        NULL
         Blocking                                                              
                NULL
         seq_lock.c:1736
          18          0     8192063           2           0          36
                   0
         tempdb
                  24 Granted
         shared intent
         TABLE
                NULL        NULL        NULL
         NULL                                                                  
                NULL
         statsapi.c:660
          18          0     8192063           5           0          36
                   0
         testdb
                  24 Granted
         shared intent
         TABLE
                NULL        NULL        NULL
         NULL                                                                  
                NULL
         statsapi.c:660


Read more...

Environment

  • SAP Adaptive Server Enterprise (ASE)  15.7 
  • SAP Adaptive Server Enterprise (ASE)  16.0 

 

 

Product

SAP Adaptive Server Enterprise 15.7 ; SAP Adaptive Server Enterprise 16.0

Keywords

reorg, rebuild , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Problem

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.