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

David Williams wrote:
>
> Anthony Mandic writes:
>
> >> -- After the user exits we update all detail recs, probably by deleting
> >> them first, and then inserting whatever the user has typed in.
> >
> > What an overhead, delete followed by insert. An in situ
> > update is far more efficient. Use it wherever possible.
> >
> 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.

> You could try to generate the needed inserts/updates/deleted but it's
> too complex and error prone, just delete and insert.

	I disagree. Its not hard, unless you're lasy or incompetent.
	From a cost point of view, its also more cost effective.

> Oh, you mean soft locking where you have a database table that
> contains the locks e.g.

        Yep.

> create table lock_table
> (
> entity char(10) (effetively table name),

	object_key interger,
	user_id ...

	You only need to identify the core table's key.



>       entity_key integer (primary key)


>       transaction_id (uses to remove locks for a given transaction just

>                       before commiting).

>       ) 

	And another user says "Why has this object been locked for
	N hours?". Its the same as when someone sets a server lock
	and goes off to lunch and lock's their PC's screen. At least
	you can find the culprit "soft lock" and fix things up.

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

> c) A power failure occurs whilst you have open locks?

	Same again. You can list all of them and generate a report.
	If the same happens to a server with server locks, how do you
	know who was working on what? In other words, you'd either
	have just one big server transaction or start worrying about
	data consistency. At leat with "soft locks" you have some
	hope of tracking them down.
 

> A database server will cleanup locks if a client dies or when
> restarted if the server dies.

	Yes, unfortunately, and you're stuck if you need to track
	down who was doing what to what/who.

> 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.
	This feature helped me on numerous occasions in fixing a badly
	written application.

> Also which is faster a
>
> BEGIN WORK
> INSERT INTO lock_table..
> COMMIT
> (including flushing 'dirty' data to disk and transaction logging
>
> or the database server putting an entry into an in memory lock table?

	An extra insert and delete doesn't hurt. I think the overall
	advantages outweight the disadvantages. Sure its pain to have
	to delete stale ones that got left around after a crash and
	don't mark an active transaction, but then users can delete
	them themselves with a well-written app.



> >       Thats the reason why server-generated locks should be fast,


> >       and not held for an indefinite period. the initial select

> >       should NOT be part of the transaction. This rule should

> >       apply to all locking methods, including row level.

	Do you mean have little data or just keep the transaction to as
	short as it should be?

-am Received on Thu Nov 27 1997 - 00:00:00 CET