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.