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

• 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

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

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