RE: Informix versus oracle

I am not sure what you are wanting to know here.... If it is a difference between Oracle and Informix on how locking may work, etc... Not much The only real significant difference that I can thik of is that Oracle keep it read consistant - meaning the rows are still accessible (cannot be altered, but can be read) without set isolation mode The only other major difference may be with begin work - Oracle generally is is in a transactional state, so begin work statement is not needed. If this is what you were looking for, please let me know, or let me know how in depth you want the answer to be. -----Original Message----- From: Mark Townsend [SMTP:markbtownsend@attbi.com] Sent: Saturday, May 31, 2003 12:22 AM To: informix-list@iiug.org Subject: Re: Informix versus oracle > I am curious to know how the following works in Oracle. > begin work ; > > select ... for update > from table > where status = 'U' ; /* U means unassigned */ > update table > set status = 'A' /* A means assigned and hence removed from the work queue */ > where current of .. > > commit work ;

Dusty Haas wrote: > I am not sure what you are wanting to know here.... > > If it is a difference between Oracle and Informix on how locking may work, > etc... Not much > > The only real significant difference that I can thik of is that Oracle keep > it read consistant - meaning the rows are still accessible (cannot be > altered, but can be read) without set isolation mode > > The only other major difference may be with begin work - Oracle generally > is is in a transactional state, so begin work statement is not needed. > > If this is what you were looking for, please let me know, or let me know > how in depth you want the answer to be. > > <snipped> A few quick corrections and comments. You are correct that in Oracle rows are 'still accessible' but you are incorrect that they can not be altered. It is possible for multiple users to simultaneously alter a single record in Oracle. Of course this is also possible in just about every other product and is called the 'lost update'. As the chances of that are insignificant in most situations the issue can usually be ignored. But for those circumstances where it is possible Oracle has the FOR UPDATE clause that is used to intentionally lock a record, or records, so that it can not be updated or deleted by another user or process. The FOR UPDATE lock is released with either COMMIT or ROLLBACK. But this lock, as all others, do not block a read. You are also incorrect when you state that Oracle 'generally is in a transaction state'. Oracle is always in a transaction state. There is no need, or syntax, to indicate that a transaction is beginning as the statement INSERT, UPDATE, or DELETE is indication enough for the database engine. -- Daniel Morgan http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp damorgan@x.washington.edu (replace 'x' with a 'u' to reply)