SQL Server interview questions covering transactions, locking, blocking & deadlocks.
Open a question for the full answer, code and interview guidance.
A transaction is a logical unit of work containing one or more database operations that should be treated as a single consistent operation. If all required statements succeed, the transaction can be committed. If a failure occurs, the transaction can be rolled back. Transactions are essential for operations such as transferring money, creating an order together with its order items, or updating multiple related tables.
ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity means the transaction's work succeeds as a unit or is rolled back. Consistency means committed transactions should move the database from one valid state to another while respecting defined integrity rules. Isolation controls how concurrent transactions interact and what intermediate effects they can observe. Durability means that once a transaction is successfully committed, its committed changes survive subsequent failures according to SQL Server's durability guarantees. ACID properties are fundamental to reliable transactional database systems.
COMMIT makes the changes performed by the current transaction permanent and ends that transaction. Applications should commit only after all required operations have completed successfully. Once committed, the transaction cannot simply be undone with ROLLBACK; reversing it requires another compensating data operation or recovery process.
ROLLBACK reverses changes made by an active transaction back to its beginning or to a specified savepoint, depending on the syntax and transaction state. It is commonly used in error handling so a partially completed business operation does not leave inconsistent data.
With IMPLICIT_TRANSACTIONS enabled, certain statements automatically start a transaction when no transaction is already active. The transaction remains open until the application explicitly issues COMMIT or ROLLBACK. This differs from SQL Server's common autocommit behavior, where each individual statement is automatically committed when it succeeds. Accidentally leaving an implicit transaction open can cause long-running locks and blocking.
@@TRANCOUNT returns the number of BEGIN TRANSACTION statements that have occurred on the current connection minus the number of corresponding COMMIT operations, subject to SQL Server's transaction semantics. It is useful when writing procedures that may execute inside an existing transaction. A ROLLBACK without a savepoint rolls back the entire transaction and resets @@TRANCOUNT to zero, while a COMMIT of a nested transaction mainly decrements the count until the outermost transaction is committed.
A savepoint marks a location within a transaction so part of the transaction can be rolled back without necessarily rolling back all work performed since the transaction began. SQL Server creates a savepoint using SAVE TRANSACTION. Savepoints can be useful in complex procedures, although transaction design should remain as simple and short-lived as possible.
XACT_STATE reports the state of the current user transaction. A value of 1 means an active transaction exists and can be committed. A value of -1 means a transaction exists but is uncommittable and can only be fully rolled back. A value of 0 means there is no active user transaction. XACT_STATE is particularly useful in TRY...CATCH transaction handling.
A lock is a concurrency-control mechanism used by SQL Server to protect data and coordinate simultaneous access by multiple transactions. Depending on the operation and isolation level, SQL Server can acquire locks at different resources such as keys, pages, tables, metadata, and other internal structures. Common lock modes include Shared (S), Exclusive (X), Update (U), Intent locks, and schema locks. Locks are normal and necessary; the performance problem is usually excessive contention or locks being held longer than necessary.
A Shared (S) lock is generally used when reading data under locking-based isolation and can coexist with other compatible shared locks. An Exclusive (X) lock is used when modifying data and conflicts with many other lock modes on the same resource. An Update (U) lock is used during certain read-before-update operations and helps reduce a class of conversion deadlocks by allowing only one update lock on a compatible resource at a time before conversion to an exclusive lock. Actual lock behavior depends on the statement, access path, isolation level, and SQL Server's lock manager.
Blocking occurs when one session must wait because another session holds an incompatible lock on a resource it needs. Some blocking is normal in transactional systems. It becomes problematic when transactions are long-running, queries touch more data than necessary, indexes are poor, users leave transactions open, or application code performs slow work while holding database transactions. Troubleshooting should identify both the blocked session and the head blocker rather than focusing only on the waiting query.
Blocking can often be reduced by keeping transactions short, accessing only required rows, creating appropriate indexes, avoiding unnecessary user interaction inside transactions, committing promptly, and using appropriate isolation strategies. Row-versioning options such as READ_COMMITTED_SNAPSHOT can reduce reader-writer blocking for many workloads, but they change concurrency behavior and increase version-store usage. They should be evaluated deliberately rather than enabled solely as a quick fix. Application retry policies may help with transient concurrency failures, but they do not replace fixing persistent blocking causes.
A deadlock occurs when two or more sessions form a cycle in which each is waiting for a resource held by another session in that cycle, so none can proceed. SQL Server detects deadlocks and selects one transaction as the deadlock victim. That transaction is rolled back and receives error 1205, allowing the other transaction or transactions to continue. A deadlock is different from ordinary blocking: blocking can resolve when the blocking transaction completes, while a deadlock contains a dependency cycle that requires intervention by SQL Server.
Common techniques include accessing tables and rows in a consistent order, keeping transactions short, using appropriate indexes so fewer rows are touched, avoiding unnecessarily high isolation levels, and reducing transaction scope. Applications should also be prepared to retry transactions that fail with deadlock error 1205 when the operation is safe to retry. Extended Events and deadlock graphs should be used to identify the exact resources and statements involved before redesigning the query or transaction.
A transaction isolation level defines the degree to which a transaction is isolated from concurrent changes made by other transactions. SQL Server supports READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, and SERIALIZABLE. READ COMMITTED is the default isolation level, although its implementation can use locks or row versioning depending on database configuration. Higher isolation can prevent more concurrency anomalies but may increase blocking or resource usage. The correct level depends on the application's consistency requirements.
READ UNCOMMITTED is the least restrictive isolation level. It allows a transaction to read data modified by another transaction even when that transaction has not committed. This can produce dirty reads and other inconsistent observations. NOLOCK is commonly associated with READ UNCOMMITTED behavior for table access, but it is not a general performance solution and does not mean 'no locks of any kind.' Use it only when the business can tolerate potentially inconsistent data.
READ COMMITTED is SQL Server's default transaction isolation level. It prevents dirty reads by ensuring a statement does not read uncommitted changes. With the traditional locking implementation, readers generally use shared locks and may wait for incompatible writers. When READ_COMMITTED_SNAPSHOT is enabled for the database, READ COMMITTED can instead use row versions for many reads, reducing reader-writer blocking. Therefore, knowing the database option is important when diagnosing concurrency behavior.
REPEATABLE READ prevents other transactions from modifying rows that the current transaction has read until the transaction completes, preventing non-repeatable reads. However, new rows that match the search condition can still appear in a repeated query. SERIALIZABLE is more restrictive. It also protects key ranges so other transactions cannot insert qualifying rows that would create phantoms for the protected range. These isolation levels can increase blocking and should be used only when the stronger consistency guarantees are required.
SNAPSHOT isolation uses row versioning so a transaction can read a transactionally consistent snapshot of committed data as of the time the transaction began, rather than taking traditional shared locks for those reads. It can reduce reader-writer blocking, but it requires the database option ALLOW_SNAPSHOT_ISOLATION and uses the row-versioning infrastructure. Update conflicts can occur when concurrent transactions modify the same data. SNAPSHOT should be selected based on consistency and concurrency requirements rather than viewed simply as a faster isolation level.
NOLOCK is a table hint that allows READ UNCOMMITTED-style reads for the referenced table. It can read uncommitted data and can produce inconsistent results, including effects from rows changing while the scan is occurring. READ_COMMITTED_SNAPSHOT (RCSI) changes READ COMMITTED behavior at the database level so eligible readers use committed row versions rather than waiting on writers for data locks. This can reduce reader-writer blocking without permitting dirty reads. RCSI is therefore fundamentally different from NOLOCK. It has version-store and concurrency implications and should be tested before enabling, but it is often a more appropriate concurrency strategy than adding NOLOCK throughout application queries.