0

I'm writing a term paper that demonstrates Oracle 11g's bells and whistles and I'm having a hard time demonstrating locking. I'm trying to show that "dirty reads" can be prevented with session isolation levels but my sample code seems to allow them but my 2nd SELECT seems to see my INSERTed row even with the SERIALIZABLE isolation level:

/* --------------- */
ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE;   
set transaction name 'gen_trx';
select count(*) from genres;
-- 135
insert into genres
VALUES (60,'Bar');
select count(*) from genres;
-- 136
COMMIT;
select count(*) from genres;
-- 136

What am I missing here?

Dan Dye
  • 767
  • 7
  • 15
  • 5
    Oracle never allows dirty reads. And it doesn't appear that the snippet you posted is doing any dirty reads. Oracle doesn't use any locking to prevent dirty reads, it uses multi-version read consistency. Are you sure that you intend to be talking about dirty reads and not something else? – Justin Cave Nov 11 '13 at 22:25
  • Thanks, Justin. No, I am *not* sure that "dirty reads" are my point. I guess what I'm trying to demonstrate is that I have control over the isolation levels and I'm trying to provide a succinct example of that control. I was under the impression that SERIALIZABLE would hide my INSERT from my SELECT until after I'd COMMITed but the counts (135,136,136) show that this is not the case. BTW, I'll clean up the question for posterity and future reference once I get this sussed out. – Dan Dye Nov 12 '13 at 00:17
  • @Dan Dye: I'm not sure if I understood properly, but after the insert statement your current session will be able to see your inserted values. Also you are making a 'Select' before the transaction is closed. May be you should check in another session. The select in another session would give you the count 135 and once you commit your transaction here, the count would be 136. – zephyrus Nov 12 '13 at 10:57
  • Good read http://www.oracle.com/technetwork/issue-archive/2005/05-nov/o65asktom-082389.html – Srini V Jan 09 '14 at 13:35
  • In a nutshell, with SERIALIZABLE, you won't "see" changes that other transactions committed after your transaction started. In other words, you only "see" things as they were committed when your transaction started, plus any changes made by your transaction. To demonstrate this, you need two concurrent transactions. – Brian Camire Nov 12 '14 at 14:49

1 Answers1

2

Your current session can always read uncommitted rows and cannot read uncommitted rows from other sessions. Oracle does not allow reading uncommitted transactions read more

To test it You must run your code in two different session without a commit.

cause if Oracle did allowed dirty reads you should have been getting more then 136 rows when a parallel session is making uncommitted entries.

You will find more information about Oracle 11g transactions isolation levels here

Azzy
  • 1,717
  • 2
  • 13
  • 17