Re: Problem setting SERIALIZABLE in a distributed transaction.

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'.

Thanks for the remote vs distributed definitions.

Thanks again for your response and I'll let you know if I learn more.

cheers, Alasdair.

"Vladimir M. Zakharychev" wrote:


> 

> I think you can't set serializable level for remote transaction - it's

> a different database instance so it simply doesn't make sense to do

> so even if you could. I also think that default isolation level in Oracle

> (which is not quite READ COMMITTED as per ANSI SQL, but a bit

> stronger level, something between read committed and serializable,

> for which ANSI standard has no definition) is adequate for your needs.

> Both databases will attempt to maintain before images for your

> transaction and your distributed transaction will see data as it was

> at the time tx started on both ends (or you'll get ORA-1555 if at some

> point Oracle would be unable to construct before image for some

> reason). Other transactions on local or remote end will not affect what

> your distributed transaction sees and will not see uncommitted changes

> from your distributed transaction, which is what I think you want to

> achieve.

> 

> As for differences between remote and distributed transactions, I see

> it this way: remote transaction takes place on remote database and is

> local for that database, and distributed transaction takes place on more

> than one database, one local and one or more remote. 2PC (two-phase

> commit) is used to commit distributed transaction: first one (master)

> database asks all other databases to prepare for commit and receives

> confirmation, and then local and all remote transactions are committed

> simultaneously.

> 

> Corrections and additions welcome.

> 

> --

> 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:3D5A8034.DD61CECC_at_talk21.com...

> > I'm trying to transfer data from one database to another and would like

> > to do it within a SERIALIZABLE transaction (I need to modify my local

> > data, do some processing and then do inserts into a remote table whilst

> > my local table is still growing).

> >

> > I thought I could do

> >

> > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

> > -- Do some local stuff

> > -- INSERT INTO table_at_remote_db

> > COMMIT;

> >

> > But I get an error message

> > ORA-02070 database <remote db> does not support set transaction in this

> > context

> >

> > Is what I'm attempting possible? (I'm struggling up a very steep oracle

> > learning curve).  I could simulate this with my own synchronisation

> > logic but would rather go with what seems a simpler solution.

> >

> > The default tx isolation level of both local and remote dbs is READ

> > COMMITTED.

> >

> > I've read (in the pile of oracle books beside me) that the "compatible"

> > param must be at least 7.3.0 - does this refer to the entry in

> > init.ora?  If so mine reads 8.1.0 and I'm waiting to hear what the other

> > end is.

> >

> > Any suggestion would be gratefully received.

> >

> > Alasdair.

> >

> > ps is what I've described a "distributed transaction" the docs also talk

> > about a "remote transaction" but I'm not clear on the difference.