ACID Properties in SQL Server

A - ATOMICITY   : all statements in a transaction succeed, or none do.
C - CONSISTENCY : data always moves from one VALID state to another
                  (constraints, foreign keys, rules are never broken).
I - ISOLATION   : concurrent transactions do not see each other's
                  half-finished work (see file 21).
D - DURABILITY  : once COMMIT returns, data survives a crash / restart
                  (written to the transaction log first = write-ahead logging).

Story used in this file : an e-commerce warehouse
  Inventory  -> stock per product
  SalesOrders / SalesOrderLines -> customer orders

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Topic tables

DROP TABLE IF EXISTS dbo.SalesOrderLines;
DROP TABLE IF EXISTS dbo.SalesOrders;
DROP TABLE IF EXISTS dbo.Inventory;

CREATE TABLE dbo.Inventory
(
    ProductID    INT PRIMARY KEY REFERENCES dbo.Products(ProductID),
    Warehouse    VARCHAR(20) NOT NULL,
    QtyOnHand    INT         NOT NULL CONSTRAINT CK_Inventory_NonNegative CHECK (QtyOnHand >= 0),
    QtyReserved  INT         NOT NULL DEFAULT 0,
    CONSTRAINT CK_Inventory_Reserved CHECK (QtyReserved <= QtyOnHand)
);

INSERT INTO dbo.Inventory (ProductID, Warehouse, QtyOnHand, QtyReserved) VALUES
(1, 'Hyderabad-WH1', 10, 0),
(3, 'Hyderabad-WH1',  2, 0),
(7, 'Mumbai-WH2',     5, 1),
(9, 'Mumbai-WH2',   100, 0),
(13,'Chennai-WH3',   20, 0);

CREATE TABLE dbo.SalesOrders
(
    SalesOrderID INT IDENTITY(1000,1) PRIMARY KEY,
    CustomerID   INT          NOT NULL REFERENCES dbo.Customers(CustomerID),
    OrderTotal   DECIMAL(12,2) NOT NULL DEFAULT 0 CHECK (OrderTotal >= 0),
    CreatedAt    DATETIME2(0) NOT NULL DEFAULT SYSDATETIME()
);

CREATE TABLE dbo.SalesOrderLines
(
    SalesOrderID INT NOT NULL REFERENCES dbo.SalesOrders(SalesOrderID),
    ProductID    INT NOT NULL REFERENCES dbo.Inventory(ProductID),
    Qty          INT NOT NULL CHECK (Qty > 0),
    Price        DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (SalesOrderID, ProductID)
);
GO

-- Procedure that places an order : reserve stock + create order + lines
CREATE OR ALTER PROCEDURE dbo.usp_CreateSalesOrder
    @CustomerID INT,
    @ProductID1 INT, @Qty1 INT,
    @ProductID2 INT = NULL, @Qty2 INT = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    DECLARE @OrderID INT;

    BEGIN TRY
        BEGIN TRAN;
            INSERT INTO dbo.SalesOrders (CustomerID) VALUES (@CustomerID);
            SET @OrderID = SCOPE_IDENTITY();

            INSERT INTO dbo.SalesOrderLines (SalesOrderID, ProductID, Qty, Price)
            SELECT @OrderID, v.ProductID, v.Qty, p.UnitPrice
            FROM (VALUES (@ProductID1, @Qty1), (@ProductID2, @Qty2)) AS v(ProductID, Qty)
            JOIN dbo.Products p ON p.ProductID = v.ProductID
            WHERE v.ProductID IS NOT NULL;

            UPDATE i SET i.QtyReserved = i.QtyReserved + l.Qty     -- CHECK constraint protects us
            FROM dbo.Inventory i
            JOIN dbo.SalesOrderLines l ON l.ProductID = i.ProductID AND l.SalesOrderID = @OrderID;

            UPDATE dbo.SalesOrders
            SET OrderTotal = (SELECT SUM(Qty * Price) FROM dbo.SalesOrderLines WHERE SalesOrderID = @OrderID)
            WHERE SalesOrderID = @OrderID;
        COMMIT TRAN;
        SELECT 'Order created' AS Result, @OrderID AS SalesOrderID;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;
        SELECT 'Order FAILED - nothing saved' AS Result, ERROR_MESSAGE() AS Reason;
    END CATCH;
END;
GO

(5 rows affected)

A - ATOMICITY : all or nothing

Successful order: order + lines + stock reservation saved together

EXEC dbo.usp_CreateSalesOrder @CustomerID = 1, @ProductID1 = 1, @Qty1 = 2, @ProductID2 = 9, @Qty2 = 10;

Failing order: product 3 has only 2 in stock, we ask for 5.

The order header and first line were inserted, but EVERYTHING is rolled back.

EXEC dbo.usp_CreateSalesOrder @CustomerID = 2, @ProductID1 = 13, @Qty1 = 1, @ProductID2 = 3, @Qty2 = 5;

SELECT * FROM dbo.SalesOrders;           -- only the first order exists
SELECT * FROM dbo.SalesOrderLines;       -- no lines of the failed order
SELECT * FROM dbo.Inventory;             -- product 13 reservation was NOT changed
GO

