5.2 ACID property of transactional requirements

---

All the transactional systems are required to possess ACID properties (atomicity, consistency, isolation, and durability).

Atomicity describes the property that all operations taking place under a transaction form a single, indivisible unit of work. The transaction either succeeds as a single unit or fails as a single unit. Consistency is the characteristic that all data operations within the same transaction either commit together or they are aborted. Aborted data operations cause modified data to be restored to its original state. The isolation property states that the data operations associated with separate transactions do not interfere with one another. Data modifications made by one transaction that have not yet been committed are not visible to other transactions. Data updates are therefore not visible to other transactions until the data modification has been guaranteed to be permanent. This guarantee that committed data is permanently stored is the property of durability. Durability means that even if there is a system failure at an inappropriate time (i.e., before the committed data is actually written to disk), it is required that either all the data is saved or all the data is rolled back.

Every transactional system is required to adhere to these basic rules of transaction management. In any business processing transactional information (e.g., enterprise resource planning, supply chain management, transportation logistics, customer care and billing, or defense systems), it is required to follow these basic ACID properties or else data can not be guaranteed to be useful in any form.

One of the prominent rules that would be clear to every transactional management architect, database administrator, or application developer, should be that either the entire transaction completes successfully or none is allowed to succeed. This is required to ensure that all parts of the data are saved to disk. For example, in an inventory system, when a part is issued to the customer, the customer's account should be updated with the item and the item master should be reduced by the item that was removed and sold to the customer. In a situation where the update to the customer's account has completed successfully, but the system failed while making updates to the item master, the entry in the customer's account should not be saved but rather undone. This means that the entire transaction succeeds successfully or no part is allowed to succeed, otherwise in our example above the accounting books would be out of sync. There would be items showing as sold, while the actual quantity of items in stock remained unchanged.

Another example would be a banking operation. A transfer of funds between two accounts (the transaction or logical unit of work) should include the debit to one account (performed by one SQL statement) and the credit to another account (performed by another SQL statement). Both actions should either fail or succeed together as a unit of work; the credit should not be committed without the debit. Other unrelated actions, such as a new deposit to the same account, should not be included in the transfer of funds transaction.

Oracle RDMS is not any different; actually this is one of the databases that strictly follows the implementation of these properties of transactional management. That is probably why most enterprise systems today use Oracle as their primary system of recording transactional data.

In a single instance/stand-alone configuration of Oracle, ACID properties provide read consistency such that users can only view committed data, and there is no visibility of in-flight transactions, i.e., transactions that are in progress are only visible to the process that owns the transaction. These are some of the simple basic rules of transactional integrity that Oracle's architecture ensures in a single or stand-alone configuration.

If Oracle follows this transactional behavior in a single stand-alone configuration of the database, it is guaranteed that these rules are also followed in a given instance on a clustered configuration. However, in a clustered database configuration the transactional behavior does not end with a single instance but with many instances communicating and sharing information through the cluster interconnect. So basically Oracle needs to ensure the transactional behavior at the clustered level plus at the single stand-alone instance level.

In Oracle 9i, Oracle has implemented the cache fusion architecture that follows sharing of transactional data across the cluster interconnect making it possible to transfer information much quicker compared to the prior versions such as OPS. (Under this architecture, transaction management has shifted from within a single instance to multiple instances across the network.)

Under OPS, as users familiar with the system would recall, when a block owned by one instance was required by another instance based on a user access, the requesting instance will request the owning instance to write the information back to disk for the requesting instance to read the information back form disk. This writing allowed recovery of the transaction in the case of failure (maintaining transactional integrity). On the other hand, this architecture created performance issues.

Under the new architecture, while the block is not forced to disk, copies of these blocks are retained in memory of the owning instance before a copy is sent across the cluster interconnect to the requesting instance. Retaining a copy or PI helps in the recovery operation when the requesting instance (holding the latest copy of the change) fails.

How does one ensure all this integrity of transactional behavior? How does Oracle ensure that:

• Only completed transactions are visible by multiple users concurrently accessing data from multiple instances?

• Only committed transactions are made visible to users?

• All changes complete successfully or none complete?

We will discuss various scenarios to analyze these transactional behaviors of Oracle under a clustered environment using RAC through the rest of this chapter. First let us start our discussion by examining the transactional behavior in a single instance non-clustered database environment.

---