You don't need to close the statement and reissue it. You can just update
the bind variable and execute it again. Much more efficient. Since you
can't reproduce this in SQLPlus then the problem is probably in the Java.
Are you using connection pooling from some application server so that each
statement is a different connection? That might give you odd results.
Since you didn't commit the delete then another session wouldn't see the
rows deleted.
Jim
"Oliver Zeigermann" <oliver_at_zeigermann.de> wrote in message
news:2jb3aiFvrhdmU1_at_uni-berlin.de...
> 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
Received on Wed Jun 16 2004 - 09:40:32 CDT
