Isolation levels in Informix vs Oracle

--Concider the following situation: --An application is trying to analyze bank's balance --during the operational day - that is, some records --in the 'account' table might change while the query is running. --In Informix, DB2, MS SQL the only way to implement the --consistent query in such situation is to lock the entire --table. The above sounds really like a poor designed application/database. In informix you probably get the correct answer by locking the whole lot. this will upset customers. in Obstacle (Oracle) you may get an incorrect answer; do know Oracle too well however i can imagine that start the query and at the same time change a record for an account will render the answer useless. As far as i understand Oracle will not look at that record that was changed just after the report started. or worse a transaction started before the report started and completed just after the report started...i may be wrong.. i would imagine having an additional table which contains transactions. In this table i would have a column which represents the time the transaction took place or finished ...(Datetime year to fraction(5)) So in order to get a good answer one grabs the account information and all the transactions which took place before and equal to when the report started using the column which represents the transaction time. This using committed read and lock mode to wait; Or if you brave use dirty read (Assuming 99% of the started trx will get a commit???) ahum it can give you incorrect results!!!!! Superboer.

superboer wrote: > in Obstacle (Oracle) you may get an incorrect answer; > do know Oracle too well however i can imagine that start the query and at > the same time change a record for an account will render the answer useless. Actually no. Changing a record after a query has begun is irrelevant: The result will be unchanged. In fact performing the following: DELETE FROM mytable;COMMIT; while a query is running will not affect the result which is preordained at the point-in-time when the query was initiated. > As far as i understand Oracle will not look at that record that was changed > just after the report started. or worse a transaction started before the > report started and completed just after the report started...i may be wrong.. I can't tell if you are as what you wrote is unclear. What Oracle does is guarantee that without locking any record, block (page), or table it will give a result perfectly consistent with the point-in-time when the query began: No more ... no less. The demo I do for my classes to demonstrate this issue is as follows: Assume two bank accounts and you are transferring money between them. At some point in time an update must decrement the balance in one and a separate update but increment the balance in the other. In most databases you must lock both accounts until both update statements take place or risk a combined balance that is inaccurate. In Oracle that answer will always be consistent without locking. > Or if you brave use dirty read (Assuming 99% of the started trx will get a > commit???) > ahum it can give you incorrect results!!!!! > > Superboer. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond)