RE: Mysterious sql problem

Dave You say the exact same statement, but is the environment the same? i.e. do you have a lock mode set to wait in the app and not in dbaccess, do you set isolation dirty read in dbaccess but not in the app? What is the app session showing in the locks column when it is 'stuck'? Keith -> -----Original Message----- -> From: Dave Griffen [mailto:dgriffen@nospam.finishline.com] -> Sent: Thursday, November 04, 2004 8:57 PM -> To: informix-list@iiug.org -> Subject: Mysterious sql problem -> -> -> I've got an application thread that is sitting on the same -> statement for -> 3-10 hours. When the exact same statement is executed from -> dbaccess it -> returns in about 5 minutes. The dbaccess test has been -> tried with all -> available connection types, all with similar quick results. -> All sqexplains -> from dbaccess and from within the application show the same -> access path. -> -> Outside of the application, this statement is performing -> normally. Outside -> of this statement, the application is performing normally. -> -> Onstat -u shows that the application thread has consistent -> but very slow -> read progression, the lock count is high but remains -> unchanged, the write -> count is unchanged, and the vast majority of time is spent -> in critical -> section (flags --BPX--). -> -> A typical stack trace looks something like. -> -> Stack for thread: 7167 sqlexec -> base: 0xc0000000c961e000 -> len: 69632 -> pc: 0x0000000000000000 -> tos: 0xc0000000c96219e0 -> state: running -> vp: 3 -> -> 0x40000000003650b0 oninit :: resume + 0x0 sp=0xc0000000c96219e0 -> 0x40000000008e6137 oninit :: yield_processor_mvp + 0x1f7 -> sp=0xc0000000c96218b0 delta_sp=304 -> 0x400000000034fe83 oninit :: mt_yield + 0xc3 sp=0xc0000000c9621710 -> delta_sp=416 -> 0xc0000000be0bfda8 ?unknown? :: ?unknown? + 0x0 sp=0xc0000000c9621660 -> delta_sp=176 -> -> -> This application has been running for a bit over a year and -> the issue first -> surfaced 9 days ago. For a week the statement took hours -> but eventually -> finished. Yesterday, the application ran for over 12 hours -> before dieing -> due to a long transaction abort. -> -> I've had a tech support case open for over a week and don't -> see a resolution -> in sight. So, I'm throwing this out to the group. Has -> anyone seen anything -> like this before? Any ideas on why the thread is spending -> massive amounts -> of time in critical section when it is not currently -> modifying data? Any -> other ideas? -> -> Thanks, -> Dave Griffen -> -> -> ********************************************************************************** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. ********************************************************************************** sending to informix-list