SEO

Hi t,

I'm not Serge, but the answer to that is yes.

As of 11.10, you can have pessimistic-locking, or optimistic locking.

Entirely configurable at the instance level.

The onconfig parameter is

USELASTCOMMITTED




----- Original Message ----
From: Tool <tool@thetool.com>
To: informix-list@iiug.org
Sent: Monday, June 18, 2007 1:26:57 PM
Subject: Re: Informix beats Oracle

Serge,

Got me to want to actually ask you a question. :-)

It's my understanding that all other DBMS products are pessimistic-locking, ORACLE
is optimistic-locking, Oracle being only one in this category of mainstream DBMS products
to have optimistic locking.

Does this mean Informix has both pessimistic _and_ optimistic locking?

Thanks,

-t-

Serge Rielau wrote:
> DA Morgan wrote:
>> No CTO has ever purchased an RDBMS based on efficient use of CPU.
>> Instead of following the bouncing ball how about asking IBM to make the
>> same changes to Informix they just made to Viper 2 so it will support
>> major business applications?
> Hmmm... nice stitching. IDS has traditionally watched Oracle closer than
> DB2 has, so many of the features you allude to are already present (like
> DECODE, UNIQUE, Oracle sequence syntax...). also IDS has "readers don't
> block writers, ..." which eliminated the perhaps single biggest
> stumbling block for porting Oracle apps.
>
> Cheers
> Serge

_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

Mark Jamison wrote:
> Hi t,
>
> I'm not Serge, but the answer to that is yes.
>
> As of 11.10, you can have pessimistic-locking, or optimistic locking.
>
> Entirely configurable at the instance level.
>
> The onconfig parameter is
>
> USELASTCOMMITTED
>

As I wrote answering to Serge, I believe this is not optimistic locking.
And we can configure it on instance level, session level, and depending on the
previous isolation level... An Informix DBA dream

Regards.

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

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

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.

-t-

Fernando Nunes wrote:
> Mark Jamison wrote:
>> Hi t,
>>
>> I'm not Serge, but the answer to that is yes.
>>
>> As of 11.10, you can have pessimistic-locking, or optimistic locking.
>>
>> Entirely configurable at the instance level.
>>
>> The onconfig parameter is
>>
>> USELASTCOMMITTED
>>
>
> As I wrote answering to Serge, I believe this is not optimistic locking.
> And we can configure it on instance level, session level, and depending
> on the previous isolation level... An Informix DBA dream
>
> Regards.
>

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

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

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
>

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
>>
>

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@thetool.com> wrote in message
news:MPZdi.50406$lk.4675@fe188.usenetserver.com...
> 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
>>
>

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.

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

DA Morgan wrote:
> 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.

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

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:
> 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
>>>
>>