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; Hi Madeline, Still gnawing, terrier-like, at this problem, here's something I found for OL 7.20 (should be similar for 5.0, but check). Your problem is related to two aspects of Informix behaviour. 1. When does OL react to LTXHWM & LTXEHWM? 2. What is the size of the Logical Log Records (LLR) for key sql transactions? Item 1 has been discussed at length already. Note that if your machine is fast, OL could be well past the LTXHWM threshold before the long trx abort starts. Also, if the number of logs you have is below 10, expect surprises. I did some investigation on Item 2 and found the following. 1. INSERT (fairly predictable) LLR is record size + header 64 bytes LLR for each index is index size + overhead (about 60 bytes) N.B if varchars exist, the LLR is squeezed appropriately. 2. DELETE (still in a grey suit with bowler!) LLR is record size + header 64 bytes LLR for each index is index size + overhead (about 60 bytes) N.B varchars squeezed appropriately. 3. UPDATE (most interesting!) 3.1 AN UPDATE WHICH CHANGES NOTHING CREATES NO LLR. For example, "update junk set field1= 'junk1'" will create no LLRs if field1 is already 'junk1'. However the message '1000 rows updated' will still appear. LLRs will only be created for records matching the where clause in an update WHICH ACTUALLY CHANGE. 3.2 THE SIZE OF THE LLR VARIES WITH THE SIZE OF THE CHANGE. In fact, the formula for calculating the size of an LLR during an update is A+B+C (in bytes) where A,B & C are as follows A. (SUM (larger of before or after image of CHANGE in each column) rounded upwards to make it an even number) * 2 B. (Number of changed columns - 1) * 2 C. Fixed header of 64 'A' needs further elaboration. Point 1 : Columns which do not change are ignored in the LLR. Point 2 : Among columns which do change, only the change in that column is logged. e.g if field1 (char 100) is changed from 'abcde' to 'abcxy', the value of A is dependant on only the change 'de' to 'xy'. The value of A would be 2 * 2 = 4 and the LLR record, assuming only field1 was updated, would be 4 + 0 + 64 = 68. Interestingly, a change from 'abcde' to 'xycde' would have an indentically size LLR - only the change 'ab' to 'xy' is logged. 4. INDEXES DURING UPDATE. If any column of the index changed in any way, 2 LLR are created for each index row - 1 delete and 1 insert. Sizes are as if the parent row was deleted and inserted i.e. index size + header of 64 bytes squeezed for varchars. 5. ROLLBACK LLRs - exactly 40 bytes ---- End of Investigation : Can somebody confirm? ------- Getting back to Madeline's problem... Having read Miroslav's comment, I have to agree that what he mentions seems a strong possibility. However... The LLR for each row updated by your transaction above could be as low as 68 bytes (1>2*2 + 0 + 64) (you need to check this in OL v 5; v7 has the onlog utility which shows the length of the LLR). With LTXEHWM at 60, a fast machine and a low total log size, OL could be commencing rollback after crossing the threshold of 63% (68 basic LLR + 40 CLR) - which means the rollback does not have enough space to complete. You could lower LTXHWM/LTXEHWM to 'significantly' lower values, 'slow' down your machine by hitting the statements in batches, or increase the log size so that OL can 'catch' the long trx before it reaches 'the point of no return'. You can also increase the content of your update so that your LLR record size increases, changing the threshold upward (as CLR is constant). I'd be interested in knowing how you fare. Bye, ----------------------- Rudy Fernandes GIC, Kuwait OL 7.20UC4, 4GL 6.04UC1 -----------------------