 |
AppletTalk.com
Java discussions newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Oliver Zeigermann
Guest
|
 Posted: Wed Jun 16, 2004 2:49 pm Post subject: Strange problem with JDBC and Oracle 10g |
 |
|
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
| Quote: | 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
| Quote: | =====================
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
|
|
| Back to top |
|
 |
Raquel
Guest
|
| Posted: Fri Jun 18, 2004 5:55 am Post subject: Re: Strange problem with JDBC and Oracle 10g |
|
|
Don't know how Oracle works but one thing is for sure. Isolation level
should not be effecting this...as isolation levels effect how OTHER
transactions will be viewing (reading) the data when your transaction is
performing changes to the data. Out of curiosity, did you try performing a
COMMIT after the first delete and see if that makes a difference?
Regards,
Raquel.
|
|
| Back to top |
|
|
Oliver Zeigermann
Guest
|
| Posted: Fri Jun 18, 2004 6:34 am Post subject: Re: Strange problem with JDBC and Oracle 10g |
|
|
Raquel wrote:
| Quote: | Don't know how Oracle works but one thing is for sure. Isolation level
should not be effecting this...as isolation levels effect how OTHER
transactions will be viewing (reading) the data when your transaction is
performing changes to the data. Out of curiosity, did you try performing a
COMMIT after the first delete and see if that makes a difference?
Regards,
Raquel.
|
I indeed tried a commit after the first delete and actaully the data has
vanished the way it should!
Oliver
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
