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

In article <347E2D7B.4B74_at_agd.nsw.gov.au>, Anthony Mandic <no_sp.am_at_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