Re: Weired problem when using serializable transaction in 10g

Hello Oliver,

"Oliver Zeigermann" <oliver_at_zeigermann.de> wrote in message news:2j897uFu2kciU1_at_uni-berlin.de...
> I have a very weired delete problem that only occurs when isolation
> level is set to serializable.
>
> > delete from PROPERTIES p where p.VERSION_ID = ?
>
> deletes 10 rows while when I execute the same request *inside the same
> transaction*, again 10 rows are deleted.
>
> After that
>
> > select * from PROPERTIES p where p.VERSION_ID = ?
>
> returns 10 rows *inside the same transaction* as well.
>
> Obviously, nothing is removed at all until a invoke commit. Has anyone
> experienced something similar? Or has anyone any idea what might be my
> mistake? As a hint, a final commit does succeed as well...
>

Could you provide a full session transcript since what you are describing appear to be unlikely:

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jun 15 10:29:08 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production

SQL> create table t1(id int);

Table created.

SQL> insert into t1 select 10 from all_objects where rownum <=10;

10 rows created.

SQL> insert into t1 select 20 from all_objects where rownum <=10;

10 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> set transaction isolation level serializable;

Transaction set.

SQL> delete from t1 where id=10;

10 rows deleted.

SQL> delete from t1 where id=10;

0 rows deleted.

SQL> select * from t1;

        ID

        20
        20
        20
        20
        20
        20
        20
        20
        20
        20

10 rows selected.

SQL> Probably you are running your deletes/selects in *different* transactions, not the same one.

VC

> Cheers and thanks in advance,
>
> Oliver
Received on Tue Jun 15 2004 - 09:34:26 CDT