What are atomicity and consistency?

Atomicity and Consistency are two of the four properties of ACID transactions in databases, ensuring the reliability of operations.

Atomicity guarantees that a transaction is either fully completed or not executed at all. If any part of the transaction fails, the entire transaction is rolled back to the initial state. No partial transactions.

// Example of atomicity in SQL (not Java code, but for illustration)
// Suppose transferring 100 units from one account to another
public void transfer(int fromAccountId, int toAccountId, double amount) {
    Connection connection = null;
    try {
        connection = dataSource.getConnection();
        connection.setAutoCommit(false); // Disable auto-commit for transaction control

        // Debit from one account
        String debitSql = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
        try (PreparedStatement debitStmt = connection.prepareStatement(debitSql)) {
            debitStmt.setDouble(1, amount);
            debitStmt.setInt(2, fromAccountId);
            int updatedRows = debitStmt.executeUpdate();
            if (updatedRows == 0) {
                throw new SQLException("Account not found or insufficient funds: " + fromAccountId);
            }
        }

        // Credit to another account
        String creditSql = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
        try (PreparedStatement creditStmt = connection.prepareStatement(creditSql)) {
            creditStmt.setDouble(1, amount);
            creditStmt.setInt(2, toAccountId);
            int updatedRows = creditStmt.executeUpdate();
             if (updatedRows == 0) {
                throw new SQLException("Account not found: " + toAccountId);
            }
        }

        connection.commit(); // All operations successful, commit transaction
    } catch (SQLException e) {
        if (connection != null) {
            try {
                connection.rollback(); // Rollback on error
            } catch (SQLException rollbackEx) {
                // Log rollback error
            }
        }
        // Handle transaction error
    } finally {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException closeEx) {
                // Log close error
            }
        }
    }
}

Consistency guarantees that a transaction transforms the database from one correct state to another. After a successful or rolled-back transaction, the database remains in a state that satisfies all rules and constraints (e.g., key uniqueness, non-negative account balance). Consistency is maintained through data integrity constraints before and after the atomic transaction.

Consistency is achieved through a combination of database properties (constraints, triggers) and application logic.

// Example of consistency (via application logic and constraints)
// In the above transfer() code:
// 1. Balance check (part of maintaining consistency) is indirectly ensured
//    through the count of updated rows or using a CHECK constraint in the DB.
// 2. If debitStmt.executeUpdate() returns 0, it may mean insufficient balance (if CHECK constraint exists)
//    or account absence, which violates consistency if we continue.
// 3. Rollback on any error guarantees that the database does not remain in an intermediate,
//    inconsistent state (e.g., debited but not credited).

// Example of a CHECK constraint in SQL to maintain balance consistency:
// ALTER TABLE accounts ADD CONSTRAINT check_positive_balance CHECK (balance >= 0);