Sahil
Tue Apr 05 20:15:36 CDT 2005
Got it !!! Thanks
- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/
"Pablo Castro [MS]" <pablocas@online.microsoft.com> wrote in message
news:uEDr$8jOFHA.2728@TK2MSFTNGP15.phx.gbl...
> read-committed-snapshot is a database-level option. Basically, you have to
> do at least this to enable the snapshot infrastructure:
>
> ALTER DATABASE <your db name> SET ALLOW_SNAPSHOT_ISOLATION ON
>
> at this point, you have the option of also doing this:
>
> ALTER DATABASE <your db name> SET READ_COMMITTED_SNAPSHOT ON
>
> If you do this, then SET TRANSACTION ISOLATION LEVEL READ COMMITTED
implies
> read-committed-snapshot, otherwise it's the default (non-snapshot based).
>
> --
> Pablo Castro
> Program Manager - ADO.NET Team
> Microsoft Corp.
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
>
> "Sahil Malik [MVP]" <contactmethrumyblog@nospam.com> wrote in message
> news:OvrvAFgOFHA.3144@tk2msftngp13.phx.gbl...
> > Fantastic answer Pablo and this clarifies a lot.
> >
> > I have another quick question.
> >
> > If I want to use read-commited-snapshot in SQL2k5, through ADO.NET, do I
> > use
> >
> > ReadCommitted
> > or do I use
> > ReadCommited | Snapshot
> >
> > Strange the binary values are such that they can be or'ed. So I was
> > curious
> > :)
> >
> > - Sahil Malik [MVP]
> >
http://codebetter.com/blogs/sahil.malik/
> >
> >
> >
> > "Pablo Castro [MS]" <pablocas@online.microsoft.com> wrote in message
> > news:uWC5h6fOFHA.2604@TK2MSFTNGP10.phx.gbl...
> >> 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" <contactmethrumyblog@nospam.com> wrote in message
> >> news:O3caPnSOFHA.2132@TK2MSFTNGP14.phx.gbl...
> >> > 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 [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> wrote in
> >> > message
> >> > news:#fIq9VSOFHA.3808@TK2MSFTNGP14.phx.gbl...
> >> >> 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/
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> wrote in
> >> >> > message
> >> >> > news:eOZQ4BPOFHA.604@TK2MSFTNGP10.phx.gbl...
> >> >> >
> >> >> >>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#)
> >> >>
>
>>>------------------------------------------------------------------------
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >> --
> >>
>
>>> ------------------------------------------------------------------------
> >> >> Get LLBLGen Pro, productive O/R mapping for .NET:
> >
http://www.llblgen.com
> >> >> My .NET blog:
http://weblogs.asp.net/fbouma
> >> >> Microsoft MVP (C#)
> >>
>
>>> ------------------------------------------------------------------------
> >> >
> >> >
> >>
> >>
> >
> >
>
>