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.
The consistency property will follow the following criteria:
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:
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.
Source: Authored by Vincent Tran