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 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

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 23 1999 - 15:03:36 CST