Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Weired problem when using serializable transaction in 10g

Re: Weired problem when using serializable transaction in 10g

From: Oliver Zeigermann <oliver_at_zeigermann.de>
Date: Wed, 16 Jun 2004 17:01:31 +0200
Message-ID: <2jb5o6FvbbviU2@uni-berlin.de>


Hi Jim!

Thanks for the kind reply!

I do not use connection pooling, but work directly on the connection class of the JDBC driver shipped with Oracle 10g (same thing with older versions, though). The whole thing gets committed later...

The closing of the prepared delete statements could not be part of my problem, right?

Cheers,
Oliver

Jim Kennedy wrote:

> 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 - 10:01:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US