SQL Transactions, ACID and Isolation Levels

A transaction is a group of statements that succeed or fail as one unit. Transactions are why a bank transfer never takes money from one account without adding it to the other. Interviewers ask about ACID, the read problems that concurrent users cause, and deadlocks.

ACID

Take a transfer of 500 from account 1 to account 2.

Property	Meaning	In the transfer
Atomicity	All statements succeed, or none do	Either both the debit and the credit happen, or neither
Consistency	A transaction moves the database from one valid state to another	Constraints hold. The total money in the bank is unchanged
Isolation	Concurrent transactions do not see each other's half-finished work	Another user does not see the money "in flight"
Durability	Once committed, the change survives a crash	After `COMMIT`, a power failure cannot undo the transfer

The transaction pattern in SQL Server

SET XACT_ABORT ON;     -- any run-time error rolls the transaction back

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE accounts SET balance = balance - 500 WHERE id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE id = 2;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;             -- report the original error to the caller
END CATCH;

By default SQL Server runs in autocommit mode: each statement is its own transaction. BEGIN TRANSACTION starts an explicit one that lasts until COMMIT or ROLLBACK. SAVE TRANSACTION name creates a savepoint you can roll back to. In standard SQL and PostgreSQL the commands are BEGIN (or START TRANSACTION), COMMIT and ROLLBACK.

What can go wrong when users overlap

Problem	What happens
Dirty read	You read a change that another transaction has not committed yet. If it rolls back, you read data that never officially existed
Non-repeatable read	You read the same row twice in one transaction and get different values, because someone committed an update in between
Phantom read	You run the same range query twice and the second time new rows appear, because someone inserted them in between

Isolation levels

A higher isolation level prevents more problems but allows less concurrency.

Isolation level	Dirty read	Non-repeatable read	Phantom read
Read Uncommitted	Possible	Possible	Possible
Read Committed	Prevented	Possible	Possible
Repeatable Read	Prevented	Prevented	Possible
Serializable	Prevented	Prevented	Prevented

The table follows the SQL standard. Real engines differ in details. In SQL Server the default is Read Committed (the same default as PostgreSQL). SQL Server also offers Snapshot isolation, which uses row versions so readers do not block writers. is the same as Read Uncommitted for that table, so it can return dirty data. Do not recommend it as a performance fix.

Deadlocks

A deadlock happens when two transactions each hold a lock the other needs, so neither can continue:

Transaction A locks row 1 and then wants row 2.
Transaction B locks row 2 and then wants row 1.

SQL Server detects the cycle, picks one transaction as the victim (generally the one that is cheapest to roll back), rolls it back and returns error 1205 to it. The application should catch that error and retry. To reduce deadlocks, access tables and rows in the same order everywhere, keep transactions short, and make sure queries use indexes so they lock fewer rows.

Identity values and rollbacks

Rolling back does not give identity values back. If a transaction inserts a row that received identity 10 and is then rolled back, the next insert gets 11. Gaps in an identity column are normal, so never use one to count rows.

Practise

Open two query windows. Start a transaction in the first, update a row without committing, and try to read that row from the second. Then try in the second.
Insert a row inside a transaction, roll it back, insert again, and look at the identity values.
Take the Indexes, Design and Transactions quiz.