Re: Informix versus oracle

my apologies, comment taken out of context... the context of which you could not have possibly have known. allow me to clarify i simply meant that when in an sqlplus session, if I issue the command update, insert, whatever i have to actually type the word "commit" before I can see the changes effective outside of my session. In informix, i do not have to type the word commit for it to be relevant to all other sessions against the database... the way i understood it (perhaps incorrectly so), this occurred because of the way the rollback segments handle the before images of the data this was not meant as a slam against Oracle, merely an added step i frequently forgot, thus causing me some mild annoyances >>> Daniel Morgan <damorgan@exxesolutions.com> 05/29/03 09:59PM >>> Comments interspersed Brandt Edwin wrote: > yeah, which makes it really annoying to have to issue a "commit" after > every update.... What? Committing after every update, or for that matter every insert or delete goes against the most basic architecture of Oracle and most other serious RDBMS products with the possible exception of Sybase and SQL Server where they run out of locks rather quickly. Commits in Oracle, as in Informix, are performed when they are logically important. Not for any other reason. If you were taught or told otherwise about Oracle the person doing the instruction was either quoting from somehting they learned more than a decade ago or was just clueless. > <snipped> -- Daniel Morgan http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp damorgan@x.washington.edu (replace 'x' with a 'u' to reply)

Brandt Edwin wrote: > my apologies, comment taken out of context... > > the context of which you could not have possibly have known. > > allow me to clarify > > i simply meant that when in an sqlplus session, if I issue the command > update, insert, whatever i have to actually type the > word "commit" before I can see the changes effective outside of my > session. > > In informix, i do not have to type the word commit for it to be > relevant to all other sessions against the database... > > the way i understood it (perhaps incorrectly so), this occurred because > of the way the rollback segments handle the > before images of the data > > this was not meant as a slam against Oracle, merely an added step i > frequently forgot, thus causing me some mild annoyances > > >>> Daniel Morgan <damorgan@exxesolutions.com> 05/29/03 09:59PM >>> > Comments interspersed > > Brandt Edwin wrote: > > > yeah, which makes it really annoying to have to issue a "commit" > after > > every update.... > > What? > > Committing after every update, or for that matter every insert or > delete > goes against the most basic architecture of Oracle and most other > serious > RDBMS products with the possible exception of Sybase and SQL Server > where > they run out of locks rather quickly. > > Commits in Oracle, as in Informix, are performed when they are > logically > important. Not for any other reason. > > If you were taught or told otherwise about Oracle the person doing the > instruction was either quoting from somehting they learned more than a > decade ago or was just clueless. > > > <snipped> > > -- > Daniel Morgan > http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp > damorgan@x.washington.edu > (replace 'x' with a 'u' to reply) No need to apologize and you are correct. There are a number of reasons for the explicit commit. The most important of which is read consistency. Consider the following scenario. Bank account A has $1000 Bank account B has $1000 $500 is transferred from A to B You can do it one of two way. You can either update A set balance = balance - $500 then update B set balance = balance + $500 Or you can update B set balance = balance + $500 and then decrement B Either way without performing both updates followed by an explicit commit you have a point in time where someone running a query can see a total betwen the two accounts of either $1500 or $2500 both of which are incorrect. Informix solves the problem by blocking. In Oracle the explicit commit allows reads to no block writes and writes to not block reads. -- Daniel Morgan http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp damorgan@x.washington.edu (replace 'x' with a 'u' to reply)

"Daniel Morgan" <damorgan@exxesolutions.com> wrote > No need to apologize and you are correct. There are a number of reasons for > the explicit commit. The most important of which is read consistency. > Consider the following scenario. > > Bank account A has $1000 > Bank account B has $1000 > $500 is transferred from A to B > You can do it one of two way. > You can either update A set balance = balance - $500 then update B set > balance = balance + $500 > Or you can update B set balance = balance + $500 and then decrement B > Either way without performing both updates followed by an explicit commit > you have a point in time > where someone running a query can see a total betwen the two accounts of > either $1500 or $2500 > both of which are incorrect. Informix solves the problem by blocking. In > Oracle the explicit commit > allows reads to no block writes and writes to not block reads. I am curious to know how the following works in Oracle. In our application, two processes compete for the same row from the assignment queue table and the first one to grab it, does the processing. This is what we do (somewhat simplified) begin work ; select ... 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 ; Now we have multiple processes running the same application (for scalability) and any one of them picks up this row from the assignment table. I am hearing stories that the above code will fail because oracle (unlike informix) can not guarantee that only one session will pick up the row in the first SQL. Of course it can be achieved by a simple change in the second SQL by adding where status = 'U' . But then it would mean that the transaction was not serializable. Please do not take it as an Oracle bashing post. I am genuinely interested in knowing how it will work.

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