ACID Properties: Basic Example of Atomicity and Consistency

The ACID properties—Atomicity, Consistency, Isolation, and Durability—are fundamental principles that ensure reliable transactions in a database. To know more about ACID Properties, This article focuses on two key properties: Atomicity and Consistency, with practical examples using MySQL.

ACID Properties in MySQL

A transaction’s atomicity guarantees that several database operations are handled as a single unit. The complete transaction is rolled back in the event that any operation fails, guaranteeing that no partial updates are executed.

Example of Atomicity & Consistency

Let’s consider a simple banking scenario where money is transferred between two accounts.

Setup Database and Tables

CREATE DATABASE bank;
USE bank;

CREATE TABLE accounts (
account_id INT PRIMARY KEY,
account_holder VARCHAR(100),
balance DECIMAL(10, 2)
);

INSERT INTO accounts (account_id, account_holder, balance) VALUES
(1, 'Alice', 1000.00),
(2, 'Bob', 500.00);

Atomicity: Ensuring All-or-Nothing Transactions & Consistency: Maintaining Database Rules

Atomicity ensures that either all operations of a transaction are completed or none are.

CREATE DEFINER=`root`@`localhost` PROCEDURE `Atomicity`()
BEGIN
-- Start transaction
START TRANSACTION;

-- Debit Alice's account
UPDATE accounts SET balance = balance - 500.00 WHERE account_id = 1;

-- Check if the balance update was successful
SET @alice_balance = (SELECT balance FROM accounts WHERE account_id = 1);

-- If Alice's balance update was successful, proceed to credit Bob's account
IF @alice_balance IS NOT NULL AND @alice_balance >= 0 THEN
    -- Credit Bob's account
    UPDATE accounts SET balance = balance + 200.00 WHERE account_id = 2;

    -- Check if Bob's balance update was successful
    SET @bob_balance = (SELECT balance FROM accounts WHERE account_id = 2);

    -- If both operations are successful, commit the transaction
    IF @bob_balance IS NOT NULL THEN
        COMMIT;
    ELSE
        -- If Bob's balance update fails, rollback the transaction
        ROLLBACK;
    END IF;
ELSE
    -- If Alice's balance update fails, rollback the transaction
    ROLLBACK;
END IF;

-- Select the final balances to confirm the transaction results
SELECT * FROM accounts;

END

If the debit operation on Alice’s account succeeds but the credit operation on Bob’s account fails, the ROLLBACK command ensures that Alice’s account is not debited.

Now run this SP once again

Consistency ensures that the database adheres to defined rules, such as constraints.

Consistency ensures that a transaction brings the database from one valid state to another, maintaining database invariants.

Add a constraint to ensure account balances cannot be negative:

ALTER TABLE accounts ADD CONSTRAINT chk_balance CHECK (balance >= 0);

Conclusion

Understanding and implementing the ACID properties of Atomicity and Consistency in MySQL transactions is crucial for maintaining data integrity and reliability. By using COMMIT and ROLLBACK commands effectively, you can ensure that your database operations are robust and consistent, even in the face of failures.