Oracle equivalent of SQL Server Snapshot isolation

Question

In Microsoft SQL Server, I use the READ_COMMITTED_SNAPSHOT ISOLATION

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

In Session 1,update the Principal from 4000 to 5000

BEGIN TRAN
Update MyTable Set Principal=5000 Where InvestorId=10 

Now in Session 2, I say

Select Principal from MyTable where InvestorId=10

I get 4000, since the Session 1 Transaction is not committed.

If I do not use the READ_COMMITTED_SNAPSHOT isolation mode, and use

1. READ COMMITTED ISOLATION Mode then my Session 2 will keep waiting
2. If I use READ_UNCOMMITTED ISOLATION Mode then my session 2 will give 5000 (equivalent to using a nolock on the select statement)

In Oracle, if I perform the equivalent set of commands, by default it behaves as if the READ_COMMITTED_SNAPSHOT isolation mode is set.

I read in microsoft articles that SNAPSHOT isolation mode writes to the tempdb before updates are done.

-How does Oracle achieve this by default ?

-Is it also writing to the disk ? does it cause i/o problems ?

-Is the default locking level in Oracle different from SQL server ?

Thanks in advance for your help and time.

Answer 1

In Oracle, the READ_COMMITTED Isolation level is the default mode, i.e. data is written to the datafile (disk) and available for select by other sessions only after COMMIT. It uses UNDO segment for this. It does not cause any I/O problem while doing a select Oracle uses Row Level Locking by default.

You can have a look at Chapters 9 and 10 of Oracle DataBase Concepts for more details

Answer 2

In Oracle, its a non blocking queries by default.. similar to SQL snapshot isolation mode. The locking behaviour still in place but doesn't affect reads which only query committed data before transaction started on affected rows thus avoiding dirty reads. see chapter 9 - Non blocking queries.