Re: Conversion: SQLBase to Oracle

On Mon, 25 May 1998 19:25:48 +1000, Michael Franklin <franklin_at_actonline.com.au> wrote:
>I have been asked to evaluate a client's future database needs. They
>currently use Centura (formerly Gupta) SQLBase v5.2.1 NLM which works
>fine for their current requirements (250-300Mb database, ~60 concurrent
>users). They are being asked to consider going to Oracle v8 and would
>like to know a rough idea of costs.
>
>The main problem as I see it is the different locking strategies adopted
>by the two vendors. Gupta uses a unique (hidden) column called ROWID
>which works like the timestamp in SYBASE and not at all like Oracle's
>ROWID (i.e. SELECT the record including the rowid, edit it and then
>UPDATE... WHERE rowid = :SavedRowID)
>
>People I have spoken to who use Oracle tell me that it doesn't support
>(or at least promote) this mechanism, but rather encourages the use of
>SELECT FOR UPDATE. I realise that Oracle supports row level locking,
>but what happens if someone selects a record and then goes off for
>lunch? This application has a high probability that the same record
>will be required by two users at the same time. Is there another
>mechanism, or have I missed something?

The secret is to let Oracle handle the locking, rather than manually overiding it to make a problem. If you must have repeatable reads within a transaction or absolutely cannot ever wait to write by two people to the same row, then you have to overide the defaults and carefully handle all situations.

The defaults:

Readers don't wait for writers of the same row.

Writers don't wait for readers (unless SELECT FOR UPDATE).

Writers only wait for writers if they attempt to write to the same row at the same time. So the problem of out-to-lunch can occur if someone does an update and doesn't commit/rollback right away. But the other person can still query it. Perhaps your app could timeout/rollback. Or minimize the time locked by doing a select, then update only when the person says "yes" to a correct screen, err if data has changed in meantime.

Oracle never escalates locks. This avoids deadlocks most of the time. Most other deadlocks can be avoided by always locking multiple tables in the same order.

See http://marshal.uwyo.edu/Oracle/DOC/server/doc/SCN73/ch10.htm#toc086

>
>Can anyone help please?
>
>Michael Franklin
>Covalent Systems
>

--
These opinions are my own and not necessarily those of Information Quest

or Pebble In The Sky                     http://www.informationquest.com

http://ourworld.compuserve.com/homepages/joel_garry jgarry@nospameiq.com
"See your DBA?"  I AM the @#%*& DBA!     Remove nospam to reply.  Sorry.