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. Received on Sun Nov 28 1999 - 16:04:27 CST