Re: Weired problem when using serializable transaction in 10g

From: Oliver Zeigermann <oliver_at_zeigermann.de>
Date: Wed, 16 Jun 2004 16:20:06 +0200
Message-ID: <2jb3aiFvrhdmU1@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 - 09:20:06 CDT