SQL Server Interview

Advanced Transactions & Concurrency

SQL Server interview questions covering advanced transactions & concurrency.

20 Questions Filter by level

Interview questions

Open a question for the full answer, code and interview guidance.

20 Results
241
SeniorAdvanced

What is pessimistic concurrency control?

Pessimistic concurrency assumes conflicts are sufficiently likely that access should be protected while work is in progress. SQL Server commonly implements this through locking under locking-based isolation levels. For example, a transaction may hold locks that prevent another transaction from modifying the same rows until the first transaction commits or rolls back. This can provide strong consistency but may increase blocking. Pessimistic concurrency is appropriate when conflicts are common or when business rules require exclusive access during a transaction.

242
SeniorAdvanced

What is optimistic concurrency control?

Optimistic concurrency assumes conflicts are relatively uncommon. Instead of holding restrictive locks for the entire business operation, the application reads data and verifies at update time that the row has not changed since it was read. In SQL Server applications, a rowversion column is commonly used as a concurrency token. The UPDATE includes both the key and the original rowversion. If zero rows are affected, another transaction has changed or deleted the row. Optimistic concurrency reduces long-held locks but requires the application to detect and resolve conflicts correctly.

243
SeniorAdvanced

What is the rowversion data type?

rowversion is an automatically generated binary value that changes when a row containing a rowversion column is inserted or updated. It is unique within the database at the time it is generated. Despite its historical synonym timestamp, rowversion does not store a date or time. It is commonly used for optimistic concurrency detection. Applications should treat it as an opaque concurrency token rather than deriving business meaning from its numeric value.

244
SeniorAdvanced

How do you implement optimistic concurrency with rowversion?

First read the row together with its rowversion value. When updating, include the original rowversion in the WHERE predicate. If another transaction changed the row after it was read, the stored rowversion will be different and the UPDATE affects zero rows. The application can then reload the current data, report a conflict, merge changes, or retry according to business rules. This pattern prevents silently overwriting another user's changes.

245
SeniorAdvanced

What is a lost update?

A lost update occurs when two transactions read the same data, both make changes, and one transaction's later write unintentionally overwrites the other's committed change. Optimistic concurrency tokens, suitable locking strategies, atomic UPDATE statements, or stronger transaction semantics can prevent lost updates. For web applications, blindly updating a row using only its primary key after presenting an edit form can create this problem when multiple users edit the same record concurrently.

246
SeniorAdvanced

What is a dirty read?

A dirty read occurs when one transaction reads changes made by another transaction that has not yet committed. If the modifying transaction later rolls back, the reader has observed data that never became committed database state. READ UNCOMMITTED and NOLOCK can permit dirty reads. Dirty reads are unacceptable for many financial, inventory, workflow, and transactional operations because decisions may be made from data that subsequently disappears.

247
SeniorAdvanced

What is a non-repeatable read?

A non-repeatable read occurs when a transaction reads a row, another transaction commits a modification to that row, and the first transaction reads it again and receives different values. READ COMMITTED can allow this because shared locks used by an individual statement may be released before the transaction ends. REPEATABLE READ prevents other transactions from modifying rows already read until the transaction completes. Whether this anomaly matters depends on the business operation.

248
SeniorAdvanced

What is a phantom read?

A phantom read occurs when a transaction executes a predicate query, another transaction inserts or deletes rows that satisfy that predicate, and a repeated query returns a different set of rows. REPEATABLE READ protects rows already read but does not generally protect the entire qualifying key range from new matching rows. SERIALIZABLE uses key-range protection to prevent this type of phantom within the protected transaction. SNAPSHOT isolation provides a different consistency model using row versions.

249
SeniorAdvanced

What is lock escalation?

Lock escalation is SQL Server's process of converting many fine-grained locks on a partition or table into a larger-grained lock, typically to reduce lock-manager memory and overhead. Escalation can increase blocking because a table-level lock covers more data than individual row or key locks. Large transactions, scans, and poor indexing can contribute to escalation pressure. The preferred response is usually to reduce transaction size or improve access patterns rather than disabling lock escalation globally.

250
SeniorAdvanced

What are intent locks?

Intent locks indicate that SQL Server holds or intends to acquire more granular locks lower in the locking hierarchy. Examples include Intent Shared (IS), Intent Exclusive (IX), and Shared with Intent Exclusive (SIX). They allow SQL Server to determine lock compatibility efficiently without examining every row or page lock when another session requests a higher-level lock. Intent locks are normal parts of SQL Server's hierarchical locking architecture.

251
SeniorAdvanced

What is an update lock and why does SQL Server use it?

An Update (U) lock is used in certain read-before-write operations. It is compatible with shared locks but only one update lock can generally be held on a given resource in the relevant compatibility context. Its purpose includes reducing a common conversion-deadlock pattern where multiple sessions first acquire shared locks and then both attempt to convert them to exclusive locks. When the modification occurs, the update lock is converted to an Exclusive (X) lock.

