Sigh,

here are the facts:

1) i am using serializable.

2) 99.99% of the world seems to believe "serializable means there is some serial ordering of transactions that would achieve the same results". That is, when in serializable mode -- there must be some way to order the transactions CONSECUITIVELY such that the same outcome is achieved"
3) The premise in #2 is wrong.

The example is #bnot demonstrating any wild new crazy feature of serializable.#b It is not intended to show how serializable is different from read committed in Oracle. The example is showing that serializable DOES NOT imply *serial ordering*. That is all it is doing (and it does it quite simply -- perhaps TOO simply)

I know, 100% know, that the same outcome in Oracle would be achieve in read committed. I know that -- it is not relevant to the concept being demonstrated -- that serializable DOES NOT imply there is some serial ordering.

(for the whole story, you can read the chapter in my book that contains pages 130-131 -- I go into this in detail as it is a very important topic to understand).

It all stems from the isolation levels defined and how they are defined. Ansi defines them using specific "phenomena".

These phenomena are (excerpted from my book)

<quote>
The ANSI/ISO SQL92 standard adds to the concurrency control mix as well. In this standard, they define four levels of transaction isolation with different possible outcomes for the same transaction mixes. That is, the same work performed in the same fashion with the same inputs, may result in different answers given your isolation level. These isolation levels are defined in terms of three 'phenomena' that are either permitted or not at a given level. These phenomena are:

o Dirty read ? The meaning of this is as bad as it sounds. You are permitted to read uncommitted 'dirty' data. This is the effect you would achieve by just opening an OS file someone else is writing, and reading whatever data happened to be there. Data integrity is compromised, foreign keys violated, unique constraints ignored.

o Non-repeatable read ? This simply means that if you read a row at time T1, and attempt to re-read that row at time T2, the row may have changed. It may have disappeared, it may have been updated, and so on.

o Phantom read ? This means that if you executed a query at time T1, and re-execute it at time T2, additional rows may have been added to the database, which affects your results. This differs from the non-repeatable read in that in this case, data you already read has not been changed but rather that more data satisfies your query criteria than before.

SQL92 takes these three phenomena and creates four isolation levels based on the existence, or lack thereof, of the above phenomena. They are:

Isolation Level Dirty Read Non-Repeatable Read Phantom Read
Read Uncommitted Permitted Permitted Permitted
Read Committed Permitted Permitted
Repeatable Read Permitted
Serializable

Oracle supports explicitly two of the above isolation levels as they are defined ? read committed and serializable. This doesn't tell the whole story however. The SQL92 standard was attempting to set up isolation levels that would permit various degrees of consistency for queries performed in each level. Repeatable read is the isolation level they claim you must be at to get a read consistent result from a query. Read committed does not give you consistent results. Read uncommitted is the level to use to get non-blocking reads.
</quote>


So, serializable gives you the ability to prevent "phantom reads" -- a really really repeatable read if you will. To paraphrase:

A serializable
transaction operates in an environment that makes it appear as if there are no
other users modifying data in the database, the database will be "frozen" at the
point in time your query began. Your transaction sees the database consistently,
at a single point in time. Side effects (changes) made by other transactions are
not visible to it, regardless of how long it has been running.


that is what is given beyond read committed. In a serializable transaction if you do the following:


t1 select count(*) from b -- returns "0"
t2 insert into b values(1);
t3 commit;
t4 select count(*) from b --
returns "1"
t5 select count(*) from b -- returns "0"
t6 commit;
t7 select count(*) from b -- returns "1"

Using read committed, the query at time t5 would return 1, not 0. You get a "phantom read"

darn cut and paste from word. You are right the chart was like this:


dirty non-repeat phantom
x x x
x x
x

and should have been this:

dirty non-repeat phantom
x x x
x x
x


the tabs messed it up. Sorry about that -- I corrected it (thanks!)

Locking is done in exactly the same fashion as ever.

reads don't block writes
writes don't block reads
writes only block writes of the SAME exact data.

Oracle uses UNDO (not redo) to give each serializable transaction a "frozen" picture of the database at the point in time the transaction began.

You can lead to snapshot too old or cannot serialize access errors, yes. Serializable transactions are to be use for very short duration OLTP transactions where a snapshot too old should not ever be an issue.

t3 was another session.

Until that first session commits -- it sees the database frozen at the point in time the transaction began. That is the entire purpose of "serializable"

see
</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c23cnsis.htm#2778 <code>
for a more in depth discussion.

Exactly.

