ACID and transaction boundaries

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Treat related writes as one unit while distinguishing ACID properties. Run changing examples only on a disposable training instance. The example uses a session-local object or rolls back its changes.

Run the example

CREATE TABLE #Pair (ID int NOT NULL PRIMARY KEY);
BEGIN TRANSACTION;
INSERT INTO #Pair VALUES (1);
INSERT INTO #Pair VALUES (2);
COMMIT TRANSACTION;
SELECT ID FROM #Pair ORDER BY ID;
DROP TABLE #Pair;

Read it step by step

1. Both inserts share one transaction boundary and are committed together.
2. Atomicity means the unit either commits or rolls back. Consistency means a successful transaction preserves the database rules; the primary key enforces one rule, while business rules may need additional logic.
3. Isolation governs concurrent visibility, and durability concerns committed data on persistent storage; this temp-table demo does not test crash recovery.

Check the result

The two rows are visible after commit within this session-local temp table.

Practice

Insert IDs 3 and 4 into a temp table inside one transaction, commit, and display both.

CREATE TABLE #Pair (ID int NOT NULL PRIMARY KEY);
BEGIN TRANSACTION;
INSERT INTO #Pair VALUES (3);
INSERT INTO #Pair VALUES (4);
COMMIT TRANSACTION;
SELECT ID FROM #Pair ORDER BY ID;
DROP TABLE #Pair;