This content originally appeared on DEV Community and was authored by Sugesh Appu
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 .
This content originally appeared on DEV Community and was authored by Sugesh Appu
Sugesh Appu | Sciencx (2025-10-03T14:54:02+00:00) ACID Properties with SQL Transactions in DBMS. Retrieved from https://www.scien.cx/2025/10/03/acid-properties-with-sql-transactions-in-dbms/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.