SEO

Hi,

As you know Informix offers 'set isolation to dirty read' - a facility
to read dirty buffers. I believe DB2 UDB too offers it but not Oracle.

What is the neccessity or justification for an RDBMS to offer such a
feature and do applications really need it ? I was told by an Oracle
guy that Informix and DB2 are 'forced' to offer this because of their
architecture and this is not a 'feature' as such ! He says Oracle can
offer it in a 'jiffy' by pointing to its undo tablespace (where before
images of a buffer are kept before modifications) but they will not as
this is not justified !

I myself (worked with Informix for 9 yrs but now into Oracle for past
1 yr ) feel its quite cool. However I would like to get expert
technical opinion. I don't intend to start a flame at all !

Many thanks
Prashant

"PK" <pk26au@yahoo.com> wrote in message
news:667bf0c6.0412150133.7e4ce123@posting.google.c om...
> Hi,
>
> As you know Informix offers 'set isolation to dirty read' - a facility
> to read dirty buffers. I believe DB2 UDB too offers it but not Oracle.
>
> What is the neccessity or justification for an RDBMS to offer such a
> feature and do applications really need it ? I was told by an Oracle
> guy that Informix and DB2 are 'forced' to offer this because of their
> architecture and this is not a 'feature' as such ! He says Oracle can
> offer it in a 'jiffy' by pointing to its undo tablespace (where before
> images of a buffer are kept before modifications) but they will not as
> this is not justified !
> I myself (worked with Informix for 9 yrs but now into Oracle for past
> 1 yr ) feel its quite cool. However I would like to get expert
> technical opinion. I don't intend to start a flame at all !

dirty read is very useful when the application gurantees that the data being
read is at that instant read-only. For e.g. in a transaction table where you
are generating report for yesterday. You know for sure that all new records
in that table will be for today only. Why bother locking. Dirty read will
do the job just as fine with minimum resource contention.

Similarly there are circumstances where committed read, repeatable read
and serializable read are also useful. . Each has different concurrency
level
and must be used appropriately.

The problem with most the developers is that they know jackshit about
isolation level and concurrency issues and they go with default with
whatever JDBC offers. On that count Oracle's versioning feature is
cool. It is really an idiot-proof feature. But beyond that it is nothing
to crow about.

SQL Server 2005 is offering the same versioning by calling itself
READ_CONSISTENCY. But unlike Oracle, it also offers other
isolation modes of Informix/Db2. Use them where they are appropriate.

to add more, because of Oracle's versioning feature, I feel that its
FOR UPDATE technique is less efficient. For e.g if there is a
work flow table where competing sessions try to grab the first
available row in the queue. May be that's why Oracle recommends
using AQ tables for such a task.

"rkusenet" <rkusenet@sympatico.ca> wrote in message
news:32aifdF3j06m1U1@individual.net...
>
> "PK" <pk26au@yahoo.com> wrote in message
> news:667bf0c6.0412150133.7e4ce123@posting.google.c om...
> > Hi,
> >
> > As you know Informix offers 'set isolation to dirty read' - a facility
> > to read dirty buffers. I believe DB2 UDB too offers it but not Oracle.
> >
> > What is the neccessity or justification for an RDBMS to offer such a
> > feature and do applications really need it ? I was told by an Oracle
> > guy that Informix and DB2 are 'forced' to offer this because of their
> > architecture and this is not a 'feature' as such ! He says Oracle can
> > offer it in a 'jiffy' by pointing to its undo tablespace (where before
> > images of a buffer are kept before modifications) but they will not as
> > this is not justified !
> > I myself (worked with Informix for 9 yrs but now into Oracle for past
> > 1 yr ) feel its quite cool. However I would like to get expert
> > technical opinion. I don't intend to start a flame at all !
>
> dirty read is very useful when the application gurantees that the data
being
> read is at that instant read-only. For e.g. in a transaction table where
you
> are generating report for yesterday. You know for sure that all new
records
> in that table will be for today only. Why bother locking. Dirty read will
> do the job just as fine with minimum resource contention.
>
> Similarly there are circumstances where committed read, repeatable read
> and serializable read are also useful. . Each has different concurrency
> level
> and must be used appropriately.
>
> The problem with most the developers is that they know jackshit about
> isolation level and concurrency issues and they go with default with
> whatever JDBC offers. On that count Oracle's versioning feature is
> cool. It is really an idiot-proof feature. But beyond that it is nothing
> to crow about.
>
> SQL Server 2005 is offering the same versioning by calling itself
> READ_CONSISTENCY. But unlike Oracle, it also offers other
> isolation modes of Informix/Db2. Use them where they are appropriate.
>
>
>
>
>

