Oracle and ACID

We all know Oracle database is ACID compliant. ACID stands for Atomicity, Consistency, Isolation and Durability. Even so, DBA and database user in general could not assume that if it is an Oracle database, it meets all ACID properties. An Oracle database has to be setup and used correctly to meet ACID properties. In some cases, tradeoff is made on ACID for other gains, such as performance.

Let’s start with atomicity. Atomicity means a transaction should be done all the way or nothing. For example, two rows are inserted into table a_table by two insert SQL statements, and when rollback is issued, both rows are removed from table. But what happens if a trigger with autonomous transaction is fired on insert for a_table and the trigger inserts some records into another table b_table when it fires? Even the data in a_table is rolled back, the data in b_table will not be rolled back. Therefore, atomicity is not met. If the trigger is for auditing, such behavior is by design to meet business requirement. Another example is that two rows are updated into table a_table by two update SQL statements, and when rollback is issued, both rows rollback to their original values. Later, user who issued the rollback transaction wants to redo the rollback transaction but could not figure out the new values to update. So he does flashback query to get the value and reapplies them to the table a_table. Unfortunately, the flashback query time falls between two update statements and as the result, only second update of the transaction with two updates are executed. In other word, the transaction is half way completed and violates atomicity. Technically, these are two transactions in this case, but it is one in business sense.

The second rule of ACID is consistency. It requires the database in a consistent state before and after a transaction, does not matter the transaction is committed or rolled back. Oracle maintains the consistent state with constraints, including referential constraints. Business rules can be maintained by triggers or other means. But if the constraints are disable or non-validated, or trigger is not behaved as design, the database can end up in a no consistent state after a transaction.

The third rule of ACID is isolation. A transaction should not see the data changes of other transactions happened after the transaction is started. By default, Oracle supports “read committed” out of box. Therefore, Nonrepeatable read and Phantom read are permitted. To achieve true isolation, Isolation level should be set at serializable, so the session will not see the changes of other session until the transaction is finished. Oracle implements it by using multi-version read consistency and optimistic locking at data block level.

The last rule of ACID is durability. Once the transaction is committed, the change can not be lost. To ensure that, Oracle must in archive log mode and at least one copy of backup, all archive logs since the backup, one copy of redo and one copy of control file available. Additionally, no objects are in nologging mode, which does not necessary to generate redo log for database recovery. And commit write must be immediate and wait, so all the redo log have to be written to disk per commit.

It is DBA’s responsibility to make the Oracle database ACID compliant. And in some cases, break the AICD properties for business or performance reasons. In the late case, DBA need to understand the complications of such actions and make them clear to management and database users.