| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Weired problem when using serializable transaction in 10g
And I've just run a similar test on 10g from SQL*Plus without seeing any problem.
But if the OP were running the deletes in different sessions, the second session would be stuck waiting for the first session to commit.
The "?" is significant, perhaps - was the bind variable somehow set to a different value on the second call (I'm assuming that the question mark is an example of Java mechanism for identifying binds).
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st "VC" <boston103_at_hotmail.com> wrote in message news:SNDzc.108867$Ly.856_at_attbi_s01...Received on Tue Jun 15 2004 - 10:48:42 CDT
> 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.
>
 |
 |