Re: serializable transaction problem

Here is test case:

/* session 1 time 1 */
SQL> alter session set isolation_level = serializable; Session altered.

/* session 2 time 1 */
SQL> alter session set isolation_level = serializable; Session altered.

/* session 1 time 2 */
SQL> update a set a = 'aa' where id = 1;

1 row updated.

SQL> select * from a;

       ID A
--------- ----------

        1 aa
        2 b
        3 c

/* session 2 time 2 */
SQL> update a set a = 'bb' where id = 2; update a set a = 'bb' where id = 2

       *
ERROR at line 1:
ORA-08177: can't serialize access for this transaction

SQL> delete from a where id = 2;
delete from a where id = 2

            *
ERROR at line 1:
ORA-08177: can't serialize access for this transaction

SQL> insert into a values ( 4, 'd');

1 row created.

/* session 1 time 3 */
SQL> commit;

Commit complete.

/* session 2 time 3 */
SQL> commit;

Commit complete.

/* session 1 time 4 */
SQL> insert into a values ( 6, 'f');

1 row created.

SQL> commit;

Commit complete.

/* session 2 time 4 */
SQL> insert into a values ( 7, 'g');
insert into a values ( 7, 'g')

            *
ERROR at line 1:
ORA-08177: can't serialize access for this transaction

/* end of test case */

As you can see, if one sesion update record, other session could not update or delete other record in the same table. In some circumstances is still possible insert into table, but in some other circumstances not. Insert is misterious, because in the same scenario I was once able insert from other sesion, and in second time not. I am maybe missing something. I would greatly appreciate explanation of this behavior or statement, that in Oracle 8 it works fine. I have no chance to proof if it works fine in Oracle 8 or not by myself.

Thanks,

    Stan

Thomas Kyte wrote in message ...
>A copy of this was sent to "Stanislav Benda" <si_bendovi_at_hotmail.com>
>(if that email address didn't require changing)
>On Thu, 23 Dec 1999 11:24:10 -0600, you wrote:
>
>>alter session set isolation_level = serializable;
>>
>>change isolation level, so there is no need to explicitly issue locks
>>anymore. Problem is, that "automatic" locks, locking whole table, so it
>>decreasing concurency to make serializable transactions virtually
unusable.
>>I did following experiment with 7.3.4. Does all versions having the same
>>problem, or is it getting better in 8?
>>With 7.3.4 behavior would using serialization kill almost all transactions
>>and make server unusable, because of too many serialization conflicts.
>>Imagine working with table with one million rows and any access to single
>>row, would lock out access to rest of 999999 rows.
>>
>
>alter session set isolation_level = serializable;
>
>does *not* lock tables. setting
>
>serializable=true
>
>in the init.ora would -- allowing only for 1 updater at a time per table.
>
>isolation_level = serializable is highly concurrent (we use it for our
TPC-C's)
>
>>
>>Behavior is:
>>
>>One session do not see changes in database (DML) which are done by other
>>session, even if those are commited. This sounds very cool, but I did some
>>experiments:
>>
>>it is true, that one session do not see any, even commited, changes from
>>other sessions. It is true, that session could not issue any DML command
>>which touching data modified by other session. In the other words:
>>
>>Transaction one change one record and do not commit.
>>Transaction two try to change the same record and this update immediately
>>fail. This is very good, maybe better than GemStone aproach, because I
have
>>error immediatelly, so no problems with finding what was wrong later on
>>commit.
>>
>>Unfortunately, there are side effects:
>>
>>If some other transaction than mine after my commit or rollback updated,
>>inserted or deleted any record in table "a" and regardles if this
>>transaction is commited, mine transaction could not do any insert, update,
>>delete into table "a". Only chance is to commit or rollback and try again.
>>But this need again interaction with user to check if user is still
>>interested in this transaction. In the other words, serialization is
>>implemented by some kind of underlined table level locking, which
decreasing
>>concurency a lot.
>>
>
>can you post a test case... some scripts to run in sqlplus?
>
>>Stan
>>
>>
>>
>>
>
>
>--
>See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
>Current article is "Part I of V, Autonomous Transactions" updated June
21'st
>
>Thomas Kyte tkyte_at_us.oracle.com
>Oracle Service Industries Reston, VA USA
>
>Opinions are mine and do not necessarily reflect those of Oracle
Corporation Received on Thu Dec 23 1999 - 14:19:57 CST