Locks not releasing & oncheck

Hi All, We have a problem that even has IBM scratching its head: All of a sudden (no server updates, no code releases, etc), one of our instances is processing transactions VERY SLOWLY, and causing a lot of rollbacks and lock timeouts. Informix 11.50.FC6 running on Solaris 10 There are no messages in the message log, and checkpoints are taking 1 second max (usually zero). onstat -k|grep X|grep -v HDR shows several IX and X locks that hold forseveral seconds before disappearing. None of the transactions are large, usually single row *inserts*. Something REALLY unusual happened when IBM requested I ran oncheck -cc (the check of the system catalogs of every database). It completed fine against the system databases (sysmaster, sysadmin), but failed on the first table (systables) of the first database with: ISAM error: Lock Timeout Expired If I run on each db separately, some run fine, others do not. Not believing in coincidence, the databases that failed the oncheck are also the ones we're experiencing the rollbacks and lock issues. Any clues would be much appreciated! Pulling my hair out after 2 days of dealing with this -- and there isn't much hair left! I can provide as much further detail as you need. And yes, IBM PMR has been opened and they've been looking at this since yesterday afternoon. Thanks all, Michael Hoffman

Hi, Many Many years ago I had a similar situation on an informix instance in version 4.10. Although it took much time back then, we learned after much working that some simple database actions were taking much much longer than they should. I am betting that the problem back then is not what you are facing now. But if you find certain DML or even common run DDL that is taking a long time it might help find the issue. Back then it was four bit bitmap issues combined with text blobs of a certain, and specific sizes. I would turn on SQL trace ( if the system will allow it ) and check for different "long running SQL" that should be very quick. I might also check the loads of the tables you have using onstat -g ppf and see if some table is now being beaten beyond recognition. Might also check for item running from the dbscheduled ( although I have never had issues with this ). Good Luck. George. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MICHAEL HOFFMAN Sent: Tuesday, August 04, 2015 3:37 PM To: ids@iiug.org Subject: Locks not releasing & oncheck [35559] Hi All, We have a problem that even has IBM scratching its head: All of a sudden (no server updates, no code releases, etc), one of our instances is processing transactions VERY SLOWLY, and causing a lot of rollbacks and lock timeouts. Informix 11.50.FC6 running on Solaris 10 There are no messages in the message log, and checkpoints are taking 1 second max (usually zero). onstat -k|grep X|grep -v HDR shows several IX and X locks that hold forseveral seconds before disappearing. None of the transactions are large, usually single row *inserts*. Something REALLY unusual happened when IBM requested I ran oncheck -cc (the check of the system catalogs of every database). It completed fine against the system databases (sysmaster, sysadmin), but failed on the first table (systables) of the first database with: ISAM error: Lock Timeout Expired If I run on each db separately, some run fine, others do not. Not believing in coincidence, the databases that failed the oncheck are also the ones we're experiencing the rollbacks and lock issues. Any clues would be much appreciated! Pulling my hair out after 2 days of dealing with this -- and there isn't much hair left! I can provide as much further detail as you need. And yes, IBM PMR has been opened and they've been looking at this since yesterday afternoon. Thanks all, Michael Hoffman ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. This electronic message transmission contains information from the Company that may be proprietary, confidential and/or privileged. The information is intended only for the use of the individual(s) or entity named above. If you are not the intended recipient, be aware that any disclosure, copying or distribution or use of the contents of this information is prohibited. If you have received this electronic transmission in error, please notify the sender immediately by replying to the address listed in the "From:" field.

Just to get a basic items out of the way, I would ensure all of your index are valid and that you have run update statistics recentlly. If for some reason the optimizer is choosing a sequential scan, then the locks could beheld much longer than expected. I would run the select below against sysmaster to try and locate any table which shows to many sequential scans. While sequential scans are not a bad things on the correct queries, the large the table the more I would be sure you really want a sequential scan. Look for a table that is index incorrectly =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D select dbsname, tabname , pf=5Fseqscans , npdata, npused from sysptntab, systabnames, sysptnhdr where pf=5Fseqscans > 0 and sysptnhdr.npdata > 16 and sysptnhdr.npused > 16 and sysptnhdr.partnum =3D systabnames.partnum and systabnames.partnum =3D sysptntab.partnum order by pf=5Fseqscans DESC FIND bad indexes =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D select systabnames.dbsname, systabnames.tabname from sysptnkey, systabnames where bitval(flags,'0x40') =3D 1 and sysptnkey.partnum>1 and sysptnkey.partnum=3Dsystabnames.partnum; 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 08/04/2015 02:36:35 PM: > From: "MICHAEL HOFFMAN" <offdisc@gmail.com> > To: ids@iiug.org > Date: 08/04/2015 02:37 PM > Subject: Locks not releasing & oncheck [35559] > Sent by: ids-bounces@iiug.org > > Hi All, > We have a problem that even has IBM scratching its head: > > All of a sudden (no server updates, no code releases, etc), one of our > instances is processing transactions VERY SLOWLY, and causing a lot of > rollbacks and lock timeouts. > > Informix 11.50.FC6 running on Solaris 10 > > There are no messages in the message log, and checkpoints are taking1 second > max (usually zero). > > onstat -k|grep X|grep -v HDR shows several IX and X locks that hold for> several seconds before disappearing. None of the transactions are large, > usually single row *inserts*. > > Something REALLY unusual happened when IBM requested I ran oncheck -cc > (the check of the system catalogs of every database). It completed fine > against the system databases (sysmaster, sysadmin), but failed on the first > table (systables) of the first database with: > ISAM error: Lock Timeout Expired> > If I run on each db separately, some run fine, others do not. Not > believing in > coincidence, the databases that failed the oncheck are also the ones we're > experiencing the rollbacks and lock issues. > > Any clues would be much appreciated! Pulling my hair out after 2 days of > dealing with this -- and there isn't much hair left! > > I can provide as much further detail as you need. And yes, IBM PMR has been > opened and they've been looking at this since yesterday afternoon. > > Thanks all, > Michael Hoffman > > > ***************************************************************************= **** > Forum Note: Use "Reply" to post a response in the discussion forum. >