Re: Is oracle 8 joking with me?

Okay, I think I've got it.
Comments included in your example.
I created a table with INITRANS = 3 for clarity, and dumped blocks at each stage.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

KengLao wrote in message <383A6A0F.327D70F9_at_macau.ctm.net>...


>

>

>Transaction 1

>  create table tablename ( rec# decimal(2), descri varchar2(10) );


>  alter session set isolation_level = serializable;

>  insert into tablename ( rec# ) values (1);

>  commit;


        The commit is a fast commit, not a full clean out commit.
        The insert/commit use up transaction slot 1, and leave an
        ITL reference of 1 on the the row.

        The following SELECT starts a new transaction.  Not visible
        in v$transaction, but visible as TADDR in v$session, and
        x$kctxb.




>  select * from tablename where rec#=1;

>






>Transaction 2

>  alter session set isolation_level = serializable;

>  insert into tablename ( rec# ) values (2);

>  commit;


    Another fast commit.
    The insert and commit use transaction slot 2, and
    and ITL of 2 on the row.



>

>  select * from tablename where rec#=2;



    The select starts a new (invisible) transaction.



>  update tablename set descri='record 2'  where rec#=2;



    The update makes the transaction visible.  This uses the
    third ITL slot - BUT - it performance a full clean out on the
    block, which cleans out the ITL list and updates the ITL
    marker on the first row and resets it to zero.  i.e. the state
    of the first row has been changed by this transaction.



>  commit;






>

>Transaction 1

>  update tablename set descri='record 1'  where rec#=1;


>  <<RA-08177: Cannot serialize access for this transaction>>

>


    Because another transaction has changed the row entry -
    even though that delay is ONLY a cleanout, and not a
    real data change.



Work-arounds:
    Don't do the select, just do the updates.

    If you select, commit before you try the update

    Create the table with multiple freelists so that the
    inserts (tend to) go into different blocks and therefore
    the updates don't clean out rows other than the ones
    that belong to the current session - this is a bit of
    a long shot, and the probability is that from time to
    time you will still move across blocks and hit the
    problem:  also if you have indices in place you won't
    be able to stop the problem appearing on index blocks.