ACID Properties with SQL Transactions in DBMS

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 .