Re: Weired problem when using serializable transaction in 10g

Could you post the Java code that does deletes/inserts ? I assume the Java connection operastes in the 'autocommit-off' mode, correct ? Also, what Oracle version are you using ?

And lastly, did you try to reproduce the behaviour in SQLPlus in order to elminate or shift the problem to the database ?

VC

"Oliver Zeigermann" <oliver_at_zeigermann.de> wrote in message news:2j8h8uFuk4idU1_at_uni-berlin.de...
> Hi VC!
>
> Thanks for your help! I agree this actually is *very*, *very* unlikely,
> but is what actually happens.
>
> I connect to Oracle over JDBC and do not know how to create such a
> session transcript...
>
> I still think the stuff works in one and a single transaction as
> everything works well when in read committed mode. Additionally, both
> delete statements directy follow each other and are issued from the same
> connection. Just for testing I added a commit (done on the connection)
> between both delete statements. What does this tell me?
>
> Would it make sense to test it with another Oracle version?
>
> Cheers,
>
> Oliver
>
> VC wrote:
>
> > 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 - 10:10:36 CDT