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

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.

IQ 16 SP11 PL24

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