Strange problem with JDBC and Oracle 10g
From: Oliver Zeigermann (oliver_at_zeigermann.de)
Date: 06/16/04
- Previous message: Gregory Burd: "[ANN] Berkeley DB Java Edition 1.5.0"
- Next in thread: Raquel: "Re: Strange problem with JDBC and Oracle 10g"
- Reply: Raquel: "Re: Strange problem with JDBC and Oracle 10g"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Wed, 16 Jun 2004 16:49:44 +0200
Folks!
Somehow delete statements inside a transaction do not seem to have any
effect when I set the isolation level to *serializable* in Oracle 10g.
With the default isolation level (read committed) everything works fine.
I use the JDBC driver shipped with it. I use no connection pooling, but
am working directly on the Oracle connection. Autocommit is turned off.
When I execute this Java code
> 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);
> deleted = statement.executeUpdate();
> System.out.println("Deleted: "+deleted);
> close(statement);
> statement =
> connection.prepareStatement(
> "delete from PROPERTIES p where p.VERSION_ID
= "+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 report to delete the same amount of rows and the
select statement still retrieves them afterwards. How is this possible?
Is there any obvious mistake in the code?
This is what I get as the trace
> =====================
> 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
Only thing that would make sense to me was if the two deletes and the
select somehow were executed in different transactions. Would that make
sense? If so how could this be possible?
Thanks for *any* help in advance!
Oliver
- Previous message: Gregory Burd: "[ANN] Berkeley DB Java Edition 1.5.0"
- Next in thread: Raquel: "Re: Strange problem with JDBC and Oracle 10g"
- Reply: Raquel: "Re: Strange problem with JDBC and Oracle 10g"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
