7 questions found
What is a deadlock in SQL Server, and why does it happen?
Beginner
A deadlock happens when two or more transactions each hold a lock that the other transaction needs, creating a cycle where neither can move forward, so SQL Server must step in and end one of them so the other can continue.
-- Transaction 1 locks Row A, then wants Row B
-- Transaction 2 locks Row B, then wants Row A
-- Neither can proceed, so this is a deadlock
Real-world example
Two warehouse update jobs run at the same time, each locking a different product row before trying to update the other one, causing SQL Server to cancel one of them automatically to break the cycle.
Common follow-ups: Does a deadlock ever resolve itself without SQL Server intervening?;Is a deadlock the same thing as normal blocking?
Isolation & Locking;Transactions & ACID
How does SQL Server decide which transaction becomes the deadlock victim?
Beginner
SQL Server generally chooses the transaction that is cheapest to roll back based on the amount of work already done, unless a session has been given a higher deadlock priority, in which case a lower priority session is chosen as the victim instead, regardless of cost.
SET DEADLOCK_PRIORITY LOW;
-- This session will be chosen as the victim before others
-- with normal or high priority
Real-world example
A reporting job is given a low deadlock priority so that if it ever conflicts with a more important order processing transaction, the reporting job is the one automatically rolled back instead.
Common follow-ups: What deadlock priority values are available besides LOW and HIGH?;Does setting a low priority guarantee that session will never cause a deadlock?
Isolation & Locking;Transactions & ACID
What error does an application receive when its transaction is chosen as a deadlock victim, and how should the application respond?
Intermediate
The victim transaction receives error number 1205, and the application should catch this specific error and automatically retry the entire transaction after a short delay, since the transaction was rolled back through no fault of its own and will often succeed on a second attempt.
BEGIN TRY
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 1205
-- retry logic goes here
ROLLBACK TRANSACTION;
END CATCH;
Real-world example
A payment application automatically retries a transfer that failed with error 1205, successfully completing the transfer on its second attempt after the conflicting transaction had already finished.
Common follow-ups: How many times should an application reasonably retry before giving up?;Should there be a delay between retry attempts, and if so, how long?
Error Handling with TRY CATCH;Transactions & ACID
How do you capture detailed information about deadlocks that are happening on a server so you can investigate their root cause?
Intermediate
You set up an Extended Events session capturing the xml_deadlock_report event, which automatically records full details about every deadlock that occurs, including the exact queries, resources, and sessions involved, giving you everything needed to diagnose the problem after it happens rather than needing to catch it live.
CREATE EVENT SESSION DeadlockCapture ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file (SET filename = 'Deadlocks.xel')
WITH (STARTUP_STATE = ON);
Real-world example
A database administrator sets up a permanent deadlock capturing session, finally getting the full details needed to fix a deadlock that had been happening randomly for weeks without anyone catching it in the act.
Common follow-ups: What information is included in a deadlock graph?;Can SQL Server Profiler also capture this same deadlock information?
SQL Server Profiler & Extended Events;Error Handling with TRY CATCH
How would you read a deadlock graph to identify the exact queries and resources that caused a specific deadlock?
Advanced
You examine the captured XML deadlock report, looking at the process list to see each transaction's query text and the resource list to see exactly which locks each transaction was holding and waiting for, letting you trace the cycle of dependencies and pinpoint which two operations were actually conflicting with each other.
-- Deadlock graph XML includes process-list and resource-list sections
-- showing exactly which queries and locks were involved
SELECT CAST(event_data AS XML) FROM sys.fn_xe_file_target_read_file('Deadlocks*.xel', NULL, NULL, NULL);
Real-world example
A developer reads through a captured deadlock graph and discovers two stored procedures were updating the same two tables in opposite order, immediately explaining why the deadlock kept occurring.
Common follow-ups: What tools provide a more visual way to interpret a deadlock graph than raw XML?;How do you correlate a deadlock graph back to the actual application code that caused it?
SQL Server Profiler & Extended Events;Query Optimization & Plans
What design changes can you make to application code and database access patterns to reduce the frequency of deadlocks?
Advanced
You can ensure transactions always access tables and rows in the same consistent order, keep transactions as short as possible to minimize the time locks are held, use an appropriate isolation level such as read committed snapshot to reduce locking altogether, and add appropriate indexes so operations lock only the specific rows they need rather than larger ranges.
-- Always update tables in the same consistent order
-- across every part of the application
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountId = 2;
COMMIT TRANSACTION;
Real-world example
A development team fixes a recurring deadlock by ensuring every part of their application always updates the accounts table in ascending order by account id, eliminating the conflicting access pattern that had been causing the cycle.
Common follow-ups: How does read committed snapshot isolation specifically help reduce deadlocks?;What role do missing indexes play in causing unnecessary locking that leads to deadlocks?
Isolation & Locking;Indexes
What is the difference between a deadlock and normal blocking, and how do you tell them apart when troubleshooting a slow application?
Intermediate
Blocking happens when one transaction simply has to wait for another to release a lock, which resolves naturally once the first transaction finishes, while a deadlock is a genuine cycle where neither transaction can ever finish without one of them being forcibly rolled back, meaning blocking causes delay while a deadlock causes an actual error that the application must handle.
-- Blocking: Transaction 2 waits, then proceeds once Transaction 1 commits
-- Deadlock: Transaction 1 and 2 wait on each other, so SQL Server cancels one
Real-world example
A support team initially assumes a slow report is caused by a deadlock, but after checking sys.dm_exec_requests finds it is actually just normal blocking behind a long running transaction that eventually completes on its own.
Common follow-ups: How do you check whether a currently slow query is experiencing blocking rather than an actual deadlock?;Does blocking ever escalate into an actual deadlock?
Isolation & Locking;Query Optimization & Plans