Database Consistency

Consistency is probably the most fundamental of the four ACID components. As such, it is arguably the most important in many cases. In its most basic form, consistency tells us that no part of a transaction is allowed to break the rules of a database.

For example, if a column is constrained to be NOT NULL and an application attempts to add a row with a NULL value in that column, the entire transaction must fail, and no part of the row may be added to the database.

In this example, if consistency were not upheld, the NULL value would initially still not be added as part of the row, but the remaining parts of the row would be added. However, since no value would be specified for the NOT NULL column, it would revert to NULL, anyway, and violate the rules of the database. The subtleties of consistency go far beyond an obvious conflict between NOT NULL columns and NULL value, but this example is a clear illustration of a simple violation of consistency. In Figure 1, we can see that no part of a row is added when we try to violate the NOT NULL constraint.

Isolation

The isolation property ensures that, if a transaction is being executed, no processes other than the one executing the transaction see the transaction in a partially completed state. A simple example of this is as follows. Suppose one customer of a bank transfers money to another customer. This money should appear in one customer’s account and then in the other customer’s account but never in both accounts simultaneously. The money must always be somewhere, and it must never be in two places at the same time.

Formally, isolation requires that the database’s transaction history is serializable. This means that a log of transactions can be replayed and have the same effect on the database as they did originally.

Durability

A database system that maintains durability ensures that a transaction, once completed, will persist. This may sound like a vague definition, but it is really quite simple. If an application executes a database transaction, and the database notifies the application that the transaction is complete, then no future, unintended event will be able to reverse that transaction. A popular method of ensuring durability is to write all transactions to a log, which can be replayed from an appropriate time in the case of system failure. No transaction is considered to be complete until it is properly written to the log.