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

In article <347D44A9.2674_at_agd.nsw.gov.au>, Anthony Mandic <no_sp.am_at_agd.nsw.gov.au> writes
>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.
>
  But what if the user wants to be able to undo all the changes they made since they entered the screen?

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

>> 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).
>> )
>>
>> OK what happens when you have commited entries into this table
>> (which you have to for other users to see them) and
>>
>> a) you program SIGSEV's in the middle of a transaction with open
>> locks?
>
> 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.
>
  onstat -k, lists locks, tables, rowids, sessionids.

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

1. say why can't the application clear the locks automatically. If informix does I don't see why your application can't.
2. how do DBA indentify stale locks.
3. 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".
4. 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).

>> 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.
>
  onstat -k list locks.

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

   So does Informix 4GL, with startlog() function, all errors are t    trapped by the Informix runtime library and logged.

   Or you can trap them yourself with WHENEVER ERROR CALL do_panic()    and a do_panic functon that calls the Informix errorlog() function    to log errors.

   If the error is bad your application SEGV's of course you get a core    dump.

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

-- 
David Williams