Aborting long transaction doesn´t rollback tx

Hi, Today we had the following messages in the log: Aborting Long Transaction: tx: 0xc0000003d86ea0a8 username: xxx uid: nnn Next, onstat -m showed : On-Line (LONGTX) The sessions seemed to continue working normally. For example, I verified that one table inserted new records. After a moment, this messages showed in onstat -m: On-Line (LONGTX) Blocked I found out which session was the tx 0xc0000003d86ea0a8 and I runned onstat -z sessid. The session was in transaction, but in status "cond wait" and only with 6 locks. After that, everything went back to normal. My question is: Why the engine not rollback the session automatically ? Are there best practices for avoiding "blocked" ? Unfortunately, I can not control the code of the applications that connect to the database My environment is IDS 11.70.FC8W1 running on HP-UX 11.31 Thanks in advance.

Hi Roger. Needing "onmode -z" in that situation is unusual. "Long Transaction Aborted" occurs once most of the logical logs have been consumed since a transaction was started: the logs cannot "wrap" and overwrite a log involved in an open transaction. This will happen either if: 1) a session has got stuck or data entry left unfinished; 2) excessive log consumption occurs in one transaction. There is not much you can do about the first other than to have an alert process to let you know when data has been left locked. To avoid the second, do not affect too many rows in a single transaction, but commit every few thousand. For example, use dbload for bulk INSERTS, Art Kagel's dbdelete tool for DELETE, and stored procedures for UPDATE. Regards, Doug Lawry

Roger, Doug explained it perfectly. There are also 2 things you can do, bearing in mind that you should be in control of the possible consequences: - add more logical logs ( raising the question: do you need just a few more or incredibly more) - long transactions are controlled by 2 parameters in your ONCONFIG file: LTXHWM (long transaction high water mark) and LTXEHWM (long transaction exclusive HWM), both are % of total logical log space. These control the triggering of a long transaction (when the transaction covers LTXHWM % of the total log space), and this one becomes exclusive rollback when it reaches LTXEHWM % of the log space. You may want to slightly increase those values, but this can be dangerous game. Adding more logs is generally easier and safer as long as you can have easily disk. As Doug said, this is all about data consistency and securiy. Eric

Also bear in mind that it may take a very long time to rollback a long transaction, sometimes sever hours. You can ensure that it is effectively rolling back using the onlog utility that will display the logical log contents. Once you found the right transaction #, you should see it is writing

A little supplement: You can watch the progress of the rollback with onstat -x. See the column "est. rb_time" of transaction list. The third column userthread points via onstat -u | grep userthread to the session_id.