What is Consistency?

Consistency, one of the ACID properties, ensures that a database adheres to all predefined rules and constraints at all times. A transaction must transition the database from one valid state to another while maintaining all data integrity constraints.

Key Points:

• A transaction cannot violate database rules, such as unique keys, foreign key constraints, or business rules.
• If a transaction fails, the database state must remain unchanged, preserving its integrity.

The database always follows rules, ensuring valid data before and after transactions.

Consistency Explained Through the Example

Let’s analyze the scenario in the provided image:

1. Initial State (T1):

   • Person A’s Balance: $300
   • Person B’s Balance: $400
   • Total Balance: 300 + 400 = $700

   This represents a consistent state where the total balance across accounts is correct.

2. Transaction Begins (T2):

   • A transaction is initiated to transfer $100 from Person A to Person B.

3. Step 1: Debit Operation (T3):

   • $100 is debited from Person A’s account.
   • New Balance:
     • Person A: 300 - 100 = $200
     • Person B: $400 (unchanged so far)
   • Total Balance: 200 + 400 = $600 (Inconsistent state due to intermediate execution).

4. Step 2: Credit Operation (T4):

   • $100 is credited to Person B’s account.
   • New Balance:
     • Person A: $200
     • Person B: 400 + 100 = $500
   • Total Balance: 200 + 500 = $700 (Back to a consistent state).

5. Commit Operation (T5):

   • The transaction is successfully committed, and changes become permanent.

6. Final State (T6):

   • Person A’s Balance: $200
   • Person B’s Balance: $500
   • Total Balance: 200 + 500 = $700

   The database maintains a consistent state after the transaction.

How Consistency is Achieved

1. Validation Rules:

   • Enforce constraints such as unique keys, foreign keys, and check constraints.
   • Example: Ensure that account balances never go negative during the transaction.

2. Pre-Transaction Checks:

   • Validate that all conditions required for a successful transaction are met before execution.
   • Example: Check Person A’s account has sufficient funds before debiting.

3. Post-Transaction Verification:

   • Ensure the database satisfies all integrity constraints after the transaction.
   • Example: Verify that the total balance across all accounts is preserved.

4. Atomic Execution:

   • Consistency relies on atomicity to prevent partial updates. If any step in the transaction fails, the database reverts to its previous state.

Scenarios Where Consistency Can Be Violated

1. Constraint Violation:

   • Example: Adding a record with a duplicate primary key.
   • The database will reject the operation and maintain consistency.

2. System Crashes During Execution:

   • If the system crashes after debiting but before crediting, total balance integrity is lost.

3. Logical Errors in Business Rules:

   • Example: Transferring an amount larger than the sender’s balance due to a programming bug.

4. Concurrent Transactions Without Isolation:

   • Example: Two transactions simultaneously debiting from the same account without proper locking.