Most of the information is this thread is right, let me just try to organize
it a little bit.
IsolationLevel.Snapshot was introduced to be used in databases that
distinguish multiversioning-based serializable isolation from locking-based
serializable isolation (ok, that's SQL Server
Both protect applications from the same phenomena, but in different ways and
there are some semantic implications to it (e.g. contention versus
optimistic locking)
In SQL Server you can also enable "read-committed-snapshot" to get
snapshot-based read committed isolation level. Since that's a database-level
option, you can just use IsolationLevel.ReadCommitted.
In Oracle, isolation is implemented through versioning so existing
isolation-levels already implied versioning-like semantics, through
different phenomena would show up or not depending on which of the two
supported isolation levels is picked.
As for snapshot isolations being for reads, that's not strictly accurate.
Snapshot isolation helps reduce contention, and as a trade-off you have more
overhead on reads and writes (this can vary *a lot* depending on the
workload, so don't take this as a general guidance). Contention reduction
means, for example, that even if you have long-running read operations (e.g.
long-running SELECTs for reporting) you can still do updates without
waiting. Same the other way around: if you have long-running operations that
changed rows, your SELECTs don't need to wait until that operation finishes
before reading values.
--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.
This posting is provided "AS IS" with no warranties, and confers no rights.
Sahil Malik said:
Hey atleast that is what OracleClient MSDN says. I decompiled ODP.NET (10)
using reflector and suspicions confirmed there too.
As I am writing the book, I am giving attention to Oracle wherever it
warrants it. In besides updation and transactions - there isn't much else.
- Sahil Malik
http://codebetter.com/blogs/sahil.malik/
Frans Bouma said:
Sahil Malik [MVP] wrote:
Frans,
Yeah thats true, but snapshot is more for reads .. isn't it? I mean,
how
does it apply to writes? Writes in oracle are either Readcommitted or
Serializable.
As I understand it, normally, a writer in Oracle writes to its own
version, so a reader won't see the change until the transaction is
committed.
I believe IsolationLevel.Snapshot is not supported in .NET 2.0
OracleClient - which seemed hella odd, so I thought I'd post a
message
here
and find out for sure if it is going to be supported at all.
In that case it's pretty odd indeed, as it should be the normal
behavior. But I have to look up the details as well, and Oracle docs are
erm... massive
FB
The default IMO is ReadCommitted for both SQL Server & Oracle.
- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/
message
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
. 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#)
------------------------------------------------------------------------
