Dear,

I have a big problem.

When we print a sale invoice, on a Informix DB on an instance of a program we can see the record in the database only with an "update", but in an Oracle database using the same update can not display the record in the database until we perform a commit to the database.

Can anyone explain why this?

Can't explain because I have not touched informix in 20 years. I am by far no expert on this, but just from the first thing that pops into my mind is - transactional updates. For example, you have a complicated process that updates 10 or 15 tables, - in Oracle all of the updates happen at commit time. So if the program aborts for any reason, then Oracle will use the rollback segments to undo the transaction. This sounds like informix updates each table as the db.update hits - if something aborts how does informix rollback the changes? Not sure on that. And maybe I have this all wrong and we need a real programmer to answer.

Thank you Mark,

So, is a complex problem please if you know someone who can help me infinitely grateful to you.

As far as I know:

Informix allows "dirty read" (=you see uncommitted data).

Oracle gives you allways read consistency of data (no dirty read) from the point of time you fire the "select ..." statement.

Regards,

Kozure

Thank you very much for your response ...

Now I have one additional question. We, somehow, can configure the Oracle to work on dirty.read scheme.

As far as I knwo it is not possible to use dirty reads in oracle.

Can you explain your example why you need this feature.

Regards,

Kozure

Ofcourse, we are printing a sale invoice and need a certain part of the report refer to the table and ask by cisli245.ityp and cisli245.idoc fields, these fields are updated before of this part but commit is performed later. We in our development environment having Informix and works perfectly for me but on the client maintain Oracle and does not work for them.

Like Kozure is telling you it will not work in Oracle. Oracle always shows you consistent data. There is no workaround for that.

Guess you have to redesign your program: instead of using data out of table cisli245 can't you read the data out of the sales/warehouse tables?

Out of some oracle documentation:

Read Consistency

Read consistency, as supported by Oracle, does the following:

Guarantees that the set of data seen by a statement is consistent with respect to a single point in time and does not change during statement execution (statement-level read consistency)

Ensures that readers of database data do not wait for writers or other readers of the same data

Ensures that writers of database data do not wait for readers of the same data

Ensures that writers only wait for other writers if they attempt to update identical rows in concurrent transactions

The simplest way to think of Oracle's implementation of read consistency is to imagine each user operating a private copy of the database, hence the multiversion consistency model.

Read Consistency, Undo Records, and Transactions
To manage the multiversion consistency model, Oracle must create a read-consistent set of data when a table is queried (read) and simultaneously updated (written). When an update occurs, the original data values changed by the update are recorded in the database undo records. As long as this update remains part of an uncommitted transaction, any user that later queries the modified data views the original data values. Oracle uses current information in the system global area and information in the undo records to construct a read-consistent view of a table's data for a query.

Only when a transaction is committed are the changes of the transaction made permanent. Statements that start after the user's transaction is committed only see the changes made by the committed transaction.

The transaction is key to Oracle's strategy for providing read consistency. This unit of committed (or uncommitted) SQL statements:

Dictates the start point for read-consistent views generated on behalf of readers

Controls when modified data can be seen by other transactions of the database for reading or updating

Hi Han,

I comment you that your help was invaluable.

I understand you that the first alternative is to modify the source code and in this way we solve that problem, my question to you was just for general knowledge.

Thank you.

Informix provides a number of isolation levels, including DIRTY READ, COMMITTED READ and SERIALIZABLE (there are a number of others which I won't bore you with).

With Informix, a transaction in progress sees the results of its own updates; it may make incremental changes to a single record. Other transactions may, or may not, see those changes; it depends on the isolation level of the reading transaction. Most sane programs run with at least COMMITTED READ isolation and won't see the changes of another transaction until the other transaction is committed. Some programs will use SERIALIZABLE isolation so that it will appear to them as if there are no other transactions changing the database while they are operating. As long as the separate transactions are modifying separate parts of the database, this causes no problems.

If a transaction is rolled back, there is more work to be done at the end of an Informix transaction than in Oracle (because changes have already been made in the in-memory images of the changed data pages, some of which may have been flushed to disk). However, if a transaction is committed, there is 'less' work to be done (because the in-memory images of the data pages have already been changed). There is book-keeping work in both systems; the differences are not huge.

Informix databases can be created with one of two transaction modes (simplifying slightly). The most usual one treats each statement as an isolated transaction unless you explicitly start a larger one with a BEGIN WORK statement (similar to 'AutoCommit On'). The alternative mode (in a 'MODE ANSI' database) is what the SQL standard mandates, that a transaction is 'always' in progress until you commit it or roll it back. Again, both modes have their uses. It is probably fair to say that more Informix databases run with 'AutoCommit On' mode than with 'MODE ANSI'.

Overall, it is simply a different way of doing business from the way Oracle and other DBMS using MVCC work. Informix's methods are largely consistent with the way that DB2 works. Both systems have pros and cons.