Logical log usage and long transactions

Firstly, thanks to all who answered the mail "write cache hits slipping". Read caches are sitting nicely in th 99.4% mark. Write caches are still slipping, but I realise that I wasn't aggressive enough with my LRU settings, so that's something to look forward to at next reboot. And yes, I see now that my NETTYPE setting was totally off the planet. Now, to this query. But before that, my client's environment. ---------------- HP-UX 11.0 on a HP9000 server, IDS Workgroups Edition 7.30.UC9. Connection via shared memory. Legacy app. in 4GL-RDS 7.20, recently ported from the 4.1 series of the 4GL. We recently moved from Standard Engine. We use the default isolation level of "Committed Read". We have ten logical log files, each of 5 Mb size. The logical log buffer is 64K. We have left the LTXHWM parameter at 50% and LTXEHWM at 60%. The production database uses Unbuffered Logging. ---------------- Before going live with the new database server, I tested our Customer statements program on a copy of the production database - there was nobody else on the server at the time. At that point, there were no transaction statements at all. The statements program uses a handful of temporary tables for collation of output, and processing is moderately intensive. I ran it, and found about 80MB of log file got written to the disk backup of the log files. I had run this program before with similar results. I changed the statement program to put a transaction (begin work, commit work) around the processing of each customer (which it needs anyway!) and re-ran the program. It used only about 16MB of log space. My question is: why such a huge discrepancy in log file usage when we don't use transactions, as to when we do? I'd appreciate that there would be some overhead written to the log file at the start of each transaction, and in the before case, each SQL altering the database would be treated as a transaction. However, I find it hard to rationalise that the difference in the two scenarios would be do drastic. If anyone has any insights as to what the reason might be, and how that would affect application design, I'd be really grateful. ---------------- We have some old data entry screens (whose logic flow is quite hard to understand) giving us "Long transaction aborted" errors on a couple of occasions recently. In one instance, it occurred in a Stock Transfer screen, after a user moved quantities of six stock items from one warehouse to another. Hardly the sort of transaction activity to generate a long transaction, I would have thought. The code wraps the header and detail sections entry My question is: does this error really mean what it says, and I should shut up and just work out whatever screwy logic is causing this. Or, is there some insidious process going on that, if I was aware of that process, would get me faster to the point of resolution of this issue. Any insights that might help me resolve this would be gratefully received. ---------------- Thanks for the patience for sticking with this. Alnis Bajars. alnisb@colorcorp.com.au

Alnis Bajars wrote: [SNIP] > HP-UX 11.0 on a HP9000 server, IDS Workgroups Edition 7.30.UC9. Connection > via shared memory. Legacy app. in 4GL-RDS 7.20, recently ported from the > 4.1 series of the 4GL. We recently moved from Standard Engine. > > We use the default isolation level of "Committed Read". We have ten logical > log files, each of 5 Mb size. The logical log buffer is 64K. We have left > the LTXHWM parameter at 50% and LTXEHWM at 60%. The production database > uses Unbuffered Logging. > > ---------------- > > Before going live with the new database server, I tested our Customer > statements program on a copy of the production database - there was nobody > else on the server at the time. At that point, there were no transaction > statements at all. The statements program uses a handful of temporary > tables for collation of output, and processing is moderately intensive. > ran it, and found about 80MB of log file got written to the disk backup of > the log files. I had run this program before with similar results. > > I changed the statement program to put a transaction (begin work, commit > work) around the processing of each customer (which it needs anyway!) and > re-ran the program. It used only about 16MB of log space. > > My question is: why such a huge discrepancy in log file usage when we don't > use transactions, as to when we do? I'd appreciate that there would be some > overhead written to the log file at the start of each transaction, and in > the before case, each SQL altering the database would be treated as a > transaction. However, I find it hard to rationalise that the difference in > the two scenarios would be do drastic. If anyone has any insights as to > what the reason might be, and how that would affect application design, I'd > be really grateful. Oh, it makes perfect sense. If you do not open an explicit transaction then each SQL statement is treated as a singleton transaction and every transaction has a BEGIN WORK and either a COMMIT WORK or ROLLBACK WORK record in the logical logs. If the individual transactions are small inserts or deletes then you could EASILY need 6X the logspace without explicit transactions. > ---------------- > > We have some old data entry screens (whose logic flow is quite hard to > understand) giving us "Long transaction aborted" errors on a couple of > occasions recently. > > In one instance, it occurred in a Stock Transfer screen, after a user moved > quantities of six stock items from one warehouse to another. Hardly the > sort of transaction activity to generate a long transaction, I would have > thought. The code wraps the header and detail sections entry > > My question is: does this error really mean what it says, and I should shut > up and just work out whatever screwy logic is causing this. Or, is there > some insidious process going on that, if I was aware of that process, would > get me faster to the point of resolution of this issue. Any insights that > might help me resolve this would be gratefully received. Keep in mind that a LONG TRANSACTION is not neccessarily a BIG transaction. The LONG refers to the time between BEGIN WORK and COMMIT WORK and the amount of remaining logical log space as determined by the LTX parameters in the ONCONFIG file. This looks like your either: 1) user is beginning a transaction and leaving the terminal stand for a bit while he/she goes to lunch or checks the warehouse or whatever. OR 2) the application starts an explicit transaction before the user actually does anything, like immediately upon opening the data entry form, and so if the users has nothing to do for several hours the logs begin to fill from other transactions and finally this bogus non-transaction has begun in one log and there are 5 logs filled and so a long transaction is declared and it rolls back. But the app does not know about it until maybe even hours after that when the user finally has something to enter and tries to COMMIT. Definitely look into both work flow and the application design. Even if the problem is work-flow (ie user goes to lunch in the middle of a transaction) the application can be designed to NOT start a transaction until the user commits the work and then just verify that the data has not been modified by someone else in the interim. Art S. Kagel