Re: Informix vs. Sybase vs. Oracle vs. (gasp) MS SQL Server

In article <347E2D7B.4B74@agd.nsw.gov.au>, Anthony Mandic <no_sp.am@agd.nsw.gov.au> writes >David Williams wrote: >> >> Anthony Mandic writes: >> >David Williams wrote: >> > >> >> It think he means say 10 detail rows, in the screen array the user >> >> updates 5 , deletes 2, inserts 3. What SQL do you generate? >> > >> > I remember when I started of years ago and I used to do this >> > (until I learnt better). Well, since its SQL you're generating >> > you should be able to do it a row (and lock) at a time. >> > >> But what if the user wants to be able to undo all the changes they >> made since they entered the screen? > > Er? Perhaps I didn't make myself clear enough. I'm not advocating > that you do the changes immediately. I should have said after the > user commits, you process the changes. Doing it concurrently is a > nightmare. Of course, there's no easy way to roll back after the > commit. > So if the user deletes 1000 rows, where do you store them. Not in the screen array because they are visible.... what if the user deletes, then inserts a new row in the same entry in the array? >> Users goes into screen, deletes 20 rows and then want to undo >> everything. If you commit after each delete you cannot undo it, >> if you don;t commit you hold locks... > >[snip] > >> >> b) Someone does a kill -9 on your process when it has open locks? >> > >> > Same thing. You can clear it. Its not that big a deal. >> > >> It is.. I've had users complain the locks are held and call the system >> CRAP. The users demand a tool to clear the locks. Then they >> >> a) say why can't the application clear the locks automatically. If >> informix does I don't see why your application can't. > > My last application did. > >> b) how do DBA indentify stale locks. > > Check against existing user connections. I had a stored procedure How? Session ids can wrap and be reused... > to do this. If the user responsible for the lock isn't connected > or has a different process id (or whatever identifier you'd use) > then the lock is stale. My app would have done the checking if the Process ids wrap. User ids are now reliable since user can have >1 connection....only the server can deceide for certain if a lock is stale... > user reconnected, otherwise priveledged users had another app that > would manage it. Now of this is really that hard to do and there > wasn't much need for DBA intervention. Although I'll admit I used > to monitor the number of locks from time to time to check how busy > the users really were, etc. > >> c) DBAs delete the wrong users locks...then things get really >> interesting when two users get the ability to update the same rows. >> They both hit the "DO IT" key at different times and one user >> "loses" their updates. Then you get support calls when a user >> complains of bugs in the application as it "failed to update the >> data and gives no warning". You can't reproduce the bug and users >> say they can't reproduce it on demand as "it only happens >> occasionally". > > Tisk, tisk. This would happen if it were just deleted manually. > The way I used to do it was thru a stored procedure that did all > the correct checking. (Either that or I'd run the lock checking > app myself.) > >> d) DBA's say "why do we have to bother clearing out locks each time >> we reboot Online? (This from a site with one crash per day for 6 >> months before Informix supplied a bug fix). > > Data consistency/integrity. End users could pick up where they > left off by finding their last lock. The app also stored current > info on the client side (via files). This minimised data loss > (critical when its a paperless office. Data came in over the > phone). the client cannot gurantee to remember about stale lock, what happens if they power supply fails on the client (or the cleaner pulls the plug to plug in her Hooever - it has happened). > >> >> YOUR CLIENT PROGRAM CANNOT GUARANTEE LOCKS ARE RELEASED IF A CLIENT >> >> PROGRAM DIES. >> > >> > I wouldn't want it to. The 'soft lock' is a useful debugging tool >> > in this very case. How do you debug your client apps otherwise >> > if a user says "Oh, it just died.". Most users can't tell you >> > anything useful, so you have to rely on your wits. Knowing >> > where they were up to tells you where the app was when it died. >> Debug logs,screen messages. >> So does Informix 4GL, with startlog() function, all errors are t >> trapped by the Informix runtime library and logged. > > Good, thats the correct approach. Except that it only relates > to the server side of things. You'd have to rely on core dumps > (if on UNIX) and/or a debugger to debug a client app. Note that > there are two basic scenarios, the app failing or the app working > but doing the wrong thing. Which is harder to resolve? > Either is easy with debug logs. Switch on, get a trace, debug it. Most debugging can be narrowed down to one screen operation by the user, plaster with debug and that 's it. >> >> Agreed, have a little as possibly in the transaction. >> > >> > Do you mean have little data or just keep the transaction to as >> > short as it should be? >> Both, update as few rows and columns as possible and avoid selects >> within a transaction. > > Yes to the latter. I think the former really depends on the business > model. Sometimes it just can't be helped (or can it?). Rows can, columns can (prepare updates) but I usually do tables rather then columns. > >-am -- David Williams