Re: Weired problem when using serializable transaction in 10g

Oliver,

Clearly, the problem/bug lives in your Java code. I just could not reproduce your results:

Having somewhat simplified the code you've posted:

===

• create a table with data

create table t1(x int);
insert into t1 values(1);
insert into t1 values(2);
commit;

• Java code

import java.sql.*;
public class test1 {

 public static void main(String[] args) throws Exception{

  Class.forName("oracle.jdbc.driver.OracleDriver");   String url = "jdbc:oracle:thin:@localhost:1521:orcl1";

  Connection conn = DriverManager.
    getConnection(url, "hr", "hr");
  conn.setAutoCommit(false);

  Statement statement = conn.createStatement();   statement.execute("set transaction isolation level serializable");   int deleted = statement.executeUpdate("delete from t1 where x=1");   System.out.println("Deleted: "+deleted);   statement.close();

  statement = conn.createStatement();
  deleted = statement.executeUpdate("delete from t1 where x=1");    System.out.println("Deleted: "+deleted);   statement.close();

  statement = statement = conn.createStatement();   ResultSet rs = statement.executeQuery("select * from t1");   while (rs.next())
    System.out.println("X: "+rs.getString(1));  }
}

• result:

Deleted: 1
Deleted: 0
X: 2

Questions:

1. What does the 'close(statement)' call in *your* code do ? There is no such JDBC call, the correct call being statement.close(); It may be that the close(statemnt) call perfoms a rollback somewhere...
2. Post a full working piece of code, as I did , that reproduces the problem.

VC

"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 - 18:12:59 CDT