I alluded to this with this paragraph in chapter 3 when discussing serializable transactions:

...
In Oracle, serializability is achieved by extending the read consistency we get at the statement level to the transaction. Instead of results being consistent with respect to the start of a statement, they are preordained at the time you begin the transaction. Pretty deep thought there – the database already knows the answer to any question you might ask it, before you ask it.
......


We use the rollback (UNDO) information in the same way we would for a single statement -- it is just that the point in time your query is assumed to have started is the point in time the transaction began.

the first statement of the transaction.

A commit is the last statement -- so anything after that.

(next select would be enough)

ops$tkyte@ORA920> alter session set isolation_level=serializable;

Session altered.

don't have to use set transaction.


 

1)

begin
for x in ( select 1 x from t1 where status = p_status )
loop
raise e_status_exists;
end loop;
.....

exception
....


2) an "anti foreign key". Basically -- you'll need to lock the table T1 to prevent modifications to it during this period of time. And inserts into T1 will need to do the same to T2. To force serialization.

It is a funny situation -- curious -- what is the business requirement that is driving this? how did you get here?



3) yes.

4) no.

As pooh bear says "oh bother".... wish you would have asked me that question, that one is EASY...

create unique index t_idx on t( decode(status,0,name,null) );

done. there you go. just like in:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8980833603650 <code>


you see - entirely null entries are not made into a b*tree index, hence, by decoding on status, we in effect only index NAME when status = 0, therefore NAME will be unique when status=0.

yes

when you need to run the benchmarks provided at www.tpc.org.

when you need a point in time view of the database (a report with dozens of queries -- all of which must be consistent with regards to eachother)

not really -- you cannot update "as of"....

another side effect of serializable is that you cannot update data that was updated by someone else during your transaction. flashback won't provide that protection

why would you not just use dblinks? doing "database things" outside of the database is just "not the right idea"

why do they call themselves a design team? maybe "redesign" or "as hard as we can make it design".

set transaction statements like that must be the first statment in a transaction, it'll be a really bad idea to stick it in each procedure.

that is actually a good point and shame on me really.

there could be a valid reason for using an external TPM to coordinate Oracle resources -- there could be non-oracle data sources not covered by a gateway for example.

there could be a valid reason.

discrete transactions are deprecated and require the setting of an "_" parameter

discrete transactions "don't work" without it.

well, serializable was not added until 7.3.3 so it was moot "before"

you had to enable discrete transactions in the init.ora then. you could have gotten:

ERROR:
ORA-08176: consistent read failure; rollback data not available

but it would not be assured, the blocks you need might be in the buffer cache - we might not need the UNDO, there might be a read consistent block sitting there for you already.

q1) you can certainly file an enhancement request regarding that.

q2) no

a transaction is begun when you use

o set transaction with some options (other than read committed)....
o insert/update/delete/merge
o touch a dblink for the first time
o use "for update"

they implicitly begin a "transaction" regardless.

read only/serializable for example demark the point in time that your queries will be "as of" -- until you commit.


you have a de-facto transaction there.


should add another way (there are probably others)

trans_id := dbms_transaction.local_transaction_id( TRUE );

well, what is the goal -- why do you need to know, what are you trying to accomplish?

it is dandy, it is the default. it is harmless

serializable works best for small, short transactions.

Looking at this small fragment, if the 100 is "constant" like that, wouldn't just adding a "where userid <> 100" to the select simply solve it? (solve it simply..?)

I cannot reproduce, we send the alters over - I created a database link from my 9i to 10g database, joined emp@remote to dual locally, logged into other database, updated EMP to set ename=lower(ename), committed.

I do not see the changes over the dblink until I commit the session with the dblink.

wow, I've been asked to comment on "best practices for t-sql programming"

neat.

time to find a sqlserver programmer I guess?

this sounds like a nightmarish disaster.

what sort of "logic" is being employed here - I see *massive* lost update potential - I don't see how this sort of "update" can even begin to make sense.

You'll need to explain a lot better what is actually going on there.

... including autonomous
transaction ..

you have a bug, you have a bug, you have a big big bug.


you have a bug - you cannot use an autonomous transaction like this - do you understand what happens during "rollback"????


how, HOW, could I give you a solution to a completely UNKNOWN PROBLEM?????

It does NOT HAVE TO LOCK ANYTHING

that is the beauty of multi-versioning and read consistency. We give you non-blocking reads that return a consistent set of results. You can query as if no one else is in the database.


Do not think "oracle is like informix, db2, sqlserver <......>"


