Long Transaction

I am experiencing a long transaction problem when running a stored procedure. The procedure is taking rows from a view, inserting them into a table and then updating them. The table is used for reporting purposes and contains about 60,000 rows in all, but only about 1000 rows are involved when the procedure is run. The procedure runs fine for 3 months worth of data but fails with error 458 (long trans) for longer time spans ie more rows. I have tried tweaking a few parameters, etc but have not had any joy. Here is what I have and what I have done: 1. Platform Windows 2000, Informix 9.21 TC6R1 (no comments from you non-windows guys!) 2. Physdbs was increased to 50MB 3. Logfiles 99, Logsize 80MB 4. LTXHWM 50 5. LTXEHWM 60 6. maxlogsp 6050192 (queried from syssesprof) This seems to indicate that I am not hitting the logs limit. 7. Other procedures work quite happily with lots of rows. Especially the week-end routine which inserts data into the table in question. On the production server it seems that the oninit process is actually killed when the procedure is run a second time and they have to reboot the server. They obviously do want to try to reproduce this and so I cannot be sure of this. I look forward to your help folks. Kind Regards Ian Logan

----LNX_Tue_Sep_16_2003_13:49:21_V3.33-- Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >Datum: 2003.09.16 12:29:47 >Sender: IAN LOGAN <ian.logan@straker.com> > >I am experiencing a long transaction problem when running a stored pro >cedure. > >The procedure is taking rows from a view, inserting them into a table an= d >then updating them. The table is used for reporting purposes and contain= s about 60,000 >rows in all, but only about 1000 rows are involved when the procedure is= >run. The procedure runs fine for 3 months worth of data but fails with e= rror 458 >(long trans) for longer time spans ie more rows. > >3. Logfiles 99, Logsize 80MB = onstat -l shows 99 logfiles with 80 MB each? Therefore you have 8 GB lo=gspace - that's a lot!? or is 80 MB the sum of all your logfiles? = Anyway, what amount of data do you expect to be worked on in one transact= ion roughly ? row size \\\\* # inserts + row size \\\\* # updates + something extra f= or indexes ? = > 4. LTXHWM 50 = LTXHWM - that means your transactions must be smaller than half of your l= ogspace (if they are running alone on the server) ? NEVER touch the LTXHWM without readingthe = documentation about it! = If you think your transaction should fit into the (half) logspace: Isyour= transaction running alone (exclusive), or are running a lot of other transactions at the same time? They share t= he logspace! If other transactions run simultaniously - can you run your procedure exclusively to check if i= t fails in that case, too? = > 6. maxlogsp 6050192 (queried from syssesprof) This seems to indicate th= at I am not hitting the logs limit = Maybe it's a 'time' problem (or 'total load' problem): Your transaction i= s running very long and other transactions fill the logspace? = = Check your procedure - does it use indices where appropriate (for updates= etc.) ? = Regards, Andreas ------------------------------------------------ SPAR Oesterreichische Warenhandels-AG Hauptzentrale Europastra=DFe 3 A-5015 Salzburg = Telefon : +43 662 4470 24423 E-Mail : Andreas.KUTSCHE@spar.at Internet: http://www.spar.at ------------------------------------------------ = ----LNX_Tue_Sep_16_2003_13:49:21_V3.33----

Hi Ian, If you can, you could set the logging database as no buffer before run the sp and restore the original status later. Regards Paola PD, can you confirm me a message reception?. Quoting IAN LOGAN <ian.logan@straker.com>: > I am experiencing a long transaction problem when running a stored > procedure. > > The procedure is taking rows from a view, inserting them into a table and > then updating them. The table is used for reporting purposes and contains > about 60,000 rows in all, but only about 1000 rows are involved when the > procedure is run. The procedure runs fine for 3 months worth of data but > fails with error 458 (long trans) for longer time spans ie more rows. > > I have tried tweaking a few parameters, etc but have not had any joy. Here is > what I have and what I have done: > 1. Platform Windows 2000, Informix 9.21 TC6R1 (no comments from you > non-windows guys!) > 2. Physdbs was increased to 50MB > 3. Logfiles 99, Logsize 80MB > 4. LTXHWM 50 > 5. LTXEHWM 60 > 6. maxlogsp 6050192 (queried from syssesprof) This seems to indicate that I > am not hitting the logs limit. > 7. Other procedures work quite happily with lots of rows. Especially the > week-end routine which inserts data into the table in question. > > On the production server it seems that the oninit process is actually killed > when the procedure is run a second time and they have to reboot the server. > They obviously do want to try to reproduce this and so I cannot be sure of > this. > > I look forward to your help folks. > Kind Regards > Ian Logan > > > > ------------------------------------------------- This mail sent through IMP: http://mail.info.unlp.edu.ar/

