Re: Weired problem when using serializable transaction in 10g

From: Oliver Zeigermann <oliver_at_zeigermann.de>
Date: Tue, 15 Jun 2004 18:02:57 +0200
Message-ID: <can6ik$6sg$06$1@news.t-online.com>

Thanks again for the kind help!

• Connection works with autocommit off, right
• Using Oracle 10g
• The scenario in which this happens is very hard to reproduce, but after certain parts of code have been executed it is deterministic - at least ;) That's why I really am unable to reproduce it in SQLPlus
• I will try the same thing with Oracle 9i tomorrow and will report back then, if it still does not work I will provide the Java code

Cheers,

Oliver

VC wrote:

> Could you post the Java code that does deletes/inserts ?  I assume the Java
> connection operastes in the 'autocommit-off' mode,  correct ?  Also,  what
> Oracle version are you using  ?
> 
> And lastly,  did you try to reproduce the behaviour in SQLPlus in order to
> elminate or shift the problem to the database ?
> 
> VC
> 
> "Oliver Zeigermann" <oliver_at_zeigermann.de> wrote in message
> news:2j8h8uFuk4idU1_at_uni-berlin.de...
> 

>>Hi VC!
>>
>>Thanks for your help! I agree this actually is *very*, *very* unlikely,
>>but is what actually happens.
>>
>>I connect to Oracle over JDBC and do not know how to create such a
>>session transcript...
>>
>>I still think the stuff works in one and a single transaction as
>>everything works well when in read committed mode. Additionally, both
>>delete statements directy follow each other and are issued from the same
>>connection. Just for testing I added a commit (done on the connection)
>>between both delete statements. What does this tell me?
>>
>>Would it make sense to test it with another Oracle version?
>>
>>Cheers,
>>
>>Oliver
>>
>>VC wrote:
>>
>>
>>>Hello Oliver,
>>>
>>>"Oliver Zeigermann" <oliver_at_zeigermann.de> wrote in message
>>>news:2j897uFu2kciU1_at_uni-berlin.de...
>>>
>>>
>>>>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...
>>>>
>>>
>>>
>>>Could you provide a full session transcript since what you are

> 
> describing
> 


>>>appear to be unlikely:

>>>

>>>SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jun 15 10:29:08 2004

>>>

>>>Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

>>>

>>>

>>>Connected to:

>>>Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

>>>With the Partitioning, OLAP and Oracle Data Mining options

>>>JServer Release 9.2.0.4.0 - Production

>>>

>>>SQL> create table t1(id int);

>>>

>>>Table created.

>>>

>>>SQL> insert into t1 select 10 from all_objects where rownum <=10;

>>>

>>>10 rows created.

>>>

>>>SQL> insert into t1 select 20 from all_objects where rownum <=10;

>>>

>>>10 rows created.

>>>

>>>SQL> commit;

>>>

>>>Commit complete.

>>>

>>>SQL>

>>>SQL> set transaction isolation level serializable;

>>>

>>>Transaction set.

>>>

>>>SQL> delete from t1 where id=10;

>>>

>>>10 rows deleted.

>>>

>>>SQL> delete from t1 where id=10;

>>>

>>>0 rows deleted.

>>>

>>>SQL> select * from t1;

>>>

>>>        ID

>>>----------

>>>        20

>>>        20

>>>        20

>>>        20

>>>        20

>>>        20

>>>        20

>>>        20

>>>        20

>>>        20

>>>

>>>10 rows selected.

>>>

>>>SQL>

>>>

>>>
>>>Probably you are running your deletes/selects in *different*

> 
> transactions,
> 


>>>not the same one.

>>>

>>>VC

>>>

>>>

>>>

>>>>Cheers and thanks in advance,

>>>>

>>>>Oliver

>>>

>>>
>>>

>
> Received on Tue Jun 15 2004 - 11:02:57 CDT