https://www.oracle.com/technetwork/issue-archive/2005/05-nov/o65asktom-082389.html

*NO LOCKS*

a lock is used for a modification, a read does not lock rows (except for update of course, but that makes the read a modification).

A read is not blocked by writes
Writes are not blocked by reads

for read consistency we need not lock A THING.


We use UNDO to put things back the way they were - we don't need locks for anything like that - we are *not* sqlserver.

ops$tkyte%ORA11GR1> create table t ( x int );

Table created.

ops$tkyte%ORA11GR1> insert into t values ( 1 );

1 row created.

ops$tkyte%ORA11GR1> commit;

Commit complete.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> set transaction isolation level serializable;

Transaction set.

ops$tkyte%ORA11GR1> select * from t;

         X
----------
         1

ops$tkyte%ORA11GR1> declare
  2          pragma autonomous_transaction;
  3  begin
  4          update t set x = x+1;
  5          commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> select * from t;

         X
----------
         1

ops$tkyte%ORA11GR1> update t set x = x+1;
update t set x = x+1
       *
ERROR at line 1:
ORA-08177: can't serialize access for this transaction

do you have access to support - If so, please file this as a bug:

TEST CASE:

set echo on

/* create table t as select * from all_users where 1=0
   on both sites
*/

delete from t;
delete from t@ora10gr2;
insert into t@ora10gr2 select * from all_users@ora10gr2;
commit;

set transaction isolation level serializable;

set echo off
prompt when logged into remote database, issue:
prompt insert into t select * from all_users;;
prompt commit;;
set echo on
pause
insert into t select * from t@ora10gr2;
delete from t@ora10gr2;
pause



drop table t2;
create table t2 as select * from all_users;
delete from t;
commit;

set transaction isolation level serializable;

set echo off
prompt when logged into local database, issue:
prompt insert into t2 select * from all_users;;
prompt commit;;
set echo on
pause

insert into t select * from t2;
delete from t2;
select count(*) from t2;
commit;
select count(*) from t2;

If not, let me know please and I will.

serializable would be one approach yes. another would be:

ops$tkyte%ORA10GR2> create table t as select a.*, 'N' extracted from all_users a;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2      type array is table of rowid index by binary_integer;
  3      l_data array;
  4      l_batch_size number := 100;
  5  begin
  6      for x in (select rowid rid, t.* from t where extracted='N')
  7      loop
  8          /* ... write it out ... */
  9          l_data(l_data.count+1) := x.rid;
 10          if (l_data.count=l_batch_size)
 11          then
 12              forall i in 1 .. l_data.count update t set extracted = 'Y' where rowid = l_data(i);
 13              l_data.delete;
 14          end if;
 15      end loop;
 16      forall i in 1 .. l_data.count update t set extracted = 'Y' where rowid = l_data(i);
 17  end;
 18  /

PL/SQL procedure successfully completed.

however every approach will of course be flawed and broken - there is no way to create your file "transactionally", if an error occurs - the file has to be rebuilt entirely. It will be corrupt otherwise. No way around that.

If you use serializable, the odds of hitting an ora-8177 sometime will be very high, you will be rebuilding the entire file since utl_file (in face - since everything that just writes to the file system) will not participate in ROLLBACK.

I would question "why", "why are we doing, what is the situation that would be necessary for this to have to happen, let's find a way to NOT dump the data out of the database where it is useful, secure, protected, recoverable, transactional"



You call this a traditional harvest, I call it "a bad idea". It is not traditional where I come from.

..
Not sure what you mean. Extracting data from a database and writing it to a
file for the purpose of sending it to another system is a common use-case, no?
....

in the manner you are doing it? No, not a chance, no way.

It would never be done in an incremental fashion like that - appending to a file unextracted records.

You would extract what the other site needs - at once, upon demand - if at all.


You would have a query that identifies the data to be extracted.


I know of no one trying to do replication via flat files as you seem to be trying.


And, as stated, your algorithms just won't work unless you say to the database 'STOP, STOP I SAY, no modifications - let me work'. You have to serialize to do what you want.


Extracts to another system - sure, done all of the time.

The way you are? No, haven't seen it. It is certainly not traditional

No, I'm saying - there are things that have been sent, there are things that have not been sent.

You cannot use append to send things - appending to a file doesn't work. I have to assume you are appending because you say:

... The extract runs every X minutes and follows the following pattern ...

so, I presume that every X minutes, you run a process to add more "unextracted records" to that file.

If so, that is your flaw.

What people do is say:

