Re: DB server performance if long transaction happen

On Jan 28, 2008 1:22 AM, <falconsoon@gmail.com> wrote: > I have a long transaction (taking hours) with all tables is temp table > with no log and lock in exclusive mode, thus there will be no LOG > usage and less LOCK usage. Is this scenario will impact the database > server performace? FYI: IDS has a concept of 'long transaction' or LTX which is a Bad Thing(TM) and to be avoided whenever possible. However, it occurs when logs get full and a transaction still has records in the early logs. A classic example of how that can occur is if someone is editing a record with an update lock and they go to lunch (or away for a vacation) while other people are still busy. However, what you are talking about is not directly an IDS 'long transaction'. You are talking about a simple long-running transaction with minimal impact on the log. However, if you have a BEGIN WORK, there will be a record in the logical logs, and your transaction could become an LTX. As was discussed on another thread - either here or in the ids@iiug.org mailing list - there is almost always some log activity, so your long-running transaction can become an LTX. Your server performance while your transaction is running will not be affected beyond the workload imposed by your transaction running in competition with the workload from others. And the use of x-locks and temporary tables minimizes the resources used in many respects. So it should be quite efficient (as long as your x-locks don't annoy others). But it all ends up depending on what you do in the TX - and on whether your TX becomes an LTX (at which point, it may become a significant drain on the system, though unlogged temp tables minimizes the number of CLR (compensation log records) that have to be written to rollback the LTX). -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/ NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.