C - CONSISTENCY : the database refuses invalid states

CHECK constraint: negative stock is impossible

BEGIN TRY
    UPDATE dbo.Inventory SET QtyOnHand = QtyOnHand - 50 WHERE ProductID = 7;   -- only 5 on hand
END TRY
BEGIN CATCH
    SELECT 'CHECK constraint' AS RuleChecked, ERROR_MESSAGE() AS Msg;
END CATCH;

(0 rows affected)

Foreign Key: order line for a product that is not in inventory

BEGIN TRY
    INSERT INTO dbo.SalesOrderLines (SalesOrderID, ProductID, Qty, Price) VALUES (1000, 20, 1, 100);
END TRY
BEGIN CATCH
    SELECT 'FOREIGN KEY' AS RuleChecked, ERROR_MESSAGE() AS Msg;
END CATCH;

(0 rows affected)

Primary Key: same product twice in the same order

BEGIN TRY
    INSERT INTO dbo.SalesOrderLines (SalesOrderID, ProductID, Qty, Price) VALUES (1000, 1, 1, 100);
END TRY
BEGIN CATCH
    SELECT 'PRIMARY KEY' AS RuleChecked, ERROR_MESSAGE() AS Msg;
END CATCH;

(0 rows affected)

Business rule check: order total must equal the sum of its lines

SELECT o.SalesOrderID, o.OrderTotal,
       (SELECT SUM(Qty * Price) FROM dbo.SalesOrderLines l WHERE l.SalesOrderID = o.SalesOrderID) AS LinesTotal,
       CASE WHEN o.OrderTotal = (SELECT SUM(Qty * Price) FROM dbo.SalesOrderLines l WHERE l.SalesOrderID = o.SalesOrderID)
            THEN 'CONSISTENT' ELSE 'BROKEN' END AS CheckResult
FROM dbo.SalesOrders o;
GO

Run these steps in two query windows in SSMS (Window 1 and Window 2), one step at a time, to see how the sessions affect each other.

I - ISOLATION : other sessions don't see uncommitted work
---------------------------------------------------------------------
-- WINDOW 1
BEGIN TRAN;
    UPDATE dbo.Inventory SET QtyOnHand = 0 WHERE ProductID = 13;

-- WINDOW 2 : waits (blocked) because the change is not committed
SELECT * FROM dbo.Inventory WHERE ProductID = 13;

-- WINDOW 1
ROLLBACK;   -- window 2 now returns QtyOnHand = 20 (never saw 0)
Full details: Transcations/21_Isolation_Levels.sql

-- Single-window proof : our own uncommitted change IS visible to us, and disappears after rollback
BEGIN TRAN;
    UPDATE dbo.Inventory SET QtyOnHand = 0 WHERE ProductID = 13;
    SELECT 'Inside my transaction' AS View_, QtyOnHand FROM dbo.Inventory WHERE ProductID = 13;
ROLLBACK;
SELECT 'After rollback' AS View_, QtyOnHand FROM dbo.Inventory WHERE ProductID = 13;
GO

(1 row affected)

D - DURABILITY : committed data survives a restart
---------------------------------------------------------------------
Every change is written to the TRANSACTION LOG (.ldf) BEFORE COMMIT returns.
After a crash, SQL Server "replays" committed work and undoes uncommitted work
(crash recovery).

Try it (on your OWN practice machine only):

• Run: INSERT INTO dbo.SalesOrders (CustomerID, OrderTotal) VALUES (3, 999);
• Restart the SQL Server service (SQL Server Configuration Manager -> Restart).
• Run: SELECT * FROM dbo.SalesOrders; -- the row is still there.

Look at the log file and recovery model of this database

SELECT name, type_desc, physical_name, size * 8 / 1024 AS SizeMB
FROM sys.database_files;

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases WHERE name = 'SqlLearningDb';

See log records written by a transaction (undocumented function, for learning only)

BEGIN TRAN DurabilityDemo;
    UPDATE dbo.Inventory SET QtyOnHand = QtyOnHand + 1 WHERE ProductID = 13;
COMMIT TRAN DurabilityDemo;

SELECT TOP (20) [Current LSN], Operation, Context, [Transaction Name], AllocUnitName
FROM fn_dblog(NULL, NULL)
WHERE [Transaction ID] IN (SELECT [Transaction ID] FROM fn_dblog(NULL, NULL) WHERE [Transaction Name] = 'DurabilityDemo')
ORDER BY [Current LSN];

UPDATE dbo.Inventory SET QtyOnHand = QtyOnHand - 1 WHERE ProductID = 13;   -- undo demo change
GO

(1 row affected)

(1 row affected)

Practice Questions

1. Add a CHECK constraint so OrderTotal can never exceed 10,00,000 and test it.
2. Write a procedure that SHIPS an order: reduce QtyOnHand and QtyReserved together atomically.
3. Break atomicity on purpose (remove the transaction) and show the inconsistent result.
4. Explain in your own words which ACID property each of these protects: