Re: Why use transactions in SE?

Dear Bryan, While you seemed to cover most of the bases on the pros and cons of transaction logging in the SE. I will throw in my two cents worth. I have been developing informix-based financial apps for small companies for the last 13 years, primarily with the SE. Even though most of our sites are small, 2 to 50 users in the financial app, we still elected to use transaction logging. The primary purpose for the logging in our apps is for data entry within a specific accounting journal screen (typically master-detail relationships) and with the batch posting routines which move the journal detail to a centralized ledger. In the data entry screens, we have implemented the following scenario for using the transaction logging which has worked well for us: 1. user queries rows 2. user chooses modify 3. app begin work 4. app declares update cursor on selected rows (pessimistic locking) - this will typically lock anywhere between 2 and 150 rows 5. if update cursor fails, then message presented to user that record is being updated elsewhere. 6. if update cursor succeeds, then selected rows are redisplayed with current values (just in case of modification between query and modify). 7. user modifies rows and presses <ESC> (or other defined accept key) 8. rows are updated in tables 9. sqlerr is checked upon each modification/insert and if error occurs then transaction rolled back 10. if all successful then transaction committed. While a bit of work, this scenario has been working well for us, with little problem with user perception of response time, even on small systems with a single disk. With the batch posting routines, it is much simpler, in that we usually lock the tables in share mode, do the mass insertion/deletions (typically 100 to 5000 rows in a single journal posting), checking the sqlerr code along the way and doing the appropriate rollback or commit. Of course, in summary, it really depends largely upon the app in question and the likely hood that problems will occur if you don't use the logging feature. Of course, even if you don't use the logging feature, you typically will have to produce code which is going to check the status of modified/inserted/deleted rows to see if they were successfully done and then have the code in place to appropriately deal with the cases where something does go wrong. While originally we did think we were going to have a high likely hood that multiple users would be attempting to modify/delete the same rows at approximately the same time, we found that in the small companies that we deal with, it happens quite frequently, so the pessimistic locking and logging have saved us quite of bit of work otherwise. Anyway, hope this helps in your decision. Good Luck, Bill Bounds Director of Software Development Echo Management Group billb@echoman.com