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

In article <347BCB78.1025_at_agd.nsw.gov.au>, Anthony Mandic <no_sp.am_at_agd.nsw.gov.au> writes
>Snorri Bergmann wrote:
>
>> Ok. Here is an easy one. Only involves 2 tables (master and detail).
>> This is of course very simlified and I have not checked the syntax :-)
>
>[DDL definitions snipped]
>
>> Now what the app does is it allows users to select a record from the
>> master table, get all child records from the detail table and allow the
>> users to add/delete/modify those records as needed. After the user exits
>> it updates the
>> master table by selecting the sum of cost from the detail table. The sql
>> statements involved are something like this: (assume everything is under
>> transaction control)
>>
>> -- We need to lock the master table so nobody else will update it while
>> we are.
>
> Here is the crux of your problem. "We need to lock".
> Its this mindset that doesn't allow you to see a better
> solution, so you let your DB server do it for you.
>
 Thats's the idea, the DB server SHOULD do the locking. see below.
>> SELECT * FROM master WHERE id = ? FOR UPDATE;
>>
>> -- No need to lock the detail recs 'cause the master rec is locked.
>
> Yes, no need to lock. Its how you lock master thats the
> problem. I'll explain below.
>
>> SELECT * FROM detail WHERE id = ? ORDER BY line_no;
>>
>> -- 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?

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

>> -- Then we'll need to update the master table and release the lock.
>
> You can use a non-server generated lock instead. Then it
> just becomes a matter of updating the master table. The
> action of this update is also the action of releasing the
> lock. No other process is locked out of reading the record
> but others can't update or delete while that record is marked
> as being in use (provided that they observe the rules).
>
  Oh, you mean soft locking where you have a database table that contains the locks e.g.

   create table lock_table

      (
      entity char(10) (effetively table name),
      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  

1. you program SIGSEV's in the middle of a transaction with open locks?
2. Someone does a kill -9 on your process when it has open locks?
3. A power failure occurs whilst you have open locks?

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

  YOUR CLIENT PROGRAM CANNOT GUARANTEE LOCKS ARE RELEASED IF A CLIENT   PROGRAM DIES.   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?

>> If you have row level locking, the first select stmt will lock one row.
>> As the primary key is 4 bytes you can potentially put 4-500 keys on any
>> page (if you have 2K pages). In a page lock system (like Sybase and
>> MSSQL) the probability of a user trying to access one of the ~499 key
>> values another user has locked is pretty high.
>
> 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.

>> Page lock workarounds could be:
>>
>> 1) Make the primary key >1Kb, so you would only have one key per page.
>> (Disks are cheap, right?)
>
> Wrong approach. This is the BFI method favoured by those who
> just can't grasp the concepts.
>

 Agreed.
>> 2) Use optimistic locking. (So users who have been typing in for hours
>> get the message: Somebody else has modified this rec while you were
>> working. Please try again).
>
> Same wrong approach.
>>
>> 3) Swich to Informix :-)
>
> I feel tempted to state the same here, but I'd rather ask why
> some of Informix's top programmers have jumped ship and joined
> Orable? (According to what I've read recently in the trade
> papers)
>
>-am
 

-- 
David Williams