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

ACID Properties

Check all related articles