Re: Informix beats Oracle

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@xxxxxxxxxxx> wrote in message
news:MPZdi.50406$lk.4675@xxxxxxxxxxxxxxxxxxxxxxxxx
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 :)




.

Relevant Pages

  • Re: Informix beats Oracle
    ... What's the difference between that and this new feature? ... similar to the Oracle implementation, and was not available in other products. ... Using this, you won't block when trying to read a row that has a lock. ... I'm "tired" of explaining the locking issues to developers that were trained only in Oracle... ...
    (comp.databases.informix)
  • Re: Locking question when using Select clause with For Update and Skip locked
    ... This is working fine in SQL Server and multiple session can get the ... But in Oracle the first session only return 1 row but locks all the ... It looks like both the session got the ROW-X lock but one session is ...
    (comp.databases.oracle.server)
  • Re: Deadlock - advice needed
    ... Lock table order_release_bp in share mode; ... Resource Name process session holds waits process session holds waits ... After reading about the Oracle locking, my guess is the the stored ... "Unindexed foreign keys cause DML on the primary key to get a share row ...
    (comp.databases.oracle.server)
  • Re: row locking and inserts
    ... I am working in an application in which I want to lock a set of rows ... session 1> begin transaction ... session 1> commit ... You could get an Oracle DB fairly easily - download the free Oracle ...
    (comp.databases.oracle.server)
  • Re: RAC internals - GLOBAL ENQUEUE SERVICES DEADLOCK DETECTED
    ... >> lower and upper 4 bytes of the transaction identifier where the lock ... >> user session for deadlock lock 0x7553ab14 ... > The Co-operative Oracle Users' FAQ ... the session that later detected ORA-60 ...
    (comp.databases.oracle.server)