Re: Weired problem when using serializable transaction in 10g

From: Oliver Zeigermann <oliver_at_zeigermann.de>
Date: Tue, 15 Jun 2004 16:59:30 +0200
Message-ID: <2j8h8uFuk4idU1@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 - 09:59:30 CDT