Regarding long transactions... A long transaction is defined as "1. still open (uncommitted), and 2. starting in the oldest log". One of the sanity checks we do at log switch is to determine if the logs full surpasses the LTXHWM, defaulting to 50%. If so, we "take over" the long transaction(s), and start rolling it back. While this in question transaction (the one you have dug into a bit) is running, all of the other transactions are there as well, and in turn, also filling up log space. So the "size" of this transaction isn't the only factor - everyone contributes to filling the logs. The question comes up ... "are long transactions time-based, or activity-based?" The answer here is "yes". Imagine the scenario that someone inserts one row (and it's logged to the oldest log). He then does nothing. Meanwhile, the other transactions fill the logs to 50%...this one-row insert will be a long transaction..it's still open, and it started in the oldest log. The reason we have to deal with it is because when we circle around to re-use that log, we can't since there is an open transaction in it. So we attempt to roll it back by placing CLR (compensation log records) in the logical logs to undo what he did...in this case, just 1 CLR for 1 row. Meanwhile, people are still writing to the logs. If we cross the LTXEHWM (e is for exclusive), AND we are still rolling back a transaction (doubtful in this scenario, but possible), we give "exclusive" access to the rolling back transaction, effectively suspending the other "normal" txs. If we can't get the rollback done within the remaining log space, we circle and can't write to the oldest log, and the engine hangs. HTH - Mark. Mark Scranton Principal Consultant/Teacher IBM Denver IBM Software Group - Data Management Office: 303-773-5067 Cell: 303-929-0914 email: mscranto@us.ibm.com "Andreas.KUT...." <andreas.kutsche@ To: ids@iiug.org spar.at> cc: Sent by: Subject: Antw: Long Transaction [1881] forum.subscriber@ iiug.org 09/16/2003 05:52 AM ----LNX_Tue_Sep_16_2003_13:49:21_V3.33-- Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >Datum: 2003.09.16 12:29:47 >Sender: IAN LOGAN <ian.logan@straker.com> > >I am experiencing a long transaction problem when running a stored pro >cedure. > >The procedure is taking rows from a view, inserting them into a table an= d >then updating them. The table is used for reporting purposes and contain= s about 60,000 >rows in all, but only about 1000 rows are involved when the procedure is= >run. The procedure runs fine for 3 months worth of data but fails with e= rror 458 >(long trans) for longer time spans ie more rows. > >3. Logfiles 99, Logsize 80MB = onstat -l shows 99 logfiles with 80 MB each? Therefore you have 8 GB lo=gspace - that's a lot!? or is 80 MB the sum of all your logfiles? = Anyway, what amount of data do you expect to be worked on in one transact= ion roughly ? row size \\\\* # inserts + row size \\\\* # updates + something extra f= or indexes ? = > 4. LTXHWM 50 = LTXHWM - that means your transactions must be smaller than half of your l= ogspace (if they are running alone on the server) ? NEVER touch the LTXHWM without readingthe = documentation about it! = If you think your transaction should fit into the (half) logspace: Isyour= transaction running alone (exclusive), or are running a lot of other transactions at the same time? They share t= he logspace! If other transactions run simultaniously - can you run your procedure exclusively to check if i= t fails in that case, too? = > 6. maxlogsp 6050192 (queried from syssesprof) This seems to indicate th= at I am not hitting the logs limit = Maybe it's a 'time' problem (or 'total load' problem): Your transaction i= s running very long and other transactions fill the logspace? = = Check your procedure - does it use indices where appropriate (for updates= etc.) ? = Regards, Andreas ------------------------------------------------ SPAR Oesterreichische Warenhandels-AG Hauptzentrale Europastra=DFe 3 A-5015 Salzburg = Telefon : +43 662 4470 24423 E-Mail : Andreas.KUTSCHE@spar.at Internet: http://www.spar.at ------------------------------------------------ = ----LNX_Tue_Sep_16_2003_13:49:21_V3.33----

