Re: performance of transaction isolation serializable / optimistic locking

Tricky question.

When you run 'serializable' you need to
allow at least one extra ITL slot on each relevant object (i.e. increase INITRANS).

If you set transaction serializable then try an update, I believe that Oracle may have to roll the target blocks back to
the SCN of the start of transaction in
order to check that no other transaction updated the row you are after between
the 'set transaction' and the update.

If the activity is scattered and not running at a high rate, the overhead could be
very small. If it is busy and focused the overhead could be large.

I suspect the optimum for optimistic locking (at least for single row) is your 'verified update' option, i.e.:

    select current_values_including version_column     where ...

    fiddle about on screen.

    update table
    set values = new_values,

        version_column = version_column + 1     where rowid =
    and version_column = value_retrieved;

This only does any serious work if the row can still be found, and doesn't require any special consistent read generation.

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html

Screensaver or Lifesaver: http://www.ud.com
Use spare CPU to assist in cancer research.




Tinou Bao wrote in message
<95d59091.0107021025.42d7614a_at_posting.google.com>...


>Hi,

>

>I realize that there is a performance hit when you use serializable

>transaction, but I'm looking for a better feel of how much the hit is.

>I also realize this is a vague question since it'll depend on the

>ratio of writes to reads, the likelyhood of conflict, etc., but what

>are people's experiences with serializable transaction.  Switching

>from default read committed to serializable what are "typical"

>performance hits you have experience.  It seems that since Oracle

>doesn't put read locks for either serializable or read committed the

>hit in oracle for serializable may be significantly less than compared

>to other databases that uses read locks. Is this true.  My issue is

>how to get optmistic locking. Seems that either to serialize the

>transaction or do verified updates.  If the hit isn't that big I'd

>rather not have to make my sql verified the updates (i.e., update

>table set attribute = 'value' where version = x).

>

>Much thanks.

>

>--

>Tinou Bao

>www.tinou.com