People repeat ACID as if four letters settle every concurrency argument. The ACID properties shown with real transactions become much easier to discuss once each letter has an observable SQL Server behavior.
Test the ACID Properties in a Disposable Database
Run these scripts in a disposable user database, not a production application. The temporary-table examples disappear with the session. The durability example uses a permanent table because a temporary table cannot demonstrate persistence across connections. Read each result before moving to the next property. I have found that a ten-line experiment resolves more arguments than another page of definitions.
ACID is a set of guarantees with important settings and boundaries. A transaction in one database does not make an external email or HTTP call roll back. Isolation level changes which concurrent effects are visible. Durability also depends on settings such as delayed durability. Which boundary are you calling a transaction today: the database commit, or an entire business workflow? Keep that boundary explicit.
Atomicity, the First of the ACID Properties, Means All or Nothing
Atomicity says a transaction’s database changes commit together or roll back together. In this script, the insert is visible inside the transaction, then ROLLBACK removes it. The final SELECT returns no row. Use one session so the temporary table remains available. The result is simple, but it is the foundation for transfer and order workflows that must not leave only half a database change.
I look for a matching rollback path in every multi-statement change script I review. An error handler that catches an exception and quietly continues can defeat the intended all-or-nothing behavior. The transaction needs an explicit COMMIT on success and a ROLLBACK on failure. A comment saying atomic is not a substitute for a tested failure path.
CREATE TABLE #AtomicDemo (entry_id int NOT NULL PRIMARY KEY);
BEGIN TRANSACTION;
INSERT INTO #AtomicDemo (entry_id) VALUES (1);
SELECT entry_id FROM #AtomicDemo;
ROLLBACK TRANSACTION;
SELECT entry_id FROM #AtomicDemo;Consistency Is Enforced by Rules
Consistency means a transaction takes the database from one state that satisfies its declared rules to another such state. SQL Server can enforce some of those rules with CHECK, FOREIGN KEY, UNIQUE and NOT NULL constraints. The negative amount below violates a CHECK constraint. The failed insert cannot create an invalid row, and the final query shows the table still empty. Business rules outside the schema need application or database logic with equal care.
I resist the claim that ACID automatically makes every business record correct. If a rule is never declared or implemented, the engine cannot invent it. A table can faithfully store a logically wrong value. This property depends on the constraints and transactional code that define valid state, not on an optimistic reading of an acronym.
CREATE TABLE #ConsistencyDemo
(
amount decimal(12,2) NOT NULL
CHECK (amount >= 0)
);
BEGIN TRY
INSERT INTO #ConsistencyDemo (amount) VALUES (-10.00);
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS error_number,
ERROR_MESSAGE() AS error_message;
END CATCH;
SELECT amount FROM #ConsistencyDemo;
Isolation Needs Two Sessions
Isolation governs what concurrent transactions can observe and how they interfere. Open two query windows in the same test database. In window one, create a permanent test row and begin an update without committing it. In window two, a READ COMMITTED SELECT targeting that row will generally wait for the write lock under locking read committed. Under READ_COMMITTED_SNAPSHOT, it can instead read the last committed version. Both behaviors preserve the promise that dirty, uncommitted data is not returned by READ COMMITTED.
I never explain isolation from a single window alone because a transaction cannot race itself. The example is a prompt to observe locking or versioning on your instance. Do not leave the first window open after the exercise; roll back and remove the test table. The important question is which committed version each statement can see.
CREATE TABLE dbo.AcidIsolationDemo
(
entry_id int NOT NULL PRIMARY KEY,
amount int NOT NULL
);
INSERT INTO dbo.AcidIsolationDemo (entry_id, amount)
VALUES (1, 100);
BEGIN TRANSACTION;
UPDATE dbo.AcidIsolationDemo
SET amount = 200
WHERE entry_id = 1;
-- Run the next SELECT in a second query window.
-- ROLLBACK this transaction after the observation.Read From the Second Window
Run this block in another query window while the first transaction remains open. A value of 100 under row versioning or a wait under locking read committed is expected; a value of 200 would be a dirty read and is outside READ COMMITTED behavior. The exact wait depends on database options and other concurrent work. Afterward, roll back the first window and drop the disposable table. The small pause is a teaching tool, not a performance benchmark.
I check the database’s READ_COMMITTED_SNAPSHOT option when the observed behavior differs from somebody’s mental model. That setting changes the read implementation without changing the name of the isolation level. If the team’s design needs repeatable reads or serializable range protection, test those properties separately rather than assuming READ COMMITTED covers them.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT amount
FROM dbo.AcidIsolationDemo
WHERE entry_id = 1;Durability Starts at Commit
Durability means a committed database transaction survives a failure within the engine’s documented recovery model. Create the small permanent table in a disposable database, commit an insert, disconnect, reconnect and query it. A row seen in the same session before COMMIT is not the test. The reconnect makes the transaction boundary clear, although a true crash-recovery test belongs in controlled infrastructure, not in a casual tutorial.
I distinguish this guarantee from backup and disaster recovery. A committed row can survive an ordinary restart while a lost storage volume still requires a recoverable backup. Delayed durability settings can also change when log records are hardened, so inspect them before promising a particular failure behavior. Durability is a storage and recovery promise tied to configuration, not magic against every possible loss.
CREATE TABLE dbo.AcidDurabilityDemo
(
entry_id int NOT NULL PRIMARY KEY,
note nvarchar(100) NOT NULL
);
BEGIN TRANSACTION;
INSERT INTO dbo.AcidDurabilityDemo (entry_id, note)
VALUES (1, N'Committed example');
COMMIT TRANSACTION;Disconnect and reconnect to the disposable database before running this second block. Confirm the committed row, then remove the test table.
SELECT entry_id, note
FROM dbo.AcidDurabilityDemo;
DROP TABLE dbo.AcidDurabilityDemo;Use the ACID Properties to Ask Better Questions
The four ACID properties, tested here, lead to a better review checklist. Can all related database writes roll back together? Which constraints define valid state? Which isolation level and row-versioning options are active? When does the commit become durable on this system? Those questions expose design choices that the four letters alone conceal.
I also ask what sits outside the database: a message queue, an API call, a payment gateway or a file. Those effects need retries, idempotency and reconciliation because a SQL rollback does not undo an email. ACID gives a reliable core for database work, and the surrounding workflow must be designed with equal care. A transaction diagram with no failure arrows is usually a very optimistic diagram.
Related reading on this blog: SQL Server Deadlock: Build One With Your Own Hands and Finding Open Transactions for Session: @@TRANCOUNT.
ACID is not a slogan for safe data, it is four testable transaction behaviors with defined boundaries.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
