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
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 for Me (Login required).Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.