252
SeniorAdvanced

What are key-range locks?

Key-range locks protect ranges of index keys rather than only existing rows. SQL Server uses them under SERIALIZABLE isolation to prevent another transaction from inserting rows into a range that would change the result of a protected predicate query. Effective range locking depends on the access path and index structure. Appropriate indexes can therefore influence both performance and the scope of locking under SERIALIZABLE workloads.

253
SeniorAdvanced

What is READ_COMMITTED_SNAPSHOT (RCSI)?

READ_COMMITTED_SNAPSHOT is a database option that changes READ COMMITTED so many reads use committed row versions instead of shared data locks. This can significantly reduce reader-writer blocking while still preventing dirty reads. Writers still take locks, and writer-writer conflicts remain. RCSI uses SQL Server's versioning infrastructure, so tempdb or the Persistent Version Store depending on platform/features must be monitored appropriately. The application should also be tested because concurrency behavior changes.

254
SeniorAdvanced

What is the difference between RCSI and SNAPSHOT isolation?

Both use row versioning, but their consistency scope differs. Under RCSI, each statement generally sees committed data as of the start of that statement. Two SELECT statements in the same transaction can therefore see different committed states. Under SNAPSHOT isolation, the transaction generally sees a consistent snapshot as of the start of the transaction. SNAPSHOT must be enabled and explicitly selected for the session/transaction. SNAPSHOT can also raise update-conflict errors when concurrent transactions attempt incompatible modifications.

255
SeniorAdvanced

What is a snapshot update conflict?

Under SNAPSHOT isolation, a transaction can read an older committed version without blocking a concurrent writer. However, if it later attempts to update a row that another transaction changed after the snapshot began, SQL Server may terminate the update with a snapshot update conflict. The application must handle the error appropriately, often by reloading current data and retrying the complete transaction when the operation is safe to retry. Row versioning reduces read blocking; it does not eliminate write-write conflicts.

256
SeniorAdvanced

What is the version store?

The version store maintains previous versions of rows required by row-versioning features such as RCSI and SNAPSHOT isolation. Long-running transactions can force older row versions to be retained for longer periods, increasing version-store space usage. Version-store pressure should therefore be monitored in systems using row-versioning heavily. The exact storage architecture can vary with SQL Server features and versions, including Accelerated Database Recovery, but the core concept is that readers may access retained row versions rather than blocking writers.

257
SeniorAdvanced

How do you capture and analyze a SQL Server deadlock?

Extended Events is the preferred mechanism for capturing deadlock information. SQL Server's system_health Extended Events session commonly captures deadlock reports automatically. A deadlock graph identifies participating sessions, statements, resources, lock modes, owners, waiters, and the selected victim. Analyze the dependency cycle rather than only the victim statement. Typical fixes include consistent object-access order, shorter transactions, better indexes, reduced lock footprint, appropriate isolation, and safe retry logic.

258
SeniorAdvanced

How should an application handle deadlock error 1205?

SQL Server chooses one transaction as the deadlock victim, rolls it back, and returns error 1205. Applications should generally treat this as a transient concurrency failure and retry the entire logical transaction when the operation is safe and idempotency/business rules permit it. Retry policies normally use a limited number of attempts with a delay or backoff. Retrying is important, but repeated deadlocks also require root-cause analysis. Retry logic should not hide a persistent transaction-design problem.

259
SeniorAdvanced

How can transaction scope in ASP.NET Core affect SQL Server concurrency?

A transaction should include only database operations that must succeed or fail together. Keeping it open while calling external APIs, waiting for user interaction, performing long computations, or doing unrelated work can hold locks and resources unnecessarily. In EF Core, SaveChanges normally uses a transaction for the changes in that call when required. Explicit transactions are appropriate for multi-step operations that need a larger atomic boundary. Production code should keep explicit transactions short, handle exceptions carefully, and avoid mixing long-running non-database work inside the transaction.

260
SeniorAdvanced

Two users edit the same ASP.NET Core record and the second save overwrites the first. How would you fix it?

This is a classic lost-update scenario. Add an optimistic concurrency token, commonly a SQL Server rowversion column, and configure EF Core to treat it as a concurrency token. When the edit page is loaded, retain the original rowversion. EF Core includes that original token in the UPDATE predicate. If another user has changed the record, the UPDATE affects no rows and EF Core raises DbUpdateConcurrencyException. The application should catch that exception and apply a defined business policy: show the current database values, let the user review differences, merge compatible changes, cancel, or explicitly retry. Silently overwriting the newer data should not be the default. This is a strong interview scenario because it connects SQL Server concurrency control with real ASP.NET Core/EF Core application behavior.