SAP Knowledge Base Article - Preview

2884258 - Stuck thread in st_txnMgr::SendSecondaryVersionList results in versioning -- SAP IQ

Symptom

IQ multiplex configuration was ok while on IQ 16 sp11 pl20.

After upgrading to IQ 16 sp11 pl24, open transaction related to ev_iqmpxstatus() was noticed on some of the secondary nodes.

Secondary iqmsg shows single execution ev_iqmpxstatus() event.
--------------------------------------------------------------------------------
$ grep -i -e ev_iqmpxstatus -e " DB: Versn:" SecondaryNode.iqmsg

I. 01/11 19:17:56. 0000000000 DB: Versn: 16.0.110.2805/11355/P/sp11.20/Enterprise Linux64 - x86_64 - 2.6.18-194.el5/64bit/2018-06-15 02:57:16
I. 01/11 19:17:58. 0000000000 DB: Versn: 16.0.110.2805/11355/P/sp11.20/Enterprise Linux64 - x86_64 - 2.6.18-194.el5/64bit/2018-06-15 02:57:16
I. 01/11 19:18:06. 0000000006 ev_iqmpxstatus started
I. 01/11 19:19:00. 0000000047 ev_iqmpxstatus started
I. 01/11 19:20:00. 0000000081 ev_iqmpxstatus started
I. 01/11 19:21:00. 0000000118 ev_iqmpxstatus started <--------- Running every minute
..
I. 01/12 12:38:00. 0000040513 ev_iqmpxstatus started
I. 01/12 13:21:33. 0000000000 DB: Versn: 16.0.110.3193/13291/P/sp11.24/Enterprise Linux64 - x86_64 - 2.6.18-194.el5/64bit/2019-09-24 12:32:07
I. 01/12 13:21:34. 0000000000 DB: Versn: 16.0.110.3193/13291/P/sp11.24/Enterprise Linux64 - x86_64 - 2.6.18-194.el5/64bit/2019-09-24 12:32:07
I. 01/12 13:22:00. 0000000024 ev_iqmpxstatus started <--------- Running once

I. 01/13 12:33:46. 0000000000 DB: Versn: 16.0.110.3193/13291/P/sp11.24/Enterprise Linux64 - x86_64 - 2.6.18-194.el5/64bit/2019-09-24 12:32:07
I. 01/13 12:33:52. 0000000000 DB: Versn: 16.0.110.3193/13291/P/sp11.24/Enterprise Linux64 - x86_64 - 2.6.18-194.el5/64bit/2019-09-24 12:32:07
I. 01/13 12:34:00. 0000000009 ev_iqmpxstatus started

I. 01/13 15:55:48. 0000000000 DB: Versn: 16.0.110.3193/13291/P/sp11.24/Enterprise Linux64 - x86_64 - 2.6.18-194.el5/64bit/2019-09-24 12:32:07
I. 01/13 15:55:50. 0000000000 DB: Versn: 16.0.110.3193/13291/P/sp11.24/Enterprise Linux64 - x86_64 - 2.6.18-194.el5/64bit/2019-09-24 12:32:07
I. 01/13 15:56:00. 0000000015 ev_iqmpxstatus started <------- Pstack collected shows following thread
--------------------------------------------------------------------------------

Pstack on secondary IQ server spid shows a thread which does not move.
--------------------------------------------------------------------------------
hos_condvar::Wait
inc_MpxConnection::LockForExecution
inc_CommandHandler::RunVersionCmd
st_globalFlMgr::ProcessSecondaryShTempDeallocate
st_txnMgr::SendSecondaryVersionList
db_iqutilityCatRepl::Execute
st_command::DoCmdThroughResourceGate
db_iqddl::IQUtilityCatRepl
UIQDDL_IQUtilityCatRepl
st_SAIQInterfaceInfo::callFunction
st_SAIQInterface::RunIQFunc
st_SAIQInterface::Execute
saint_iqthresholdddl::IQUtility
dbi_iq_utilities
DoExecuteStmt
exec_procedure_stmt
run_procedure
call_procedure
DB_Exec_event_handler
EventHandler::do_request
--------------------------------------------------------------------------------

Coordinator started up building versions slowly. After 1 day there were 1.5 TB of versions.
Coordinator started to crawl and simple procedures are extremely slow.

On coordinator, for problem secondary node ,
   sp_iqconnection() shows stuck connections IQ_MPX_SERVER_H0 with IQCmdType of PROCESSMPXACK. 
   sp_iqtransaction() shows active transactions related to stuck connections in sp_iqconnection().
   sp_iqcontext() shows sp_iqmpxack call.

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

ConnHandle Name      Userid    LastReqTime               ReqType IQCmdType LastIQCmdTime IQCursors LowestIQCursorState IQthreads TxnID ConnCreateTime TempTableSpaceKB TempWorkSpaceKB IQconnID satoiq_count iqtosa_count CommLink NodeAddr LastIdle MPXServerName LSName INCConnName INCConnSuspended

-------------------------------------------------------------------------------------------------------------------------
7 IQ_MPX_SERVER_H0 dbo 2020-01-13 18:01:48.412 EXEC PROCESSMPXACK Jan 13 2020 6:01:48.000000PM 0 NONE 0 652551569 Jan 13 2020 6:01:48.000000PM 0 0 63 29 1940313137 TCPIP XX.XX.XX.XXX 0 SECONDARY SERVER N
8 IQ_MPX_SERVER_P11 dbo 2020-01-13 18:01:48.412 PREFETCH NONE Jan 13 2020 6:01:48.000000PM 0 NONE 0 652551567 Jan 13 2020 6:01:48.000000PM 0 0 62 22 76 TCPIP XX.XX.XX.XXX 0 SECONDARY SERVER N

Name                         Userid TxnID    CmtID VersionID State ConnHandle IQConnID MainTableKBCr MainTableKBDr TempTableKBCr TempTableKBDr TempWorkSpaceKB TxnCreateTime CursorCount SpCount SpNumber MPXServerName GlobalTxnID VersioningType Blocking BlockingTimeout

-------------------------------------------------------------------------------------------------------------------------
IQ_MPX_SERVER_P11 dbo 652551567 0 652551567 ACTIVE 8 62 0 0 0 0 0 2020-01-13 18:01:48.406 0 1 0 SECONDARY 0 Table-level False 0
IQ_MPX_SERVER_H0  dbo  652551569 0 652551569 ACTIVE 7 63 0 0 0 0 0 2020-01-13 18:01:48.413 0 1 0 SECONDARY 0 Table-level False 0
--------------------------------------------------------------------------------

Simple bouncing secondary node did not help.
Versioning problem went away temporarily after these secondary nodes were stopped/excluded/resynced and restarted.


Read more...

Environment

IQ 16 SP11 PL24

Product

SAP IQ 16.0

Keywords

st_globalFlMgr::ProcessSecondaryShTempDeallocate , st_txnMgr::SendSecondaryVersionList , st_txnMgr::RollBackGlobalTxnsev_iqmpxstatus  , KBA , BC-SYB-IQ , Sybase IQ , Bug Filed

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.