Re: Remote LONGTX with no userthread in 9.30 UC6 W4

rdtbra wrote: > Hello all. > > We use here IDS 9.30.UC6W4, running in SunOS 5.7. Both Informix and Solaris are > 32 bits. For the second time we have reached a situation where the instance > shows a LONG TRANSACTION marked with flags --H--G and userthread 0. When this > instance is started, after the fast recovery it instantly is signed as > OnLine (LONGTX) and the second header line shows the instance as blocked. > > On our message file, there is an indication of a distributed transaction as > follows: > > 17:42:04 Aborting Long Transaction: tx 0x83dceef8 no user info due to XA or dis> tributed (2-phase commit) transaction > 17:42:04 Aborting Long Transaction: tx 0x83dced28 no user info due to XA or dis> tributed (2-phase commit) transaction > 17:42:04 Session completed abnormally. Rolling back tx id 170, flags 0x108463b > 17:42:04 Session completed abnormally. Rolling back tx id 80, flags 0x108463b > > 17:58:09 Aborting Long Transaction: tx 0x83dceb58 no user info due to XA or dis> tributed (2-phase commit) transaction > > After this, the instance is blocked, and the following appeared in our message log: > > 20:29:30 Logical Recovery has reached the transaction cleanup phase. > 20:29:30 Logical Recovery Complete.> 0 Committed, 0 Rolled Back, 3 Open, 0 Bad Locks > > 20:29:31 Dataskip is now ON for all dbspaces > 20:29:32 Checkpoint Completed: duration was 1 seconds. > 20:29:32 Checkpoint loguniq 109671, logpos 0x15018 > > 20:29:32 Maximum server connections 0 > 20:29:32 Blocking on XA transaction, tx 0x83dceb58, till it is cleaned up. > 20:29:32 Blocking on XA transaction, tx 0x83dced28, till it is cleaned up. > 20:29:32 Blocking on XA transaction, tx 0x83dceef8, till it is cleaned up. > 20:29:32 On-Line Mode> > The only solution was to call 24x7, and the problem was solved. They used an > utility to clear the transactions. > > Unfortunatelly there was a second occurrence yesterday. Additional information: > > Applications using Oledb with .NET. > Applications using ODBC. > Applications using ESQL/C. > > The only distributed transaction is a .NET application that opens two > connections with the same instance but two different databases. > > Does anyone has any ideas of what might be causing this situation? > > Thanks in advance > > rdtbra. What does onstat -x show? This will detail the actual transaction. Ensure that all you distributed transactions use the TCP connections from server to server. onmode -H 0x83dceef8 might have worked in this situation. onmode -H <address> forget heuristically completed transaction Still

TBP wrote: > rdtbra wrote: >> We use here IDS 9.30.UC6W4, running in SunOS 5.7. Both Informix >> and Solaris are 32 bits. For the second time we have reached a >> situation where the instance shows a LONG TRANSACTION marked with >> flags --H--G and userthread 0. When this instance is started, >> after the fast recovery it instantly is signed as OnLine (LONGTX) >> and the second header line shows the instance as blocked. >> >> On our message file, there is an indication of a distributed >> transaction as follows: >> >> 17:42:04 Aborting Long Transaction: tx 0x83dceef8 no user info due to>> XA or distributed (2-phase commit) transaction >> 17:42:04 Aborting Long Transaction: tx 0x83dced28 no user info due to>> XA or distributed (2-phase commit) transaction >> 17:42:04 Session completed abnormally. Rolling back tx id 170, flags>> 0x108463b >> 17:42:04 Session completed abnormally. Rolling back tx id 80, flags>> 0x108463b >> >> 17:58:09 Aborting Long Transaction: tx 0x83dceb58 no user info due to>> XA or distributed (2-phase commit) transaction >> >> After this, the instance is blocked, and the following appeared in our >> message log: >> >> 20:29:30 Logical Recovery has reached the transaction cleanup phase. >> 20:29:30 Logical Recovery Complete.>> 0 Committed, 0 Rolled Back, 3 Open, 0 Bad Locks >> >> 20:29:31 Dataskip is now ON for all dbspaces >> 20:29:32 Checkpoint Completed: duration was 1 seconds. >> 20:29:32 Checkpoint loguniq 109671, logpos 0x15018 >> >> 20:29:32 Maximum server connections 0 >> 20:29:32 Blocking on XA transaction, tx 0x83dceb58, till it is>> cleaned up. >> 20:29:32 Blocking on XA transaction, tx 0x83dced28, till it is>> cleaned up. >> 20:29:32 Blocking on XA transaction, tx 0x83dceef8, till it is>> cleaned up. >> 20:29:32 On-Line Mode>> >> The only solution was to call 24x7, and the problem was solved. They >> used an utility to clear the transactions. >> >> Unfortunatelly there was a second occurrence yesterday. Additional >> information: >> >> Applications using Oledb with .NET. >> Applications using ODBC. >> Applications using ESQL/C. MTS - M$ Transaction Server/Service? Is anything on the client side using that? >> The only distributed transaction is a .NET application that opens two >> connections with the same instance but two different databases. Do you know if any other database servers are involved? Or is it all strictly local to the current IDS server? Of itself, two separate connections are simply two separate connections, but .NET might be making them into one XA transaction. In which case, the question is - why is the XA coordinator dying? Or, roughly equivalently - who is turning off their PC and not restarting it? Or who's machine is BSoD'ing and not rebooting in a timely manner? Who's cleaning lady pulls the plug out from their PC each night? >> Does anyone has any ideas of what might be causing this situation? > > What does onstat -x show? This will detail the actual transaction. > > Ensure that all you distributed transactions use the TCP connections > from server to server. > > onmode -H 0x83dceef8> > might have worked in this situation. > > onmode -H <address> forget heuristically completed transaction These suggestions are, I think, reasonable. Heuristic rollbacks are inherently unsatisfactory, but until you know why the XA coordinator is not around, it may be your best workaround - more satisfactory than having Informix support come in to do that. Although the errors mention two phase (distributed) transactions, unless there is another IDS server that could also be in use - and it would be having recovery problems too, and it would be the coordinator system for the transactions - then the problem is that the XA coordinator is dying and not reappearing in a timely manner. And, as I suggested, that sounds like a PC using MTS that is not shutting up shop properly - nor restarting reliably. Since the problems happened in the evening (quarter to six; eight o'clock0), this is a semi-plausible hypothesis. I wonder; could the XA coordinator be on a DHCP host, which doesn't get the same IP address when it is restarted? That could screw things up totally. I don't know whether IDS would allow a different IP address to identify the original GTRID and close the transaction, or whether IDS would say "well, that GTRID was not originated by your (new DHCP) IP address, so I can say I know nothing about the XA TX; we can presume it was aborted", while still waiting for the original IP address to ask about the GTRID - or say that it was rolled back. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/