Re: Problem setting SERIALIZABLE in a distributed transaction.

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.