IDS Forum

RE: need quick way to find whom is causing Blo....

Posted By: Alexandre Marini
Date: Tuesday, 5 March 2013, at 10:02 a.m.

In Response To: RE: need quick way to find whom is causing Blo.... (John Adamski)

My suggestion would be:
1) do you really want a transaction to do this? If you don´t have any related foreign keys, for example, you could easily do this job without a transaction, just output the sql command to a log, and watch for some errors
2) you could also reduce the transaction size, asking the developer to do it by phases, separating the jobs through a primary key filter, avoiding LONGTX (the best way I think)
3) as Art said, the most recommended situation would be an increase of your logical-logs, to at least 4 days of full transaction loads. But this demands disk space, so.... not always as easy as we would like.

Hope it helps.

Alexandre Marini
IBM Informix Certified Professional v10 / v11.50 / v11.70

IBM Information Management Informix Technical Professional

IBM Infosphere DataStage Technical Professional
Informix Senior DBA - Orizon Brasil
BRIUG website administrator
Informix independent consultant

> To: ids@iiug.org
> From: adamski@graceland.edu
> Subject: RE: need quick way to find whom is causing Blo.... [29697]
> Date: Tue, 5 Mar 2013 09:15:46 -0500
>
> Thanks for the information,
>
> I have LTXHWM set to 50 and LTXEHWM set to 60, sounds like I need to increase
> the difference.
>
> The cause seems to be a developer added a new field to a large table that
> should have been defaulted to not null, when a cleanup process ran it found a
> large number of nulls in the field and tried to change them to blank.
>
> I think I need to get a script setup to help identify who is causing these
> long transactions. Thanks everyone for your input.
>
> John
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John
> Miller iii
> Sent: Monday, March 04, 2013 12:19 PM
> To: ids@iiug.org
> Subject: Re: need quick way to find whom is causing Blo.... [29678]
>
> Just a few points here.
>
> Killing a session who is rolling back a long transaction will not help.
> The servers undo daemon will assume the rollback for this session.
>
> I believe what you want to look at is the difference between these two
> parameters. The first parameter LTXHWM will automatically force the session to
> start a rollback and NO other users will be effected by this one persons
> rollback. LTXEHWM (the parameter with the "E") is the threshold which provides
> EXCLUSIVE access to the logical logs and block all users attempting to write
> data. If you encounter this blockage my suggestion would be to increase the
> difference between this to values. By default hey come set at 70% and 80%. My
> suggestion would be to drop LTXHWM to 50%, What this means is any transaction
> which consume more than 50% of the logical logs will be forced to rollback and
> if it consume up to 80% of the logical logs it will stop others from writing
> to the logical logs and provide this transaction with exclusive resources.
>
> # LTXHWM - The percentage of the logical logs that can be # filled before a
> transaction is determined to be a # long transaction and is rolled back #
> LTXEHWM - The percentage of the logical logs that have been # filled before
> the server suspends all other # transactions so that the long transaction
> being # rolled back has exclusive use of the logs
>
> The other solution would be to utilize dynamic logs, which causes the logical
> logs to grow upon hitting a long transaction.
>
> My last point would be to let you know that onstat -x has been enhanced to
> provide an estimation of how long it will take to rollback transactions. The
> column in onstat -x is called "rb_time".
>
> If you want to look at this information programmatically look at the table
> called sysmaster:systxptab
>
> John F. Miller III
> STSM, Lead Architect
> miller3@us.ibm.com
> 503-747-1366
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 03/04/2013 08:09:28 AM:
>
> > From: "John Adamski" <adamski@graceland.edu>
> > To: ids@iiug.org,
> > Date: 03/04/2013 08:21 AM
> > Subject: need quick way to find whom is causing Blocked.... [29670]
> > Sent by: ids-bounces@iiug.org
> >
> > OS: HPUX 11.31 (11iv3) on BL870x IA server
> > IDS: 11.70.FC4
> >
> > Friday afternoon we had a Blocked:LONGTX that locked up the database
> > for
> a
> > while. I was informed while in a meeting that the "database is
> > down"about
> 40
> > minutes after the Blocked:LONGTX started.
> >
> > It took me a way too much time to find the session that had the block
> > and
> to
> > kill that session; I was wonder what would have been the quickest way
> > to
> find
> > who had the block that was preventing the rollback. As I'm sure I
> > chose a less than speedy way to do find it.
> >
> > John Adamski, Sr. Network Specialist
> > Graceland University, 1 University Place, Lamoni, IA 50140
> > adamski@graceland.edu
> >
> >
> >
>
>
> *******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>

Messages In This Thread

• need quick way to find whom is causing Blocked:LON
  John Adamski -- Monday, 4 March 2013, at 11:09 a.m.
  • RE: need quick way to find whom is causing Blocked
    Andrew Ford -- Monday, 4 March 2013, at 11:20 a.m.
  • RE: need quick way to find whom is causing Blocked
    Andrew Grantham -- Monday, 4 March 2013, at 11:53 a.m.
  • Re: need quick way to find whom is causing Blocked
    Art Kagel -- Monday, 4 March 2013, at 12:14 p.m.
    • RE: need quick way to find whom is causing Blo....
      Andrew Ford -- Monday, 4 March 2013, at 1:04 p.m.
      • RE: need quick way to find whom is causing Blo....
        John Adamski -- Tuesday, 5 March 2013, at 9:00 a.m.
        • Re: need quick way to find whom is causing Blo....
          Art Kagel -- Tuesday, 5 March 2013, at 9:12 a.m.
          • RE: need quick way to find whom is causing Blo....
            John Adamski -- Tuesday, 5 March 2013, at 2:34 p.m.
  • Re: need quick way to find whom is causing Blocked
    John Miller iii -- Monday, 4 March 2013, at 1:19 p.m.
    • RE: need quick way to find whom is causing Blo....
      John Adamski -- Tuesday, 5 March 2013, at 9:15 a.m.
      • RE: need quick way to find whom is causing Blo....
        Alexandre Marini -- Tuesday, 5 March 2013, at 10:02 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.