SQL Transactions Interview Questions

SQL Server supports five transaction isolation levels that control how transactions interact with each other. READ UNCOMMITTED allows dirty reads (seeing uncommitted changes from other transactions). READ COMMITTED (the default) only reads committed data. REPEATABLE READ prevents other transactions from modifying rows you have read. SERIALIZABLE is the strictest level, preventing phantom reads by locking entire ranges. SNAPSHOT uses row versioning to give each transaction a consistent view of the data without blocking other transactions.

ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity means all operations in a transaction succeed or all are rolled back, there is no partial completion. Consistency ensures the database moves from one valid state to another, respecting all constraints and rules. Isolation means concurrent transactions do not see each other's intermediate state, controlled by isolation levels like READ COMMITTED or SERIALIZABLE. Durability guarantees that once a transaction is committed, the changes survive even a system crash, ensured by the transaction log. In SQL Server, a transaction is a unit of work wrapped in BEGIN TRANSACTION and ended with COMMIT or ROLLBACK.

BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

    IF @@ERROR <> 0
        ROLLBACK;
    ELSE
        COMMIT;

Without locking, three classic concurrency problems can occur. First, dirty reads, where one transaction reads uncommitted data from another transaction that later gets rolled back, so you end up with data that never officially existed. Second, non-repeatable reads, where you read a row, another transaction modifies it and commits, and when you read it again in the same transaction you get a different value. Third, phantom reads, where a query returns a different set of rows when executed twice in the same transaction because another transaction inserted or deleted rows between the two reads.

Lock escalation is a process where the database engine automatically converts many fine-grained locks, like row-level or page-level locks, into a single coarser lock, typically a table-level lock. This happens to reduce the memory overhead that comes from maintaining a huge number of individual locks. SQL Server, for example, escalates to a table lock when a transaction holds roughly 5,000 locks on a single object. The downside is reduced concurrency since a table lock blocks other transactions from accessing any part of that table, so escalation can cause unexpected blocking and timeouts under heavy load.