Informix beats Oracle

#41

SET ISOLATION TO DIRTY READ reads rows without regard
for lock(s) held. That means it will never wait for any rows currently
locked by any other session. However it would also mean that
it will read in-transaction rows, that is rows which have been
updated but not yet committed or rolledback!!!. So theoretically
your DR session may end up reading rows which were never
committed. At first it may sound dangerous,
but for many situations in real life it is adequate.

"Tool" <tool (AT) thetool (DOT) com> wrote

Quote:

Well I guess I was thinking this was a bit more than "SET ISOLATION TO
DIRTY READ".

What's the difference between that and this new feature? Now I'm totally
bamboozled.
Does this mean that in IDS you could not read a locked row at all before
this new
feature? Or is this just a global dirty-read setting?

-t-

Fernando Nunes wrote:
Tool wrote:
Fernando, thanks too for your response. I read your website article and
it was good too.

Thanks!

I have been under the impression that this one feature is what Oracle
sells
to clients, that they have the best non-blocking database engine
available.
Perhaps this is a bit simplistic, but it is notable that applications
and
developers now have another choice of what engine to use if indeed this
is
similar to the Oracle implementation, and was not available in other
products.

I'm not speaking for IBM... standard disclaimer applies, but:

In practice, I think this has the same results. Using this, you won't
block when trying to read a row that has a lock (not a shared one, but an
insert/update/delete lock). You will get whatever was there (or
wasn't...) before the operation holding the lock.

However, the underlying implementation is AFAIK (I'm not a developer...)
completely different. Oracle is a versioned RDBMS like Postgres and I
believe some engines used in mySQL. Informix is NOT. The Informix
implementation is simpler (quicker?). If it hits a lock, it fetches the
value from logical logs.

SQL server has a similar implementation if I read and understood it's
documentation correctly (since v2005 if I recall correctly).

From my experience as DBA, this is THE feature that developers were
wishing for. From some talks with colleagues and some customers I don't
find the degree of enthusiasm I was expecting...

I'll be very happy if my daily customer migrates to IDS 11 and I can use
this feature. I'm "tired" of explaining the locking issues to developers
that were trained only in Oracle...

It will also make my daily discussions (friendly) with an Oracle DBA much
less boring, since we tend to fall in this specific difference

#42

Fernando Nunes wrote:

Quote:

In practice, I think this has the same results. Using this, you won't
block when trying to read a row that has a lock (not a shared one, but
an insert/update/delete lock). You will get whatever was there (or
wasn't...) before the operation holding the lock.

This would be a major step forward for Informix if this is what it
appears to be.

Can anyone confirm the following statement is true?

"Reads don't block writes and writes don't block reads and only
committed rows are visible."

And yes SQL Server finally got a measure of this with 2005 whereas
Oracle has had it for decades.

Thank you.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

#43

DA Morgan wrote:

Quote:

Fernando Nunes wrote:

In practice, I think this has the same results. Using this, you won't
block when trying to read a row that has a lock (not a shared one, but
an insert/update/delete lock). You will get whatever was there (or
wasn't...) before the operation holding the lock.

This would be a major step forward for Informix if this is what it
appears to be.

Can anyone confirm the following statement is true?

"Reads don't block writes and writes don't block reads and only
committed rows are visible."

I can.

Quote:

And yes SQL Server finally got a measure of this with 2005 whereas
Oracle has had it for decades.

Thank you.

#44

Thanks for the clarification Madison.

I can see where this would be something that needs a little more explaining
to the community, in a presentation of some kind. It appears that this is
going to be useful for high-volume transactions if I'm understanding this
correctly.

Thanks!!

-t-
PS thanks to the rest of you too, DC, Fernando, etc etc.!

Madison Pruet wrote:

Quote:

Tool wrote:
Well I guess I was thinking this was a bit more than "SET ISOLATION TO
DIRTY READ".

What's the difference between that and this new feature? Now I'm
totally bamboozled.
Does this mean that in IDS you could not read a locked row at all
before this new
feature?

not with committed reads

Or is this just a global dirty-read setting?

No

Dirty Read could return a row which is part of a current transaction and
which might be rolled back. Last committed read will only return
committed rows. The row might be in the process of being updated or
deleted, but the transaction will be returned the last committed version
of the row. However, it is always a version of the row which was
committed.

M.P.

-t-

Fernando Nunes wrote:
Tool wrote:
Fernando, thanks too for your response. I read your website article
and
it was good too.

Thanks!

I have been under the impression that this one feature is what
Oracle sells
to clients, that they have the best non-blocking database engine
available.
Perhaps this is a bit simplistic, but it is notable that
applications and
developers now have another choice of what engine to use if indeed
this is
similar to the Oracle implementation, and was not available in other
products.

I'm not speaking for IBM... standard disclaimer applies, but:

In practice, I think this has the same results. Using this, you won't
block when trying to read a row that has a lock (not a shared one,
but an insert/update/delete lock). You will get whatever was there
(or wasn't...) before the operation holding the lock.

However, the underlying implementation is AFAIK (I'm not a
developer...) completely different. Oracle is a versioned RDBMS like
Postgres and I believe some engines used in mySQL. Informix is NOT.
The Informix implementation is simpler (quicker?). If it hits a lock,
it fetches the value from logical logs.

SQL server has a similar implementation if I read and understood it's
documentation correctly (since v2005 if I recall correctly).

From my experience as DBA, this is THE feature that developers were
wishing for. From some talks with colleagues and some customers I
don't find the degree of enthusiasm I was expecting...

I'll be very happy if my daily customer migrates to IDS 11 and I can
use this feature. I'm "tired" of explaining the locking issues to
developers that were trained only in Oracle...

It will also make my daily discussions (friendly) with an Oracle DBA
much less boring, since we tend to fall in this specific difference

#45

"DA Morgan" <damorgan (AT) psoug (DOT) org> wrote

Quote:

Fernando Nunes wrote:

In practice, I think this has the same results. Using this, you won't
block when trying to read a row that has a lock (not a shared one, but an
insert/update/delete lock). You will get whatever was there (or
wasn't...) before the operation holding the lock.

This would be a major step forward for Informix if this is what it
appears to be.

Can anyone confirm the following statement is true?

"Reads don't block writes and writes don't block reads and only
committed rows are visible."

And yes SQL Server finally got a measure of this with 2005 whereas
Oracle has had it for decades.

And unlike Oracle, both SS and Informix offer other isolation levels
also, which means, that developers can use them appropriately.

#46

Data Cruncher wrote:

Quote:

"DA Morgan" <damorgan (AT) psoug (DOT) org> wrote in message
news:1182298306.525484 (AT) bubbleator (DOT) drizzle.com...
Fernando Nunes wrote:

In practice, I think this has the same results. Using this, you won't
block when trying to read a row that has a lock (not a shared one, but an
insert/update/delete lock). You will get whatever was there (or
wasn't...) before the operation holding the lock.
This would be a major step forward for Informix if this is what it
appears to be.

Can anyone confirm the following statement is true?

"Reads don't block writes and writes don't block reads and only
committed rows are visible."

And yes SQL Server finally got a measure of this with 2005 whereas
Oracle has had it for decades.

And unlike Oracle, both SS and Informix offer other isolation levels
also, which means, that developers can use them appropriately.

And unlike you some people read the docs.

Oracle offers other isolation levels too.
http://download-west.oracle.com/docs...t.htm#CNCPT621

If you are going to bash something at least choose a valid point on
which to bash. One thing I find fascinating about some in this crowd
is the willingness to close their eyes and throw punches.

Those of us who actually know Oracle throw them too. But ours are on
target.
--
Daniel A. Morgan
Puget Sound Oracle Users Group
www.psoug.org

#47

Quote:

And unlike you some people read the docs.

Oracle offers other isolation levels too.
http://download-west.oracle.com/docs...t.htm#CNCPT621

If you are going to bash something at least choose a valid point on
which to bash. One thing I find fascinating about some in this crowd
is the willingness to close their eyes and throw punches.

Those of us who actually know Oracle throw them too. But ours are on
target.

When I said SS/Informix offers other choice, it means
that the developers has a choice to bypass CPU intensive
versioning mechanism. Does Oracle offer a way to bypass
check for block versioning and then fetch from UNDO segment.
AFAIK - no. The only other choice they have is actually
worse from concurrency point of view, like SERIALIZABLE.

#48

"Data Cruncher" <dcruncher4 (AT) aim (DOT) com> wrote

Quote:

When I said SS/Informix offers other choice, it means
that the developers has a choice to bypass CPU intensive
versioning mechanism. Does Oracle offer a way to bypass
check for block versioning and then fetch from UNDO segment.
AFAIK - no. The only other choice they have is actually
worse from concurrency point of view, like SERIALIZABLE.

or it bypasses SCN check for read-only tablespaces, which
works for only read-only tables.

#49

DA Maroon wrote:

Quote:

If you are going to bash something at least choose a valid point on
which to bash. One thing I find fascinating about some in this crowd
is the willingness to close their eyes and throw punches.

Those of us who actually know Oracle throw them too. But ours are on
target.
--
Daniel A. Morgan
Puget Sound Oracle Users Group
www.psoug.org

Why? That's never stopped you.

With respect to Oracle, I can't stand how you have to consider the database
that you want to attach to as the "user". Very bizzare. And don't get me
started on Quad Tree indexing. Oh and that nasty bug with their r-tree
spatial indexing that is a major performance problem in 9 that supposedly
got fixed in 10.

Then there are their memory leaks in their JDBC drivers...

Yes Daniel, I grok software development against Oracle.

__________________________________________________ _______________
Don’t miss your chance to WIN $10,000 and other great prizes from Microsoft
Office Live http://clk.atdmt.com/MRT/go/aub0540003042mrt/direct/01/

#50

Data Cruncher wrote:

Quote:

And unlike you some people read the docs.

Oracle offers other isolation levels too.
http://download-west.oracle.com/docs...t.htm#CNCPT621

If you are going to bash something at least choose a valid point on
which to bash. One thing I find fascinating about some in this crowd
is the willingness to close their eyes and throw punches.

Those of us who actually know Oracle throw them too. But ours are on
target.

When I said SS/Informix offers other choice, it means
that the developers has a choice to bypass CPU intensive
versioning mechanism. Does Oracle offer a way to bypass
check for block versioning and then fetch from UNDO segment.
AFAIK - no. The only other choice they have is actually
worse from concurrency point of view, like SERIALIZABLE.

No bypass for multiversioning with the standard isolation level.
But then Oracle gains functionality Informix hasn't had, except
as an option with Cheetah. Functionality has its cost.

The reverse question from the other side is ... having fun with
row and page locking and lock escalation?

In truth the cost of multiversioning is quite small as compared
with the cost of other functionality built in during the last
several versions which few of you know about because you don't
actually study the competition you just throw random punches in
the dark.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Informix beats Oracle

comp.databases.informix comp.databases.informix

Re: Informix beats Oracle - 06-19-2007 , 07:49 PM

Re: Informix beats Oracle - 06-19-2007 , 08:11 PM

Re: Informix beats Oracle - 06-19-2007 , 08:25 PM

Re: Informix beats Oracle - 06-19-2007 , 08:28 PM

Re: Informix beats Oracle - 06-19-2007 , 08:40 PM

Re: Informix beats Oracle - 06-19-2007 , 09:18 PM

Re: Informix beats Oracle - 06-20-2007 , 05:09 AM

Re: Informix beats Oracle - 06-20-2007 , 05:49 AM

Re: Informix beats Oracle - 06-20-2007 , 07:54 AM

Re: Informix beats Oracle - 06-20-2007 , 09:18 AM