Consistency

1. Introduction

Consistency within the ACID properties focuses on ensuring that the data in the database moves from one valid state to another valid state. This ensures that the data that has been modified in the database is not corrupted and is correct at the end of the transaction. With the consistency property, the database should not be in a partially completed state.

2. Criteria for Consistency

The consistency property will follow the following criteria:

1. If the transaction has completed successfully, the changes will be applied to the database.
2. If there was an error in the transaction, all of the changes should be reverted/rolled back automatically. This means that the database should restore the state before the transaction has begun.
3. If there was a system failure or external issue while the transaction was executing, all of the changes that were made in the transaction up to that point will automatically be reverted/rolled back.

3. Consistency Example

Given our example, Jennifer would like to make a payment to Randall for $100 through an account transfer. This transaction would be a balance transfer between two accounts at two different branches at the same bank. Let us review what the transaction would look like:

1. Jennifer’s (10) account would be deducted by $100.
2. The banking location that Jennifer has her account would have their location’s account deducted by $100.
3. The banking location that Randall(50) has his account would be increased by $100.
4. Randall’s account would be increased by $100.

The consistency property ensures that the total value between the balance is the same at the start and the end of the transaction. This means that the customer_accounts and branch_accounts would have a consistent total to account for each statement. Let us look back at the transaction in SQL:

BEGIN;

UPDATE customer_account 
SET balance = balance – 100 
WHERE account_id = 10;

UPDATE branch_account
SET balance = balance – 100 
WHERE branch_id = (SELECT branch_id FROM customer_account where account_id = 10);

UPDATE branch_account 
SET balance = balance + 100 
WHERE branch_id = (SELECT branch_id FROM customer_account where account_id = 50);

UPDATE customer_account 
SET balance = balance +100 
WHERE account_id = 50;

COMMIT;

Let us say that during the second UPDATE statement, the system had a failure. When the system recovers, the transaction would have only partially executed. As such, there is an inconsistent state as the total balances do not match up. In this situation, the system would roll back those UPDATE statements to the consistent state prior to the transaction starting. Otherwise, we would have a problem with the data being inconsistent. Unlike the atomicity, the issue was not caused by an error in the database statement.

If Jennifer’s balance started at $1000 and Randall’s balance started at $1000. The end result should have it such that their balances have the appropriate balances being what was expected. With the results, it is the assumption that Jennifer’s account balance should be set at $900 and Randall’s balance should be at $1100. If for any reason, the values were not what was expected, the transaction would also be rolled back.