The ACID properties are a set of principles that ensure reliable processing of database transactions in SQL. A transaction is a sequence of one or more SQL operations (such as INSERT, UPDATE, DELETE) executed as a single logical unit of work.
Create the Accounts Table and Insert the Data
CREATE TABLE Accounts (
AccountID INT PRIMARY KEY,
AccountHolder VARCHAR(100) NOT NULL,
Balance DECIMAL(10,2) NOT NULL CHECK (Balance >= 0)
);
INSERT INTO Accounts (AccountID, AccountHolder, Balance) VALUES
(1, 'Alice', 1000.00),
(2, 'Bob', 500.00),
(3, 'Charlie', 2000.00);
Atomicity:
Atomicity means a transaction is an all-or-nothing operation — either all SQL statements succeed, or none take effect.
UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 101;
ROLLBACK;
Consistency:
Consistency ensures that a transaction brings the database from one valid state to another, maintaining all rules, constraints, and data integrity.
ALTER TABLE Accounts
ADD CONSTRAINT chk_balance CHECK (balance >= 0);
INSERT INTO Accounts VALUES (104, 'David', -500);
Isolation:
Isolation means each transaction executes as if it were the only one running, preventing interference from concurrent transactions.
SESSION 1 :
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 2000 WHERE acc_no = 103;
SESSION 2:
SELECT * FROM Accounts WHERE acc_no = 103;
Duriability:
Durability means once a transaction is committed, its changes are permanent and survive system failures.
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 102;
COMMIT;
Conclusion:
Atomicity → Ensures transactions are all-or-nothing, preventing partial updates.
Consistency → Guarantees the database remains in a valid state by enforcing rules and constraints.
Isolation → Keeps transactions independent, avoiding conflicts during concurrent execution.
Durability → Makes committed changes permanent, even in case of crashes or failures.
Thanks for @santhoshnc sir guiding me .
Top comments (0)