Database in (LONGTX) need identify case user
User's Informix 11.70 database hangs with LONGTX (long transaction) status. Resolution: identify the problematic user via 'onstat -u' (look for "X" flag), then implement preventive measures: break large transactions into smaller pieces, use optimistic locking, increase logical logs, and adjust LTXHWM/LTXEHWM values. Killing the user won't stop ongoing rollback; fast recovery during restart may help.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All, i have informix 11.70.FC6X5 some time database hang with LONGTX can i track the case user at this point and is there any way to avoid this issue also in this point if i restart the engine it's taking long time to come online (it's in fast recovery status long time). Thanks Pushpa
Yes, you can avoid the long transaction problem by doing these things: 1. Limit globally affecting transactions like massive updates and deletes. There are tools and techniques to break these operations down into smaller pieces that avoids the problem altogether. 2. Make sure that applications are using Optimistic Locking protocols to avoid holding locks indefinitely in interactive applications when a user walks away from a transaction without canceling it. 3. Increase the number of logical logs so that the server can handle larger transactions without triggering a long transaction rollback. 4. Reduce the LTXHWM and LTXEHWM values so that more of the logical logs are available for transaction processing and less is reserved for rollback processing. If you do this, make sure that you have taken care of #3 first and that the absolute amount of logs available for rollback is sufficient to handle rollbacks without reaching the exclusive long transaction rollback scenario. Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Nov 12, 2013 at 10:50 AM, PUSHPA KUMARA <pushpa@cybersoft.lk> wrote: > Hi All, > > i have informix 11.70.FC6X5 some time database hang with LONGTX can i track > the case user at this point and is there any way to avoid this issue also > in > this point if i restart the engine it's taking long time to come online > (it's > in fast recovery status long time). > > Thanks > Pushpa > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3145c334b2504eafceae7
Hi Art, First thanks a lot for the reply in there any command line way to find out the case user Thanks Pushpa
The user causing the rollback? Yes, run onstat -u and look for an "X" in
the first flag position. That user is being rolled back and is likely the
cause of the long transaction.
Art
Art S. Kagel, Principal Consultant
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Nov 12, 2013 at 11:06 AM, PUSHPA KUMARA <pushpa@cybersoft.lk> wrote:
> Hi Art,
>
> First thanks a lot for the reply in there any command line way to find out
> the
> case user
>
> Thanks
> Pushpa
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c3274c01cc3404eafd2376
Hi Art, Thanks you very much for the reply one thing if we cough up the user and once we kill the user process will it case the engine. Thanks Pushpa
Killing the user connection will not stop the rollback which has to complete in any case. There is no way around that, rollbacks are how the database keeps you data in a sane state. Sometimes bouncing the server will result in the rollback completing more quickly because is is being handled by the "fast recovery" processing during the server startup, but only if you have enough offline recovery threads configured and the rollback is closer to the beginning of its run than to the end (because fast recovery first has to roll all open transactions forward before it can begin to roll back in complete transactions). Art Art S. Kagel, Principal Consultant Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Nov 12, 2013 at 11:20 AM, PUSHPA KUMARA <pushpa@cybersoft.lk> wrote: > Hi Art, > > Thanks you very much for the reply one thing if we cough up the user and > once > we kill the user process will it case the engine. > > Thanks > Pushpa > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3145c09775304eafd6456