Hey, we need to send them the extract.

Great, run the extract process - it runs but it remembers in a table in the database the extract TIME. and instead of having an extract flag of Y or N, it has a timestamp - the time of the extract (sort of like a BATCH JOB ID).

The extract runs ONCE to create a single extract file - never appended to. The extract runs, updates the records it unloaded, writes to file, gets confirmation that file is OK, saves the timestamp along with other useful data in an extract table, and then commits.

If the extract file fails - gets erased, gets lost, gets corrupted, whatever - big deal, so what, just run it again - run it in a mode that says "find extract = :THIS_DATE and re-extract it (instead of 'is null' for the extract time, look for extract time = :this_date - repull those records).


It is not traditional to every X minutes try to maintain a file in sync with the database.

It is typical for systems that need to do some sort of EDI (electronic data interchange - been around since dirt was invented) to create an extra file in 'batch' - a one time creation of said file, not trying to replicate to file system like you are now.



Next month, week, day, whatever - you would "run the extract again" to extract whatever records it needs now. It is an ongoing process - but it needs to be batched up like that - so the records extracted at time T can be identified as a group - so you can rerun THAT extract for when (not if, when) that extract file fails or needs to be redone for whatever reason.

see above.

did you read my code?

As I said, extract will be a date/time or a BATCH_ID column (it will not be yes/no)

when you extract:

ops$tkyte%ORA10GR2> create table t as select a.*, cast( null as timestamp(6) ) extracted from all_users a;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure do_extract( p_which in timestamp, p_set_to in timestamp )
  2  as
  3      type array is table of rowid index by binary_integer;
  4      l_data array;
  5      l_batch_size number := 100;
  6      l_cnt number := 0;
  7  begin
  8      for x in (select rowid rid, t.* from t where (p_which is not null and extracted= p_which)
  9                union all
 10                select rowid rid, t.* from t where (p_which is null AND extracted is null ) )
 11      loop
 12          /* ... write it out ... */
 13          l_data(l_data.count+1) := x.rid;
 14          if (l_data.count=l_batch_size)
 15          then
 16              forall i in 1 .. l_data.count update t set extracted = p_set_to where rowid = l_data(i);
 17              l_cnt := l_cnt + l_data.count;
 18              l_data.delete;
 19          end if;
 20      end loop;
 21      forall i in 1 .. l_data.count update t set extracted = p_set_to where rowid = l_data(i);
 22      l_cnt := l_cnt + l_data.count;
 23      dbms_output.put_line( 'wrote ' || l_cnt || ' records' );
 24  end;
 25  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace package my_data
  2  as
  3      g_ts timestamp(6);
  4  end;
  5  /

Package created.

ops$tkyte%ORA10GR2> exec my_data.g_ts := systimestamp

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec do_extract( null, my_data.g_ts );
wrote 46 records

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_lock.sleep(3)

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec do_extract( null, systimestamp );
wrote 0 records

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_lock.sleep(3)

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec do_extract( my_data.g_ts, my_data.g_ts );
wrote 46 records

PL/SQL procedure successfully completed.

as you can see, you only delete that which you have read, you stamp it with a batch id, so WHEN (not if) you need to re-extract some records, you can.

please do not use sqlplus to write a file for something like this, you want something you have a tad bit of control over - at least utl_file will RAISE AN ERROR when an error happens. what happens when your file system fills up?

re-invent for this, it ain't that hard, it should actually be very very very VERY easy. You have to_char and formats galore, creating a flat file would be *trivial* with utl_file. Explain how sqlplus would be "so much easier" - especially since you are not just printing a cursor.

it is not exposed to my knowledge - the session that did it "knows" it (because it did it) but it isn't exposed as an attribute for another session to see.

so what, it exits? that is pretty useless, you have to have a human being involved.


you are NOT just printing a cursor, what have we been talking about this entire time? We've been talking about how actually complex this really is.

ops$tkyte%ORA10GR2> create or replace procedure do_extract( p_which in timestamp, p_set_to in 
timestamp )
  2  as
  3      type array is table of rowid index by binary_integer;
  4      l_data array;
  5      l_batch_size number := 100;
  6      l_cnt number := 0;
  7  begin
  8      for x in (select rowid rid, t.* from t where (p_which is not null and extracted= p_which)
  9                union all
 10                select rowid rid, t.* from t where (p_which is null AND extracted is null ) )
 11      loop
 12          /* ... write it out ... */
 13          l_data(l_data.count+1) := x.rid;
 14          if (l_data.count=l_batch_size)
 15          then
 16              forall i in 1 .. l_data.count update t set extracted = p_set_to where rowid = 
