Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Control for "Isolation level" at SELECT statement level

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

From: Dave Hau <davehau-no-spam-123_at_no-spam.netscape.net>
Date: Sun, 17 Aug 2003 23:17:44 GMT
Message-ID: <s2U%a.1826$3q.781@newssvr25.news.prodigy.com>


"Hillel Eilat" <hillel_at_attunity.co.il> wrote in message news:bhnqgm$dei$1_at_news2.netvision.net.il...
> 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?

No, but you can put the read in a transaction and get similar results:

set transaction isolation level {serializable | read commited}; select ... <whatever>;
commit;

The default isolation level for a transaction is read committed. If you set it to serializable, what this does in Oracle is your select will be processed with the database "frozen" at the point in time (SCN) when the "set transaction isolation level serializable" is processed. You don't see any data committed after that and before the commit. This is a bit different than in other databases where the isolation levels repeatable read and serialization will cause a shared lock for the read to be held until commit. In Oracle, reads never block writes, and vice versa, even when you are operating in the serializable isolation level.

Another thing to note is that Oracle only provides two of the four isolation levels in SQL92, i.e. read committed and serializable. Oracle does not have read uncommitted because it does not need it. Read uncommitted is used to achieve non-blocking reads in many other databases (meaning you don't apply a shared lock when reading a row). Oracle uses multi-version read concurrency and does not use shared locks in reads, therefore reads are always non-blocking. Regarding repeatable read, Oracle does not have an isolation level that provides repeatable reads within a transaction and yet does not prevent phantom reads. However, Oracle does provide statement level read consistency even in the "read committed" isolation level. Many other databases (including DB2) does not provide statement level read consistency at the "read committed" isolation level.

Cheers,
Dave

>
>
>
> Your answers will be appreciated.
>
>
>
> Hillel.
>
>
>
>
>
>
>
>
>
Received on Sun Aug 17 2003 - 18:17:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US