| 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
In the test case you sent us - was the trace file the one generated by the actual code fragment attached ?
Your closing comments about 17 rows deleted and yet being returned by the Java code do not match the trace file - which says that one row was deleted in each case, and one row was fetched.
(Is the properties table an Object table ?)
-- 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 "Oliver Zeigermann" <oliver_at_zeigermann.de> wrote in message news:2jb3aiFvrhdmU1_at_uni-berlin.de...Received on Sun Jun 20 2004 - 10:09:05 CDT
> OK, enabled tracing and this is the result:
>
> > =====================
> > PARSING IN CURSOR #6 len=34 dep=0 uid=65 oct=42 lid=65 tim=105493947922
hv=3913151867 ad='67f33cac'
> > ALTER SESSION SET SQL_TRACE = TRUE
> > END OF STMT
> > EXEC #6:c=0,e=519,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=105493947908
> > =====================
> > PARSING IN CURSOR #4 len=48 dep=0 uid=65 oct=7 lid=65 tim=105493978311
hv=3637529011 ad='67da3a90'
> > delete from PROPERTIES p where p.VERSION_ID = 28
> > END OF STMT
> > PARSE
#4:c=15625,e=9852,p=0,cr=12,cu=0,mis=1,r=0,dep=0,og=1,tim=105493978298
> > EXEC #4:c=0,e=335,p=0,cr=1,cu=1,mis=0,r=1,dep=0,og=1,tim=105493978772
> > STAT #4 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE (cr=1 pr=0 pw=0
time=163 us)'
> > STAT #4 id=2 cnt=1 pid=1 pos=1 obj=51685 op='INDEX RANGE SCAN
SYS_C007946 (cr=1 pr=0 pw=0 time=86 us)'
> > =====================
> > PARSING IN CURSOR #6 len=48 dep=0 uid=65 oct=7 lid=65 tim=105493980587
hv=3637529011 ad='67da3a90'
> > delete from PROPERTIES p where p.VERSION_ID = 28
> > END OF STMT
> > PARSE #6:c=0,e=231,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=105493980577
> > EXEC #6:c=0,e=259,p=0,cr=1,cu=1,mis=0,r=1,dep=0,og=1,tim=105493980952
> > STAT #6 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE (cr=1 pr=0 pw=0
time=160 us)'
> > STAT #6 id=2 cnt=1 pid=1 pos=1 obj=51685 op='INDEX RANGE SCAN
SYS_C007946 (cr=1 pr=0 pw=0 time=85 us)'
> > =====================
> > PARSING IN CURSOR #5 len=94 dep=0 uid=65 oct=3 lid=65 tim=105493993679
hv=2310065897 ad='6a3e5ca4'
> > select PROPERTY_NAME, VERSION_ID, PROPERTY_NAMESPACE from PROPERTIES p
WHERE p.VERSION_ID = 28
> > END OF STMT
> > PARSE
#5:c=15625,e=11137,p=0,cr=10,cu=0,mis=1,r=0,dep=0,og=1,tim=105493993668
> > EXEC #5:c=0,e=98,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=105493993898
> > FETCH #5:c=0,e=146,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=105493995306
> > STAT #5 id=1 cnt=1 pid=0 pos=1 obj=51685 op='INDEX RANGE SCAN
SYS_C007946 (cr=1 pr=0 pw=0 time=94 us)'
> > =====================
> > PARSING IN CURSOR #6 len=35 dep=0 uid=65 oct=42 lid=65 tim=105493997130
hv=4067503723 ad='68daac44'
> > ALTER SESSION SET SQL_TRACE = FALSE
> > END OF STMT
> > PARSE #6:c=0,e=155,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=105493997120
> > EXEC #6:c=0,e=512,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=105493997740
>
> I am not really sure what "PARSING IN CURSOR #" means, but it seems the
> deletes are in different cursors?! This may be ther problem, right? This
> is the Java code I execute (I have explicitely added the paramenters to
> the prepared statements to have them in the trace):
>
> > statement =
> > connection.prepareStatement("ALTER SESSION SET
SQL_TRACE = TRUE");
> > statement.executeUpdate();
> > close(statement);
> >
> > statement =
> > connection.prepareStatement(
> > "delete from PROPERTIES p where p.VERSION_ID =
"+id);
> > // statement.setLong(1, id);
> > deleted = statement.executeUpdate();
> > System.out.println("Deleted: "+deleted);
> > close(statement);
> >
> > statement =
> > connection.prepareStatement(
> > "delete from PROPERTIES p where p.VERSION_ID =
"+id);
> > // statement.setLong(1, id);
> > deleted = statement.executeUpdate();
> > System.out.println("Deleted: "+deleted);
> > close(statement);
> >
> > statement = connection.prepareStatement("select
PROPERTY_NAME, VERSION_ID, PROPERTY_NAMESPACE from PROPERTIES p " +
> > "WHERE p.VERSION_ID = "+id);
> > rs = statement.executeQuery();
> > while (rs.next()) {
> > System.out.println("After **** Name
"+rs.getString(1));
> > System.out.println("After **** Version-Id
"+rs.getString(2));
> > System.out.println("After **** NS
"+rs.getString(3));
> > }
> > close(statement,rs);
> >
> > statement =
> > connection.prepareStatement("ALTER SESSION SET
SQL_TRACE = FALSE");
> > statement.executeUpdate();
> > close(statement);
>
>
> Both delete statements return the same number (17) and the select
> statement still retrieves 17 values.
>
> How is all this possible?
>
> Thanks in advance to all the people still interested in helping :)
>
> Oliver
>
> Oliver Zeigermann wrote:
>
> > 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...
> >
> > Cheers and thanks in advance,
> >
> > Oliver
 |
 |