PK wrote:
> Hi,
>
> As you know Informix offers 'set isolation to dirty read' - a facility
> to read dirty buffers. I believe DB2 UDB too offers it but not Oracle.
>
> What is the neccessity or justification for an RDBMS to offer such a
> feature and do applications really need it ? I was told by an Oracle
> guy that Informix and DB2 are 'forced' to offer this because of their
> architecture and this is not a 'feature' as such ! He says Oracle can
> offer it in a 'jiffy' by pointing to its undo tablespace (where before
> images of a buffer are kept before modifications) but they will not as
> this is not justified !
>
> I myself (worked with Informix for 9 yrs but now into Oracle for past
> 1 yr ) feel its quite cool. However I would like to get expert
> technical opinion. I don't intend to start a flame at all !
>
> Many thanks
> Prashant
When all you have is a hammer everything looks like a nail....
Here is my take:
Dirty read shows data that potentially gets rolled back and hence there
is a potential to mess up data integrity in case of rollback.
Read consistency shows data that may be stale and hence there us a
potential to mess up data integrity in case of commit.

Both have their uses, both have their faults.

For transaction processing there is only one isolation level that is
correct: Repeatable Read/Serializable. Anything else has various degrees
of buyer beware.

Sidenote: SEQUENCE is "dirty read" by definition and it is quite popular
in Oracle :-)

Cheers
Serge

rkusenet wrote:

> "PK" <pk26au@yahoo.com> wrote in message
> news:667bf0c6.0412150133.7e4ce123@posting.google.c om...
>
>>Hi,
>>
>>As you know Informix offers 'set isolation to dirty read' - a facility
>>to read dirty buffers. I believe DB2 UDB too offers it but not Oracle.
>>
>>What is the neccessity or justification for an RDBMS to offer such a
>>feature and do applications really need it ? I was told by an Oracle
>>guy that Informix and DB2 are 'forced' to offer this because of their
>>architecture and this is not a 'feature' as such ! He says Oracle can
>>offer it in a 'jiffy' by pointing to its undo tablespace (where before
>>images of a buffer are kept before modifications) but they will not as
>>this is not justified !
>>I myself (worked with Informix for 9 yrs but now into Oracle for past
>>1 yr ) feel its quite cool. However I would like to get expert
>>technical opinion. I don't intend to start a flame at all !
>
>
> dirty read is very useful when the application gurantees that the data being
> read is at that instant read-only. For e.g. in a transaction table where you
> are generating report for yesterday. You know for sure that all new records
> in that table will be for today only. Why bother locking. Dirty read will
> do the job just as fine with minimum resource contention.

The problem with your analogy is that if by definition it is yesterday's
(or read-only) data and no changes are taking place you don't have
anything dirty to read. With read-only data any database, with any
transaction type, will give a valid answer.

Can you address the dirty read issue in a situation where there truly is
dirty data.

Thanks.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

rkusenet wrote:

> to add more, because of Oracle's versioning feature, I feel that its
> FOR UPDATE technique is less efficient. For e.g if there is a
> work flow table where competing sessions try to grab the first
> available row in the queue. May be that's why Oracle recommends
> using AQ tables for such a task.

