Stuck transaction

Hi All, Informix 11.5.FC9 running on Solaris 10. I found a transaction using 'onstat -x' that is definitely stuck. 119696f88 -LH-G 0 0 logid 81731 81763:0x189257c COMMIT 0:00 0 The problem is that those log numbers date back to when the engine crashed in Nov. 2013! (our current logid is 86457) The engine has been restarted several times since then, so obviously this transaction can never be completed. Since it is not attached to a session (sesid = 0), it cannot be killed with 'onmode -z'. Any ideas how I can kill the session and preventit from restarting, or push it forward to completion? Thanks, Michael Hoffman

On 05/05/15 15:47, MICHAEL HOFFMAN wrote: > Hi All, > Informix 11.5.FC9 running on Solaris 10. > I found a transaction using 'onstat -x' that is definitely stuck. > > 119696f88 -LH-G 0 0 logid 81731 81763:0x189257c COMMIT 0:00 0 > > The problem is that those log numbers date back to when the engine crashed in > Nov. 2013! (our current logid is 86457) > > The engine has been restarted several times since then, so obviously this > transaction can never be completed. Since it is not attached to a session > (sesid = 0), it cannot be killed with 'onmode -z'. > > Any ideas how I can kill the session and preventit from restarting, or push it > forward to completion? > > Thanks, > Michael Hoffman > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > That's a global transaction. Have your transaction manager attach to it and forget it, or alternatively, open a support PMR and have down systems forget it for you. -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

Hi Michael, I believe you can try onmode -Z with the transaction address, if it doesn't work try the onmode -H, also with the transaction address. If it doesn't work then you have to call your support. Keen regards On Tue, 5 May 2015 at 15:48 MICHAEL HOFFMAN <offdisc@gmail.com> wrote: > Hi All, > Informix 11.5.FC9 running on Solaris 10. > I found a transaction using 'onstat -x' that is definitely stuck. > > 119696f88 -LH-G 0 0 logid 81731 81763:0x189257c COMMIT 0:00 0 > > The problem is that those log numbers date back to when the engine crashed > in > Nov. 2013! (our current logid is 86457) > > The engine has been restarted several times since then, so obviously this > transaction can never be completed. Since it is not attached to a session > (sesid = 0), it cannot be killed with 'onmode -z'. > > Any ideas how I can kill the session and preventit from restarting, or > push it > forward to completion? > > Thanks, > Michael Hoffman > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bfcefa69c4f51051557373b

Hi Michael, As Ricardo said, if you didn't succeed with "onmode -Z" or with "onmode -H", please call immediatly Informix technical support and give them direct access to Informix server. They have a possibility(tool) to kill this global transaction. I think this is the only option. Regards, Boycho

That must be a record :) Global (XA) transactions are sticky... because Informix was ot the TX coordinator, it cannot decid what to do... and we take this (too...?) seriously. That's why Marco suggested to tell your XA TX manager to order a rollback... but considering this is from 2013... welll... I have some doubts... onmode -Z/-H don't always work... That's why you may need to call tech support.Apart from that I'd strongly suggest you to vote on this RFE: http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=36910 I'm not 100% sure it matches your case (these transactions can be in a lot of states). But in any case it's a problem that for a simple task customers need to call tech support. In particular in times where vendors would like to reduce tech support costs. The only drawback is that customers may force rollbacks in the database that will create inconsistencies in their systems (on architectures that include several XA compliant systems... Regards On Tue, May 5, 2015 at 3:47 PM, MICHAEL HOFFMAN <offdisc@gmail.com> wrote: > Hi All, > Informix 11.5.FC9 running on Solaris 10. > I found a transaction using 'onstat -x' that is definitely stuck. > > 119696f88 -LH-G 0 0 logid 81731 81763:0x189257c COMMIT 0:00 0 > > The problem is that those log numbers date back to when the engine crashed > in > Nov. 2013! (our current logid is 86457) > > The engine has been restarted several times since then, so obviously this > transaction can never be completed. Since it is not attached to a session > (sesid = 0), it cannot be killed with 'onmode -z'. > > Any ideas how I can kill the session and preventit from restarting, or > push it > forward to completion? > > Thanks, > Michael Hoffman > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --e89a8f50316c7d82c105155b851f

Thanks for all the replies. Neither onmode -Z nor onmode -H worked; both complained that there was no transaction found for that address. :-( I even converted the address to integer for onmode -Z, no joy. So I will end up calling Informix and have them log in when I get permission from Security to drop my firewall. Or we may leave it -- not like it's causing any real issues in the past 2.5 years! :-) Oh, and Fernando, I voted for that RFE. Thanks for pointing it out. Michael Hoffman

I also voted for the RFE that Fernando referenced. I've wanted to script those steps many times since we do hit the "can't get exclusive access" issue in a few surprising cases. Recently we had this issue and couldn't find anyone holding "anything" on the table in question. I was trying to apply a simple ALTER. Fortunately we were able to bounce the engine quickly, apply the alter and be done. Thanks - Mark Scranton The Mark Scranton Group www.markscranton.com mark@markscranton.com

An open cursor on a table may prevent ALTERs... even if done in DR... but onstat -g opn and onstat -k are able to show this...I've created a script to show who's preventing an ALTER... But in a really busy system it can be a nightmare. For that I'd recommend another RFE... which asks for FORCE_DDL_EXEC to be enforced for any DDL instruction. Regards On Wed, May 6, 2015 at 4:03 PM, MARK SCRANTON <mark@markscranton.com> wrote: > I also voted for the RFE that Fernando referenced. I've wanted to script > those > steps many times since we do hit the "can't get exclusive access" issue in > a > few surprising cases. Recently we had this issue and couldn't find anyone > holding "anything" on the table in question. I was trying to apply a simple > ALTER. Fortunately we were able to bounce the engine quickly, apply the > alter > and be done. > > Thanks - > Mark Scranton > The Mark Scranton Group > www.markscranton.com > mark@markscranton.com > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --089e0149bb0e650ad305156c6625

Hi all!!! SUCCESS!!!!! After all these months, we got tripped up by this transaction while trying to upgrade to Informix 12. After the install, running 'oninit' caused a Fatal Error that the engine had found an open transaction from the previous version and could not complete. My memory got jogged while crolling through this group, and I realized we were upgrading the same instance as this transaction. Well, onmode -H killed it off! Why didn't it work the first time? I was just applying the address from onstat -x to onmode -H ---- instead of pre-pending a "0x" to define it as a hex number. Got to watch those hex/integer conversions!! Thanks everyone! Fingers crossed the the upgrade runs smoothly tomorrow. (Already found one bug in the sysadmin update; to be reported later) Michael Hoffman