Can a database support "Atomicity" but not "Consistency" or vice-versa?

Question

I am reading about ACID properties of a database. Atomicity and Consistency seem to be very closely related. I am wondering if there are any scenarios where we need to just support Atomicity but not Consistency or vice-versa. An example would really help!

Answer 1

They are somewhat related but there's a subtle difference.

Atomicity means that your transaction either happens or doesn't happen.

Consistency means that things like referential integrity are enforced.

Let's say you start a transaction to add two rows (a credit and debit which forms a single bank transaction). The atomicity of this has nothing to do with the consistency of the database. All it means it that either both rows or neither row will be added.

On the consistency front, let's say you have a foreign key constraint from orders to products. If you try to add an order that refers to a non-existent product, that's when consistency kicks in to prevent you from doing it.

Both are about maintaining the database in a workable state, hence their similarity. The former example will ensure the bank doesn't lose money (or steal it from you), the latter will ensure your application doesn't get surprised by orders for products you know nothing about.

Answer 2

Atomicity:

In an atomic transaction, a series of database operations either all occur, or nothing occurs. A guarantee of atomicity prevents updates to the database occurring only partially, which can cause greater problems than rejecting the whole series outright.

Consistency:

In database systems, a consistent transaction is one that does not violate any integrity constraints during its execution. If a transaction leaves the database in an illegal state, it is aborted and an error is reported

A database that supports atomicity but not consistency would allow transactions that leave the database in an inconsistent state (that is, violate referential or other integrity checks), provided the transaction completes successfully. For instance, you could add a string to an int column provided that the transaction performing this completed successfully.

Conversely, a database that supports consistency but not atomicity would allow partial transactions to complete, so long as the effects of that transaction didn't break any integrity checks (e.g. foreign keys must match an existing identity). For instance, you could try adding a new row that included string and int values, and even if the insertion failed half way through losing half the data, the row would be allowed provided that none of the lost data was for required columns and no data was inserted into an incorrectly typed column.

Having said that, consistency relies on atomicity for the reversal of inconsistent transactions.

Answer 3

I have a different understanding of consistency in the ACID context:

Within a transaction, if a given item of data is retrieved and retrieved again later in the same transaction, no changes are seen. That is, the transaction is given a consistent state of the database throughout the transaction. The only updates that can change data visible to the transaction are updates done by the transaction itself.

In my mind, this is tantamount to serializability.