Re: ADO.NET 2.0 - Question for Microsoft Employees

Sahil Malik wrote:
Okay, so we have a new Isolationlevel for transactions in ADO.NET - the snapshot isolation.

That exists at System.Data - which leads me to believe that nothing really stops me from setting that isolation level on Oracle.

So my question is - by setting that on Oracle, does it mean the same as

SET TRANSACTION READ ONLY

Or does Isolationlevel.Snapshot not work on Oracle?

(Frankly my opinion, IsolationLevel.Snapshot not working on Oracle is not a good answer, but if that is what it does, then that is what it does ..)

Can anyone advise? :)

Snapshot IS oracle :D. Databases, during transactions, use various kinds of locking mechanisms to ensure consistency. They all work more or less the same: depending on the transaction isolation level, other transactions can read the data changed or have to wait till the exclusive lock is lifted from the row or table.

Oracle uses a different strategy in that it uses a Snapshot isolation level, in which writers don't block readers in any way and readers simply get an older version of the modified data, till the transaction has been completed. This locking mechanism was unique for oracle but is now also implemented in Sqlserver. It works together with MVCC, which ensures multiple copies of the same data during different transactions.

If I'm not mistaken snapshot is what oracle does by default, for example when you don't start a transaction explicitly, but I have to look that up. Basicly, snapshot is the isolation level which avoids deadlocks and also avoids dirty reads.

		FB

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
.

Relevant Pages

  • Re: computational model of transactions
    ... the rows that have been changed in the transaction log. ... varieties of the SNAPSHOT isolation that Microsoft faithfully, ... than the Oracle 'serializable'). ... DBAs and developers will use it without fully understanding the ...
    (comp.databases.theory)
  • Re: IsolationLevel workaround?
    ... In the end I opted not to use the ADO transaction methods (i.e. ... This allows me to use the SNAPSHOT isolation level. ... Is there any way to get ADO to use the SNAPSHOT isolation level? ...
    (microsoft.public.data.ado)
  • Re: Oracle Isolation Level and ADO
    ... it isn't set the Isolation level of the transaction on the ADO Connection and i think that ADO use the default Level "Read ... data to change during your transaction, and therefore if you execute your query twice, you might get different results. ... The REPEATABLE READ isolation level should work too, but Oracle does not support it, and automatically upgrades such transactions to SERIALIZABLE. ...
    (comp.databases)
  • Re: ADO.NET 2.0 - Question for Microsoft Employees
    ... but snapshot is more for reads .. ... Writes in oracle are either Readcommitted or Serializable. ... They all work more or less the same: depending on the transaction isolation level, other transactions can read the data changed or have to wait till the exclusive lock is lifted from the row or table. ...
    (microsoft.public.dotnet.framework.adonet)
  • Strange problem with JDBC and Oracle 10g
    ... Somehow delete statements inside a transaction do not seem to have any ... effect when I set the isolation level to *serializable* in Oracle 10g. ... With the default isolation level everything works fine. ... > END OF STMT ...
    (comp.lang.java.databases)
Quantcast