Understanding the Data Flow

We'll look at the data flow first. This will allow us to get an overall view of the needs that are met by the architectural elements. Once the general needs are understood, it is much easier to understand why Informix is doing what it does.

You'll find that the overall Informix system architecture is a tradeoff between multiple factors. Some decisions are made to enhance speed. Others sacrifice speed for security. Still others sacrifice speed and security for memory or disk savings. Like any vendor, the people at Informix have tried to present a product that offers tradeoffs that are reasonable to the largest possible audience. In some cases, the DBA can work around these tradeoffs by tuning the database or the application. Sometimes, though, the DBA just has to admit that the database has some weaknesses that need to be worked around. Although some of the limitations and constraints seem to be arbitrary, most often they make sense when viewed in the context of database operations as a whole.

While IDS was a major rewrite of the engine code from OnLine, one must really look at IDS as a natural progression from OnLine. Most of the concepts from OnLine apply to IDS. IDS starts where OnLine left off.

Informix engines are multiuser relational databases that support transactions. Understanding what these two terms really mean will make understanding the data flow much easier.

Informix Engines: Multiuser RDBMSs

A multiuser relational database management system (RDBMS) must do much more than simply store and retrieve data. It must maintain the integrity of the data and ensure that the user is using the "cleanest" data available.

Most of the finer points of the multiuser design are there to help act as a traffic cop when different users want to work with the same data at once. Informix has implemented a series of locks and isolation levels that manage these multiuser conflicts.

Locks are internal data flags that determine who gets to modify or read data at any particular time. Informix has locks available at many levels, depending upon the user's needs. These locks are available at the database level, locking an entire database. Locks are available at the table level, locking an entire table. They are also available at the page level within a table. Page-level locking is the default in Informix databases. If you don't specify locking levels, you will get page-level locking. For the finest control of locking granularity,

Informix allows locks to be placed at the row level. There are also locks at the index and at the byte level that are used internally by Informix.

Along with locking comes the concept of isolation levels. Isolation levels are transaction level commands that specify the levels of "cleanness" the user will accept. The least clean isolation level is dirty read, in which the user really doesn't care if someone is changing the data while the user is working with it. Isolation levels range up to repeatable read in which the database has to ensure that a query will return the same rows if it is run twice in a row with no other actions in between. We'll go further into these items in the section on tuning.

Informix Engines: Support Transactions

Informix also supports the concept of transactions. Supporting transactions begins with the admission that lots of things can go wrong in an RDBMS. Most of these things can have disastrous results if the data is left in an inconsistent state.

The textbook example of transactions is designing a database to handle the accounting transactions at a bank. Such transactions consist of two balancing debit and credit transactions to two separate tables. Suppose you have two tables, bank_cash_on_hand and customer_account. To simplify matters, consider that each table has one column, balance. When the customer comes in and withdraws $1,000, the following two SQL statements are generated:

update bank_cash_on_hand set balance=balance - 1000;
update customer_account set balance=balance - 1000;

(Ignore the credits and debits, DBAs aren't accountants!)

Clearly if one of the above SQL statements completes successfully and the other fails, the bank's books will be out of balance. What kind of failure could cause this?

DBAs are finding new kinds of failures daily. A needed table could be locked. The disk could fill up. The system could crash after completing the first SQL statement. If the database were distributed over two machines, one machine could crash or the network connection between the two could fail. Possible failure modes could fill a book.

What transaction processing does is to consider the separate SQL statements as a single entity. Any changes made to the database while inside a transaction are not made permanent until the transaction is committed with an SQL statement. The database designer and application designers build their systems to assure that both parts of the transaction are completed before the program issues the commit statement. If the transaction is not committed, the changes will be rolled back to the original status.

If the particular database is using transactions, any changes are logged to logfiles as they are made. If, for some reason, the user decides either not to commit or to roll back the work, the logfiles are used to restore the data to its original status. The purposes and uses of these mechanisms will become clearer as we track the flow of data beginning from when the user makes a query.