Or the server adds dynamic logs before hanging - depending on version (9.30 up?) and configuration (dynamic logs enabled). -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" |---------+----------------------------> | | Mark | | | Scranton/Denver/I| | | BM@IBMUS | | | Sent by: | | | forum.subscriber@| | | iiug.org | | | | | | | | | 09/23/2003 02:08 | | | PM | |---------+----------------------------> >------------------------------------------------------------------------------- --------------------------------------------------------------| | | | To: ids@iiug.org | | cc: | | Subject: Re: Antw: Long Transaction [1916] | >------------------------------------------------------------------------------- --------------------------------------------------------------| Regarding long transactions... A long transaction is defined as "1. still open (uncommitted), and 2. starting in the oldest log". One of the sanity checks we do at log switch is to determine if the logs full surpasses the LTXHWM, defaulting to 50%. If so, we "take over" the long transaction(s), and start rolling it back. While this in question transaction (the one you have dug into a bit) is running, all of the other transactions are there as well, and in turn, also filling up log space. So the "size" of this transaction isn't the only factor - everyone contributes to filling the logs. The question comes up ... "are long transactions time-based, or activity-based?" The answer here is "yes". Imagine the scenario that someone inserts one row (and it's logged to the oldest log). He then does nothing. Meanwhile, the other transactions fill the logs to 50%...this one-row insert will be a long transaction..it's still open, and it started in the oldest log. The reason we have to deal with it is because when we circle around to re-use that log, we can't since there is an open transaction in it. So we attempt to roll it back by placing CLR (compensation log records) in the logical logs to undo what he did...in this case, just 1 CLR for 1 row. Meanwhile, people are still writing to the logs. If we cross the LTXEHWM (e is for exclusive), AND we are still rolling back a transaction (doubtful in this scenario, but possible), we give "exclusive" access to the rolling back transaction, effectively suspending the other "normal" txs. If we can't get the rollback done within the remaining log space, we circle and can't write to the oldest log, and the engine hangs. HTH - Mark. Mark Scranton Principal Consultant/Teacher IBM Denver IBM Software Group - Data Management Office: 303-773-5067 Cell: 303-929-0914 email: mscranto@us.ibm.com "Andreas.KUT...." <andreas.kutsche@ To: ids@iiug.org spar.at> cc: Sent by: Subject: Antw: Long Transaction [1881] forum.subscriber@ iiug.org 09/16/2003 05:52 AM ----LNX_Tue_Sep_16_2003_13:49:21_V3.33-- Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable >Datum: 2003.09.16 12:29:47 >Sender: IAN LOGAN <ian.logan@straker.com> > >I am experiencing a long transaction problem when running a stored pro >cedure. > >The procedure is taking rows from a view, inserting them into a table an= d >then updating them. The table is used for reporting purposes and contain= s about 60,000 >rows in all, but only about 1000 rows are involved when the procedure is= >run. The procedure runs fine for 3 months worth of data but fails with e= rror 458 >(long trans) for longer time spans ie more rows. > >3. Logfiles 99, Logsize 80MB = onstat -l shows 99 logfiles with 80 MB each? Therefore you have 8 GB lo=gspace - that's a lot!? or is 80 MB the sum of all your logfiles? = Anyway, what amount of data do you expect to be worked on in one transact= ion roughly ? row size \\\\* # inserts + row size \\\\* # updates + something extra f= or indexes ? = > 4. LTXHWM 50 = LTXHWM - that means your transactions must be smaller than half of your l= ogspace (if they are running alone on the server) ? NEVER touch the LTXHWM without readingthe = documentation about it! = If you think your transaction should fit into the (half) logspace: Isyour= transaction running alone (exclusive), or are running a lot of other transactions at the same time? They share t= he logspace! If other transactions run simultaniously - can you run your procedure exclusively to check if i= t fails in that case, too? = > 6. maxlogsp 6050192 (queried from syssesprof) This seems to indicate th= at I am not hitting the logs limit = Maybe it's a 'time' problem (or 'total load' problem): Your transaction i= s running very long and other transactions fill the logspace? = = Check your procedure - does it use indices where appropriate (for updates= etc.) ? = Regards, Andreas ------------------------------------------------ SPAR Oesterreichische Warenhandels-AG Hauptzentrale Europastra=DFe 3 A-5015 Salzburg = Telefon : +43 662 4470 24423 E-Mail : Andreas.KUTSCHE@spar.at Internet: http://www.spar.at ------------------------------------------------ = ----LNX_Tue_Sep_16_2003_13:49:21_V3.33----