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

}From ilist@rmy.emory.edu Tue Apr 8 23:25 PDT 1997 }From: rferdy@kuwait.net (Rudy Fernandes) }Subject: Re: Why a long transaction cannot rollback?? (2) }Date: Wed, 09 Apr 97 03:54:03 GMT }To: informix-list@rmy.emory.edu }X-Informix-List-Id: <news.36343> } }"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? What is the full version number (5.0x.Uyz) where x is a digit, y is a letter (probably C) and z is a digit (probably 1). }This is exactly what should happen! LTXHWM=50 & LTXEHWM=60 are figures }for a multi-user environment (conservative figures, I should mention). No, it is not what should happen. }The logic is very simple. To rollback a transaction, the minimum required }space in the logical log is the amount used up for the transaction before }the rollback was initiated. Not quite, I think. My understanding is that one CLR (compensation log record) is written to the log during rollback for each log record generated by the LTX while it was running. The CLRs are 16 or 20 bytes each. If the size of each basic log record is smaller than 16 bytes, then you'd run into problems as you said, but every log record has some fixed overhead (12 bytes, I think), which means that if you are inserting 4-byte values, then you could have as much space used in CLRs as in the INSERT records. For UPDATEs, you get both the before and after images logged, so the space needed should always be at least as big as the CLR. }If you have LTXHWM at 50 and LTXEHWM at 60 in a single user environment, }then by the time OL is reacting to initiatiate a 'long trx' abort, your }transaction has already used more than 50% of the log. This means that OL }cannot rollback your transaction in the remaining available space. As indicated above, I don't think that is the reason. Question: when does OnLine test the fullness of the logs against the HWM values? Short answer: I don't know. ** SPECULATION ** Online might check the fullness of the logs against the HWM values only when it switches to a new log. Under this hypothesis, if you have 3 logs on your system, the first time that the logs are checked is at 33% full; the second time is at 66% full. The LTX{E}HWM figures might need to take the number of logs present in the system into account. If you have 32 logical logs, then you would get a check every 3% or so, which is unlikely to cause problems; if you have just a very few logs, then the implied granularity of the checks could more easily cause overflow problems. ** END OF SPECULATION ** Subsidiary questions (for Madeline): How many logs were present in your system? What was the schema of the tables you were updating? Did each table have the same schema? }Further, you cannot free any logs because they are all are involved }with an 'open' transaction. } }If you want to test how this works in a single-user environment, }LTXHWM and LTXEHWM should be reduced considerably. Try 5 and 10 }to be absolutely sure of a positive result. This should certainly work safely, but it is most useful if the objective is to ensure that the program doesn't crash unreasonably when an LTX occurs. If the objective is to ensure that the LTX{E}HWM values are set safely, then it isn't such a good idea. }Settings of 50 and 60 work fine in a multi-user environment because }the offending transaction would normally occupy only a fraction }of the logs involved since it started (because other transactions }are using the logs simultaneously). Thus, when OL goes into 'exclusive' }at 60, 40% of the log space would usually be enough to rollback the }offender, especially since it blocks most other potential log-fillers. Yours non-definitively, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>