Re: Weired problem when using serializable transaction in 10g

From: Oliver Zeigermann <oliver_at_zeigermann.de>
Date: Thu, 24 Jun 2004 12:24:56 +0200
Message-ID: <2jvoi3F157l7rU1@uni-berlin.de>

Jonathan Lewis wrote:
> According to your trace file:
>
>

>>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)'
>
>
> Note the EXEC line, which contains the text "r=1".

Ah, I see. Any pointer you could give to a full explanation?

> For a delete statement, this should be the number
> of rows deleted.
>
>
> If this trace is from your script, then there is

It is, it is, it is, I swear!

> something odd going on - but I wouldn't speculate
> about the source of the oddity until I see a
> trace that is guaranteed to be the definitive
> end to end simple test case. (For example -
> what happened to cursor #5 ?)

This trace would be gigantic. Do you want it anyway?

>
>
> In passing - here's a complete log from an SQL
> session which produces a similar effect - but is
> not what's happening in your case:

What does happen here? I insert and delete the same values over and over again in my application.

>
> SQL> spool temp
> SQL> truncate table t1;
>
> Table truncated.
>
> SQL> insert into t1 values(1);
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> delete from t1;
>
> 1 row deleted.
>
> SQL> delete from t1;
>
> 1 row deleted.
>
> SQL> delete from t1;
>
> 1 row deleted.
>
> SQL> delete from t1;
>
> 1 row deleted.
>
> SQL> delete from t1;
>
> 1 row deleted.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select * from t1;
>
> N1
> ----------
> 1
>
> 1 row selected.
>
> SQL> spool off
>
>
Received on Thu Jun 24 2004 - 05:24:56 CDT