Strange problem with JDBC and Oracle 10g

Thread view:

Oliver Zeigermann - 16 Jun 2004 15:49 GMT

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

Reply to this Message

Raquel - 18 Jun 2004 06:55 GMT

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.

Reply to this Message

Oliver Zeigermann - 18 Jun 2004 07:34 GMT

> 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
[quoted text clipped - 4 lines]
> Regards,
> Raquel.

I indeed tried a commit after the first delete and actaully the data has
vanished the way it should!

Oliver

Reply to this Message

Strange problem with JDBC and Oracle 10g

Free Magazines