Long Transaction Abort !!

Hi , Can somebody, please, reply on the following problem : we have a financial transaction processing system. recently during the day, sometimes our system becomes very slow. we traced the reason. during these points of extreme slowness -- the informix log has generally a long transaction abort message. we are trying to find out "which of our programs cause this long txn abort?" my queries are : 1) can someone give some tips on how to pinpoint the exact queries/insert/update stmts which are causing long txn abort? 2) what are the ways by which we could avoid long txn abort (eg. say by increasing the logical logs or by changing some informix parameters.) 3) what care should be taken while coding such that the code doesn't cause such long txns? thanks in advance, sandeep -- Such is life and such is growing And thru' mistakes, we end up in knowing !!! -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

StudJaggu wrote: > > Hi , > > Can somebody, please, reply on the following problem : > > we have a financial transaction processing system. > recently during the day, sometimes our system becomes very slow. > we traced the reason. during these points of extreme slowness -- > the informix log has generally a long transaction abort message. > we are trying to find out "which of our programs cause this long txn abort?" > > my queries are : > > 1) can someone give some tips on how to pinpoint the exact > queries/insert/update stmts which are causing long txn abort? When the abort is in process run onstat -u and look for sessions with the rollback flag set. Also the abort message in the log shows the transaction address which should match the first column entry for the session involved. Running onstat -g ses <sessid> with the session id from column 3 from the -u report will show you the PID and query. > 2) what are the > ways by which we could avoid long txn abort (eg. say by increasing the > logical logs or by changing some informix parameters.) Increase logs, avoid the causes of long transactions (see 3). > 3) what care should be > taken while coding such that the code doesn't cause such long txns? There are two kinds of long transactions: o Large transactions - These involve the insert/update/delete of HUGE numbers of rows in a single transaction such that either the time involved in completing the transaction allows the logs to fill from other transactions or the shear volume of the transaction itself fills the available log space triggering the rollback abort. Solutions? A) Add more log space to accommodate normal transaction size and B) Reduce the size of transactions by adding intermediate COMMITS (this may require changing cursors to WITH HOLD status or restructuring the UPDATE or DELETE see the source of my dbdelete.ec utility for two different ways to break up huge deletes and of my dbcopy.ec for ways to break up inserts). o True long transactions - These are caused by applications that begin a transaction and wait a long time to complete it. The situation is typical of interactive applications that begin a transaction, either explicitely or through a CURSOR FOR UPDATE, present one or more rows for a user to view and modify and wait for the user to initiate a commit or rollback manually (this can happen in dbaccess BTW). These are poorly designed because the user can decide to go to lunch, go home, go on vacation without ever commiting the transaction. Adding logs will not solve this problem but may alleviate the symptom temporarily by allowing longer lunches without rollback aborts. The solution is to rewrite the application to use a timestamp or other verification scheme to prevent users from stomping on each other's changes rather than relying on locks. Art S. Kagel