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: Problem setting SERIALIZABLE in a distributed transaction.

Re: Problem setting SERIALIZABLE in a distributed transaction.

From: Alasdair I MacLeod <alasdair_i_macleod_at_talk21.com>
Date: Fri, 16 Aug 2002 16:07:05 +0100
Message-ID: <3D5D1519.BC67F34D@talk21.com>


Guys,

The problem I had was that I wanted to alter the data in the table so I couldn't use a read only tx - but I've re-written the processing to work around the problem. Although it doesn't solve why I can't use serializable txz it's actually a more light-weight and probably better solution. (but read on...).

Your comments and Jusung Yang's made me suspicious of my setup. I tried setting serialisable from a session on an 8.1.7 on Solaris and it works fine!

I.e.

on the remote server Oracle9i 9.0.1.1.1
create table test (num NUMBER);
insert into test values (77);
commit;

on the 8.1.7/Solaris
SQL> set transaction isolation level serializable; Transaction set.
SQL> select * from test_at_eclipse.world;

       NUM


        77
SQL> on the 8.1.5/Windows 2000
SQL> set transaction isolation level serializable; Transaction set.
SQL> select * from test_at_eclipse.world;
select * from test_at_eclipse.world;

              *
ERROR at line 1:
ORA-02070: database <remote link> does not support set transaction in this context
SQL> So I reckon it's a problem with my 8.1.5. Either it's configured wrongly or there's
a bug in the networking.

When I've got time I'll install 8.1.7 on the W2K box and see what happens.

Thanks again for your help, Alasdair.

"Vladimir M. Zakharychev" wrote:
>
> Here's a small demonstration of behavior I described (8.1.7, but should be
> the same in 8.1.5), with some explanations following:
>
> Session 1 log
> ----------------
> SQL> insert into t values(1);
>
> 1 row created.
>
> SQL> insert into t values(2);
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> declare
> 2 cnt pls_integer;
> 3 begin
> 4 dbms_output.put_line('Tx 1 started at '||to_char(sysdate,'HH24:MI:SS'));
> 5 set transaction read only;
> 6 select count(*) into cnt from t;
> 7 dbms_output.put_line('Rows in T: '||cnt);
> 8 dbms_lock.sleep(10);
> 9 select count(*) into cnt from t;
> 10 dbms_output.put_line('Rows in T 10 seconds after: '||cnt);
> 11 commit;
> 12 dbms_output.put_line('Tx 1 ended at '||to_char(sysdate,'HH24:MI:SS'));
> 13 end;
> 14 /
> Tx 1 started at 13:06:05
> Rows in T: 2
> Rows in T 10 seconds after: 2
> Tx 1 ended at 13:06:15
>
> PL/SQL procedure successfully completed.
>
> ------------------
>
> Session 2 log:
> ----------------
>
> SQL> insert into t values(3);
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select to_char(sysdate,'HH24:MI:SS') "Tx 2 ended at" from dual;
>
> Tx 2 end
> --------
> 13:06:07
>
> SQL> select count(*) "Rows in T for Tx 2" from t;
>
> Rows in T for Tx 2
> ------------------
> 3
>
> ---------------------
>
> Note that Tx 2 started after Tx 1 and ended before Tx 1. It inserted one row
> into T, committed it and confirmed that row was inserted (3 rows in T). Tx 1
> started by defining a read only transaction and selected count from T, which
> was 2 at that time. It then suspended for 10 seconds and re-queried T - and
> still saw 2 rows, while Tx 2 already inserted 3rd row and committed it! The
> same holds for read-write transactions, but in this case each query gets its
> own snapshot. If you remove SET TRANSACTION .... from the above code,
> you will see that changes made by Tx 2 become visible for Tx 1 - each query
> from T receives its own snapshot now because tx is read-write (default) and
> Oracle maintains statement-level read consistency for this tx. Currently you
> cannot freely switch between tx-level and stmt-level read consistency within
> one transaction, and read only transactions are what they are - read only, so
> you can't change anything within such tx though you are guaranteed to have
> consistent view of the database throughout the whole tx.
>
> Also note that SET TRANSACTION must be the first statement in a transaction.
> Your problem may well be fixed by issuing COMMIT just before SET
> TRANSACTION to end previous tx and start new tx with new isolation level
> (no guarantees that it will help though ;)
>
> By the way, I would recommend upgrading to at least 8.1.7 - 8.1.5 is long
> time desupported and was full of bugs.
>
> --
> Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
> All opinions are mine and do not necessarily go in line with those of my employer.
>
> "Alasdair I MacLeod" <alasdair_i_macleod_at_talk21.com> wrote in message
> news:3D5BDF1B.EBAE77AA_at_talk21.com...
> > Vladimir,
> >
> > thanks for your comments but I'm not seeing the transaction behaviour
> > you describe.
> >
> > The problem I had was that I don't want to see any changes to my local
> > table after the transaction begins. The default oracle tx level
> > doesn't seem give me this - if I select * from table then insert and
> > commit another row from a different session and select * again the new
> > row will show up in my original tx.
> >
> > I.e.
> > session 1 session 2
> > select * from test
> > num
> > ---
> > 1
> > 2
> > insert into test (num) values 3;
> > commit;
> > select * from test
> > num
> > ---
> > 1
> > 2
> > 3
> >
> > (Running on 8.1.5 by the way.)
> >
> > What I was looking for was a situation where the third line would not
> > show
> > up in session 1 until the tx ended - I have to do some data processing
> > before transferring the data and end up with two sets of data with a one
> > to
> > one correlation.
> >
> > I got round the problem by having an initial transaction that simple
> > updates
> > a flag column to mark the rows as 'mine'. Then, in a later transaction
> > I
> > select for update, do stuff and write to the remote db. This works fine
> > and saves on creating a fairly long lived, serializable, tx. However,
> > it
> > doesn't feel the 'right thing'.
> >
Received on Fri Aug 16 2002 - 10:07:05 CDT

Original text of this message

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