Database Concurrency and Reliability Overview

Concurrency and reliability have long been “hot topics” of discussion among developers and users of distributed systems. The fundamental problem can be seen in a simple example, as follows.

Suppose two users are working on the same part of a database at the same time. They both UPDATE the same row in the same table, but they provide different values in the UPDATE. The UPDATE commands are sent to the database precisely at the same time. What does the database system do about this, and what are the rules governing its decision?

ACID

When discussing concurrency and reliability, developers often talk about the components of ACID: atomicity, consistency, isolation, and durability. Together, these properties guarantee that a database transaction is processed in a reliable, predictable manner. A transaction, in this case, can be defined as any set of operations that changes the state of the database. It could be something as simple as reading a value, deciding how to manipulate that value based on what was read, and then updating the value.

Atomicity

The atomicity property guarantees that a transaction is either completed in full or not completed at all. Thus, the result of an operation is always success or failure, and no transaction can result in a partial completion. Essentially, by making a transaction “atomic”, all the operations involved in the transaction are virtually combined into one single operation.

Two important rules provide transaction atomicity. First, as operations in a transaction occur, those operations must remain unknown to all other processes accessing the database at the same time. Other processes may see only the final product after the transaction is complete, or they will see no changes at all.

The second rule is somewhat of an extension of the first rule. It says that, if any operations involved in a transaction fail, the entire transaction fails, and the database is restored to the state before the transaction began. This prevents a transaction from being partially completed.

{qbapagebreak title=Database Consistency}

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.