Re: Why a long transaction cannot rollback?? (2)

In article <01bc4418$a31e3120$LocalHost@madeline>, "Madeline Wu" <madeline@ms1.hinet.net> wrote: > > I test a long transaction in Online 5.0. > Only one user in this Online. That is me. > The long transaction is like this... > > begin work; > update table1 set field1='1'; > update table2 set field2='1'; > update table3 set field3='1';> : > : > : > update table50 set field50='1';> commit work; > > I set LTXHWM=50 & LTXEHWM=60. When logical logs > used 60%, this transaction began rollback. When > rollback used the last logical log, many > checkpoint happend. When the last logical log > used 100%, "The logical logs are full---Backup > is need." showed on the sys log file. No matter > I used Auto-Backup or Continue-Backup, I still > couldn't backup all full logical fogs. The > Online 5.0 cannot work anymore. > > I thought this Online 5.0 got some problems with > the AIX UNIX 3.2.5. So I test again on SCO UNIX > whit Online 5.0, the same thing happen. Why???? > It is a bug on Online 5.0? Or I cannot test like > that? How can I test a long trancation and > rollback normally? > > -- > > Madeline Wu from Taiwan....... Sorry for posting my answer so late, but I cannot get through because of problems with my Internet provider. I have read several answers, but I would like to return to original post. Is your OnLine version bellow 5.6? In versions up to 5.6. is a bug - I forgot its number - OnLine tries to do checkpoint and writes info about CKPT to logical log, but this checkpoint is not properly executed and is repeated again and again resulting in filling logical logs with CKPOINT records. It happens in most cases by my opinion. I fall into this situation two or three years ago - LTXHWM and LTXEHWM were set to 50 and 60. One user started massive update. His long transaction made many pseudo checkpoints and filled logical logs. I could not use archive because of it was too old ( I was not this system`s DBA!), so I needed to recover manually - patched logical logs, physical log and reserved pages information. Then I tried to repeat this situation (mad checkpoints during LTX rollback) on different versions (5.xx and 7.xx) and different platforms(HP, AIX, Solaris, SunOs, SCO, IRIX ...), contacted Informix support and got these results: Bug is fixed beginning with 5.06 and seems not to be in any versions 7.xx I have worked with. Bellow 5.06 is on all platforms I tested. Solution: Checkpoints begin at LTXEHWM. Increase interval between LTXHWM and LTXEHWM to ensure that long transaction will be rolled back in this interval. You can also decrease LTXHWM - for example set LTXHWM 50 or 40 and LTXEHWM 95. In normal cases, LTXHWM 50 and LTWEHWM 60 seem to be more then very good settings for me. Only CLR entries are written into logical logs during rollback (normally - without this bug) and, of course CKPOINT records for checkpoints forced by 75% full physical log or CKPTINTVL . CLR (Compensatory log record) takes 24 bytes in OnLine 5.xx, which is significantly less then any other log entry with 16 bytes long CKPOINT entry exception. I hope this helps. Regards, Miroslav Lorenc SSA Czech Republic -------------------==== Posted via Deja News ====----------------------- http://www.dejanews.com/ Search, Read, Post to Usenet