It would certainly be my recommendation in Oracle to use Advanced
Queuing or Streams. But from my experience FOR UPDATE works in 99+%
of all similar situations. You lock it if you're first ... you don't
if otherwise.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

Serge Rielau wrote:

> Sidenote: SEQUENCE is "dirty read" by definition and it is quite popular
> in Oracle :-)
>
> Cheers
> Serge

By whose definition? Certainly none I have ever heard.

--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

DA Morgan wrote:
> Serge Rielau wrote:
>
>> Sidenote: SEQUENCE is "dirty read" by definition and it is quite
>> popular in Oracle :-)
>>
>> Cheers
>> Serge
>
>
> By whose definition? Certainly none I have ever heard.
>
The freedom of double-quotes Daniel. Open your mind:
If you open two connections to Oracle and perform a few NEXTVALs in each
without cout committing. Then you roll one connections transaction back
the values are "lost". But the second connection was very well affected
by the lost values: "Dirty read".
SEQUENCEs don't no squat about transactions and isolation levels.
Note that I don't blame anyone. Sequences do exactly what they are
designed to do, just like a regular dirty read (no double quotes) does
exactly what it's designed to do.

Cheers
Serge

"DA Morgan" <damorgan@x.washington.edu> wrote
> The problem with your analogy is that if by definition it is yesterday's
> (or read-only) data and no changes are taking place you don't have
> anything dirty to read. With read-only data any database, with any
> transaction type, will give a valid answer.

> Can you address the dirty read issue in a situation where there truly is
> dirty data.

This is red herring.

The 'dirty read' I mentioned in my example refers to the isolation level
provided by Informix/DB2/SS. I am not here to debate whether the
nomenclature
of dirty read is technically correct, English usage wise. If this pleases
you,
we can call it READ_UNCOMMITTED as it is known in ANSI lingo.

It was in response to the original poster's question about where different
isolation level is required. To quote him:-

"What is the neccessity or justification for an RDBMS to offer such a
feature and do applications really need it ?"

I gave one example, where if I were the developer, I would use only dirty
read for maximum performance. To claim that 'dirty read' isolation level is
useless in real world (which you have done in the past) is a bogus claim.

For the same example, Oracle will also give a valid answer, but not without
ascertaining for every block whether it has been updated since the query
began,
as part of its process for maintaining read consistency. Now this is
something
which has overhead. Nothing comes free.

Serge Rielau wrote:
> DA Morgan wrote:
>
>> Serge Rielau wrote:
>>
>>> Sidenote: SEQUENCE is "dirty read" by definition and it is quite
>>> popular in Oracle :-)
>>>
>>> Cheers
>>> Serge
>>
>>
>>
>> By whose definition? Certainly none I have ever heard.
>>
> The freedom of double-quotes Daniel. Open your mind:
> If you open two connections to Oracle and perform a few NEXTVALs in each
> without cout committing. Then you roll one connections transaction back
> the values are "lost". But the second connection was very well affected
> by the lost values: "Dirty read".
> SEQUENCEs don't no squat about transactions and isolation levels.
> Note that I don't blame anyone. Sequences do exactly what they are
> designed to do, just like a regular dirty read (no double quotes) does
> exactly what it's designed to do.
>
> Cheers
> Serge

I can't wrap my mind around your thought that sequence NEXTVAL in any
way relates to a dirty read. Here's why:

CREATE TABLE mytable (
mycol NUMBER);

CREATE TABLE t (
seqno NUMBER);

INSERT INTO t VALUES (1);
COMMIT;

INSERT INTO mytable
SELECT seqno
FROM t;

ROLLBACK;

UPDATE t
SET seqno = seqno+1;

COMMIT;

Do you see a dirty read? I don't. The fact that a transaction didn't
take place but the sequence number was lost is not a dirty read. So
I just don't get your thinking.

Note: No double quotes.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)