l_data(i);
 17              l_cnt := l_cnt + l_data.count;
 18              l_data.delete;
 19          end if;
 20      end loop;
 21      forall i in 1 .. l_data.count update t set extracted = p_set_to where rowid = l_data(i);
 22      l_cnt := l_cnt + l_data.count;
 23      dbms_output.put_line( 'wrote ' || l_cnt || ' records' );
 24  end;
 25  /

there is your "just printing a cursor" code - it does NOT just print a cursor, there is some logic there.

read http://www.dbazine.com/oracle/or-articles/kyte3

the sql standard defines the isolation levels in terms of "phenomena", we support serializable mode transactions in the sense that is specified.

microstrategy DOES NOT create temp tables (oh, that they would, life would be grand)

they create REAL tables and pretend to themselves that they are temporary.


Anyway...



I'm not sure what user you are talking about there - the microstrategy account or end users.

There is a chance that flashback query could be used - but I hesitate to suggest that because I don't want you to run flashback query ALL of the time - just when you need to, but that would involve query modification..

I just used two terminals and sqlplus, nothing fancy

it fit the description of ANSI as of the time of the implementation (version 7.3). At that time, ANSI described serializable purely in terms of 4 phenomena - dirty read, committed read, repeatable read, no phantom reads. Serializable was simply no phantom reads.

... will the query read that committed data on the last row at a SCN higher in value when the query execution began? ...

No, Oracle always implements read consistency. In read committed, when you OPEN the cursor - the result set is pre-ordained as of the point in time you opened the cursor. Any commits that happen AFTER the cursor is opened will not be seen by that cursor.

In serializable - the cursor result set is pre-ordained as of the point in time YOUR TRANSACTION began - you will see no other sessions committed work that was committed after your transaction began.

... On what condition will it read the value from undo segment which should store the prior data of SCN 11990? ...

I don't know what you mean there.

