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

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.

> 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
	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
	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).



> >>   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.

>

	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?



> >>    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?

	Yes to the latter. I think the former really depends on the business
	model. Sometimes it just can't be helped (or can it?).

-am Received on Fri Nov 28 1997 - 00:00:00 CET