ACID Nature of SQL Server

We all know that every Relational Database Management System has to be ACID compliant, even SQL Server is one of those RDBMS. The four transaction Properties of ACID are Atomicity, Consistency, Isolation and Durability. Let's see how SQL Server will satisfy each of the property.

Atomicity: In a single transaction involving two or more distinct changes 

(Updates\Inserts\Deletes), either all of the changes are committed or none are. To make it simple let’s say you have a single transaction consists of a delete, update and insert in the same order. And your delete, update are successful but insert is failed (bad) because of some issue in this case SQL Server just roll backs your delete and update as well. 

SQL Server uses a Write Ahead Transaction Log. The log always gets written to first before the associated data changes. That way, if and when things go wrong, SQL Server will know how to roll back to a state where every transaction happened or didn’t happen. 

Example: 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

CREATE DATABASE Test GO --//Create test Table as Atomicity_table with-- --only column Name as primary key//-- USE Test CREATE TABLE Atomicity_table ( Name VARCHAR(50) NOT NULL CONSTRAINT pk_atomicit PRIMARY KEY (name) ) GO --/Inserting two values into the Atomicity_table/-- USE Test INSERT INTO Atomicity_table (Name) VALUES ('SQL') INSERT INTO Atomicity_table (Name) VALUES ('SQL1')

The result of the above two inserts looks like below.

Now Lets introduce a single transaction that do 3 things as below.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

--//Starting a single transaction (used Begin and Commit Tran)-- --with delete\update\insert//-- Use Test GO BEGIN TRAN DELETE FROM Atomicity_table WHERE name='SQL' GO UPDATE Atomicity_table SET Name = 'Oracle' WHERE name='SQL1' GO --//To make the transaction run longer I used WAITFOR DELAY 15 secs,-- -- this is where I will force SL Server to restart to make the Insert fail//-- WAITFOR DELAY '00:00:15' INSERT INTO Atomicity_table (Name) VALUES ('Oracle1') GO COMMIT TRAN

The result of the above Delete\Update\Insert query look like below after forced to fail query execution in the middle.

All the changes are rolled back though delete and Update are successful before insert failed. And the table is unmodified yields same results (SQL and SQL1) as above first screen shot.

Consistency: Most of the tables in SQL Server will bound by set of rules like Primary-Foreign key constraint relation ships, auto increment columns, Column sizes, column data types, columns with default binding values and few columns are set use specified values only and etc., and if the data has to reside on the tables with these rules data has to obey those rules. Let’s say a table has column called Gender which only takes Male or Female, SQL Server won’t allow the transaction to complete successfully if it is trying to insert something else into that Gender column which make the data inconsistent. These kind of transactions will fail and any changes made will be rolled back to bring the data to consistent state as it was before the start of the transaction. And any valid transaction which insert Male or Female will be processed successfully and it will take the data\database from one consistent state with all rules in place to another consistent state with all rules in place and obeying.

Isolation: This will ensure transactions running at same time on same tables will not be 

impacted by each other. No current transaction sees the intermediate results (Dirty Reads\Phantom reads) of the other current transaction. Let’s say we have two transactions both are performing the same function and running at the same time, the isolation will ensure that each transaction separate from other until both are finished. This is where Isolation Levels comes into picture. We all see blockings/lockings while running queries on SQL Server databases which result from Isolation compliance of SQL Server. 

Durability: Once a transaction is successfully executed and committed to the database, that transaction wont be lost. Those changes will be permanent and durable. SQL Server relays on continuous Checkpoints and tracking db changes with LSNs for point in time recovery purposes. The committed transaction can be recovered from any software\hardware \system failures.

Features to consider for durability:

• Recovery to the most recent successful commit point after a database software failure.
• Recovery to the most recent successful commit point after an application software failure.
• Recovery to the most recent successful commit point after a CPU failure.
• Recovery to the most recent successful backup after a disk failure. 

-Thanks