Re: Control for "Isolation level" at SELECT statement level

"Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message news:3F40F63C.D251AA92_at_exxesolutions.com...
> Hillel Eilat wrote:
>
> > Hi folks.
> >
> > Some RDBMS products support the following syntax format:
> >
> > SELECT .... <whatever...> WITH {READCOMMITTED | REPEATABLEREAD | ... |
> > SERIALIZABLE}
> >
> > The WITH clause provides the means for overriding the default isolation
> > level (or the one
> > specified upon SET TRANSACTION) at a specific SELECT statement.
> >
> > WITH clause is applicable for INSERT,UPDATE,DELETE as well.
> >
> > Is there an equvalent to the "WITH" clause in Oracle's SQL?
> >
> > Your answers will be appreciated.
> >
> > Hillel.
>
> No. But I am fascinated on how one session can allow dirty reads to other
> sessions that don't.

I believe the way it works is: the session that allows dirty reads does not apply a shared lock when reading. So there's no blocking for the read, even when the row has an exclusive lock on it from another session who is in the process of updating it.

>
> Seems like a capability searching for a reason to exist. And for which I
> can find none.

It's a capability used by other databases for non-blocking reads, and assumes that the user can tolerate a certain level of error. Oracle has multi-version read concurrency where writes never block reads (and vice versa) and therefore does not need to have dirty read to achieve non-blocking reads.

Cheers,
Dave

>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
>
Received on Mon Aug 18 2003 - 11:14:46 CDT