you got that from my book????? ouch. I must not be as good at writing and explaining things as I thought :(

In Oracle - reads do not block writes, writes do not block reads. Using multi-versioning and read consistency we give you read committed results without blocking. In addition - you get read committed with READ CONSISTENCY - meaning you get an answer that existed in the database. Many other databases - the ones that employ shared read locks and where updates block reads - give you answers that NEVER existed in the database.

Perhaps you were reading the chapter on Concurrency where I *compare* Oracles read consistent approach with the OTHER databases approach. Please tell me where I wrote that:

"... that in read committed cases, the reader of any session waits till the lock on that table by writer of other session releases after DML ..."

Since serializable is designed and anticipated to be used in short duration transactions only (seconds/minutes at most), no there isn't really a performance difference.


However, if you try to use serializable for a long duration transaction - there well could be.

That is due to the fact that in general - the further back in time you "flashback", the longer a flashback query will take to execute (and serializable is just like using flashback query - all statements in a transaction will be "as of" the same old point in time).

To query some set of data "as of" one minute ago will involve rolling back all changes to the accessed data for one minute. That will be a 'small' set of undo. To query that same set of data "as of" five minutes ago will involve rolling back all changes to the accessed for five minutes. That will likely be a larger set of undo to apply. Going back ten minutes - even more so and so on.



In general - no real difference when used for short duration transactions. Possibly a measurable impact for long duration transactions. it is a matter of how much undo has to be applied.

transactions start with the first DML statement (insert,update,delete,select,merge) - ALTER is not the start of the transaction, you just set a session level setting, your transaction started with the SELECT.

If you would have used:


set transaction isolation level serializable;


instead, that would have started a transaction - and you would have seen what you were apparently expecting to see...

keep reading the book - in the book I told you what must be in place at each level, SQL describes the levels in terms of "phenomena" - you must not allow a given phenomena to exist at each level. We do that - we satisfy the standard.


http://www.oracle.com/technetwork/issue-archive/2005/05-nov/o65asktom-082389.html

sql standard does NOT say that read committed "does not" give you consistent results, it DOES NOT IN ANY WAY say that. It says what read committed must do (you get to read only committed data) - but it does not say "but give the wrong answer". If you believe it does - quote the standard verbatim

yes, if they were to violate a unique constraint together - one would block on the other. But that isn't an attribute of serializable, that would be true in all isolation levels.

"insert into A select max(x)+1 from A" -- I want a counter.


that would be just about the worst thing in the world you could possibly even consider thinking about doing - seriously. It would be what I would term "not smart"

to do it however, you would

lock table a in exclusive mode;
insert into a ....;



but it would be something you DO NOT WANT TO EVER DO IN REAL LIFE. Use a sequence. use a sys_guid(). use something else - but do not do this.

wouldn't it be so very very cool if websites that provided "information" also had a method to question such information? That way, you wouldn't have to go elsewhere to get the material refined, updated, etc.

My suggestion to you in the future is:

if a site doesn't have dates on their articles and/or if they do not make any mention of a version - beware, be very suspicious of that material.

if a site doesn't allow for commenting on articles - some method of providing feedback - beware, be very very suspicious of that material.

if a site doesn't have any references to support their claims or does not include some evidence of what they say is true - beware, be very very very VERY suspicious of that material.


Think about how hard it would have been for the author of that article to provide evidence that what they say is probably true?

A link to the documentation? That would have been nice.

Here is an "anti" link I found
http://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch10.htm#index1477
(yes that is version 7 documentation, it goes back that far)


An example showing the error you get when doing distributed with serializable? That would have been cool.

However...

ops$tkyte%ORA11GR2> set transaction isolation level serializable;

Transaction set.

ops$tkyte%ORA11GR2> insert into t1 values ( 1 );

1 row created.

ops$tkyte%ORA11GR2> insert into t2@loopback@ora11gr2 values ( 2 );

1 row created.

<b>seems to work so far - no errors - but maybe the database is just lying to us and we are not serializable.  One way to see would be to have another transaction create data:</b>

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          insert into t1 values ( 3 );
  5          insert into t2@loopback@ora11gr2 values ( 4 );
  6          commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from t1;

         X
----------
         1

ops$tkyte%ORA11GR2> select * from t2@loopback@ora11gr2;

         X
----------
         2

<b>Nope, cannot see it - it was serializable, if we commit:</b>

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> select * from t1;

         X
----------
         1
         3

ops$tkyte%ORA11GR2> select * from t2@loopback@ora11gr2;

         X
----------
         2
         4

<b>viola - we see it... further evidence:</b>
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> set transaction isolation level serializable;

Transaction set.

ops$tkyte%ORA11GR2> select * from t1 where x = 1;

         X
----------
         1

ops$tkyte%ORA11GR2> select * from t2@loopback@ora11gr2 where x = 2;

         X
----------
         2

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          update t1 set x = -1 where x = 1;
  5          update t2@loopback@ora11gr2 set x = -2 where x = 2;
  6          commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> update t1 set x = -1 where x = 1;
update t1 set x = -1 where x = 1
       *
ERROR at line 1:
ORA-08177: can't serialize access for this transaction


ops$tkyte%ORA11GR2> update t2@loopback@ora11gr2 set x = -2 where x = 2;
update t2@loopback@ora11gr2 set x = -2 where x = 2
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction
ORA-02063: preceding line from LOOPBACK@ORA11GR2

you only get ora-8177's from serializable transactions




Challenge for you - that article is relatively current (april 2011, less than a year), why don't you try to get it updated and let us know what happens.

serializable transactions see the data as of the beginning of the transaction - not statement - so this wouldn't affect them.


To say this means "serializable doesn't work with distributed" is quite simply "wrong"

quote:
The serializable transaction isolation level is not supported with distributed transactions.
The article is wrong, plain and simple.


and did the article get updated to reflect this, if the author wanted to make a point, it wouldn't be "serialiable doesn't work", it would be that documented caveat - which is a caveat, not a "does not work"

we took the ANSI definition, the set of properties given to serializable transactions and implemented it.. It was not too prescriptive, if anything - it didn't have enough definition.

http://www.oracle.com/technetwork/issue-archive/2005/05-nov/o65asktom-082389.html

1 The error stack starts at the top from the statement that raised the error, down to the client call at the bottom. So

"Something" happens which raises ORA-08176
This was raised on EVAL
Which was then raised on EVAL2DWH

2 The error raises two other possible reasons:

direct load or discrete transaction.

Did you have any direct path loads during this period? How are you managing the transactions?

3. There are some bugs listed against this error; check MOS to see if any of them apply to you.

To help further we need to see more details of what you're doing - ideally a complete test case (create table + insert into + queries) that reproduce the problem.

I'm not sure why you're getting this error - a complete test case of the process so we can reproduce it will help massively!

Search MOS for ORA-08176 - you'll find several articles/bugs. I don't know enough about your system to say if these apply to you or not