Re: Informix beats Oracle
Discussion of IDS 11's new "last committed read" isolation level and how it compares to Oracle's multi-versioning. Tool asked how it differs from DIRTY READ. The answer given: dirty read can return uncommitted rows that may be rolled back, whereas last committed read never blocks on an insert/update/delete lock and always returns the last committed version of the row. Fernando noted Informix isn't a versioned engine (it fetches the prior value from the logical logs), and Madison Pruet confirmed that writers don't block readers and only committed rows are visible, though Fernando was unsure about behaviour under REPEATABLE READ/SERIALIZABLE.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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 :) >> >
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 :) >>> >>
DA Morgan wrote: > Fernando Nunes wrote: > > 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." > Not absolutely sure, if the reader is in REPEATABLE READ/SERIALIZABLE... I will have to test it... But in the new isolation level writers won't block readers... The fact that Informix have been used in so many situations without this, would contradict the importance that you (and me) give to this feature... :) > And yes SQL Server finally got a measure of this with 2005 whereas > Oracle has had it for decades. > Oracle and Postrgres have always had it, because their design is different. I suppose in Oracle/Postgres writers won't block against readers in SERIALIZABLE... -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...