SQL Server ACID Properties
Table of Contents
What is ACID Properties in SQL?
These Properties are important aspects of whole Database Management System.
Many application uses MS SQL server for their databases. Number of complex queries runs on the database, many a times multiple queries were running on the databases. But we never heard that, SQL server has any type of issue in the data due to this.
You know how SQL server is achieving this?
SQL Server ensures the transaction reliability by enforcing Atomicity, Consistency, Isolation, and Durability through the storage engines Transaction Manager.
These Properties of SQL server are called as ACID property.
Atomicity
Whenever we execute any transaction, it will execute fully or fails completely. If the transaction fails partially, SQL server triggers the ROLLBACK.
- There is no partial execution
- This property prevents partial data updates
- Maintains data integrity
- SQL servers uses below commands to achieve this
BEGIN TRANSACTION,
COMMIT,
ROLLBACK
Technically SQL server uses Transaction Logs and Write-Ahead logging mechanisms to achieve Atomicity.
Example:-
Bank transfer from Account A to Account B:
- Step 1: Debit 100 from A (UPDATE AccountA SET Balance = Balance – 100).
- Step 2: Credit 100 to B (UPDATE AccountB SET Balance = Balance + 100).
With Atomicity, you never end up with A debited but B not credited.
Either both updates succeed, or both are undone.
Consistency
This property ensures that a transaction brings the database from one valid state to another. It preserves all defined rules like constraints, triggers, and relationships.
After a transaction
- All rules, constraints, and relationships must be satisfied
- Invalid data is not allowed
SQL server enforces consistency using following-
1. Constraints
Primary Key – no duplicates, no Nulls
Foreign key – valid relationship
UNIQE- No duplicate values
CHECK – validate conditions
Not Null – prevent missing value
2. Triggers
Automatically enforce business rule
3. Data Types
Ensures correct format
4. Indexes and relationships
Maintain logical integrity between tables
Atomicity handles completions, where consistency handles correctness.
Isolation
This property of SQL server controls how transactions interact with each other when running concurrently
It ensures that one transaction does not interfere with another transaction’s data
This prevents dirty reads, non-repeatable reads and phantom reads Default Isolation level in SQL server is READ COMMITTED.
Durability
This property ensures that once the transaction commits, its changes are permanently saved. These changes survive system failures like power outages, crashes, or restarts.
When you execute the transactions in SQL server, data is written to transaction logs.
When you Commit, SQL server forces transaction log records to disk.
Once the data flushes to the disk, it becomes durable
Please refer below Microsoft article for more information
