Earlier this week, I wrote a few blog posts on SQL Server Deadlocks. Lots of people asked me if I could convert them into Interview Questions and Answers format so it can help them in potential future interviews. Here are the blog posts:
- Understanding SQL Server Deadlocks: A Beginner’s Guide
- Detecting Deadlocks in SQL Server Using Trace Flags 1204 and 1222
- SQL SERVER – Resolving Deadlock by Accessing Objects in the Same Order
- SQL SERVER – Locking, Blocking, and Deadlocking: Differences, Similarities, and Best Practices
Here are Interview Questions answered based on them:
A: A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource that the other tasks are trying to lock. This situation creates a cyclic dependency between the tasks, and unless resolved by an external process, both transactions will wait indefinitely.
Q: How does SQL Server detect deadlocks?
A: SQL Server uses a deadlock monitor to detect deadlocks. The monitor checks for deadlocks at regular intervals (by default, every 5 seconds). When a deadlock is detected, the interval is reduced to quickly detect subsequent deadlocks. Once deadlocks stop, the interval is gradually increased back to the default.
Q: How does SQL Server resolve deadlocks?
A: When a deadlock is detected, SQL Server selects one of the transactions as the deadlock victim based on cost or assigned priority. The victim transaction is rolled back, releasing its locks and allowing the other transactions to proceed.
Q: What can I do to minimize deadlocks?
A: You can minimize deadlocks by following practices like accessing objects in the same order within transactions, keeping transactions short, using a lower isolation level, and enabling row versioning.
Q: How can I identify deadlocks in my system?
A: You can query the system_health extended events session in SQL Server to identify deadlocks. The captured xml_deadlock_report events contain the full deadlock graphs that visualize the processes and resources involved in the deadlock.
Q: What information is provided in a deadlock graph?
A: A deadlock graph contains a process-list with information about the processes involved, a resource-list with details about the contended resources, nodes/edges showing how resources are held or requested by processes, and a victim-list highlighting the process selected as the deadlock victim.
Q: What should I do if deadlocks continue to occur frequently in my system?
A: If deadlocks continue to occur frequently despite your efforts to minimize them, there are a few steps you can take:
- Add appropriate indexes to frequently accessed tables to improve read/write performance and reduce locking.
- Increase the deadlock interval to a shorter time (less than the default 5 seconds) to detect deadlocks faster.
- Check for long-running or blocking queries that may be causing serialization issues leading to deadlocks. Optimize such queries.
- For critical transactions, set a higher priority so they are less likely to be chosen as the deadlock victim.
- As a last resort, consider redesigning the database or application to reduce dependency between concurrent transactions.
Q: Why does a deadlock cause an error 1205 in my application?
A: When a SQL Server deadlock is detected and resolved by choosing a deadlock victim, that transaction is rolled back, and your application receives SQL error 1205. This error indicates that a deadlock was encountered, and the transaction has been rolled back. The application can then retry the transaction to complete it successfully.
Q: How can I prioritize my transactions to avoid being chosen as the deadlock victim?
A: You can set the DEADLOCK_PRIORITY for a session to a value between -10 to 10. The lower the value, the more likely that the session’s transactions will be chosen as the deadlock victim. Setting a high priority (like 10) makes those transactions less likely to be victims. You can set the priority like this:
SET DEADLOCK_PRIORITY 10;
Q: What information does the deadlock error message contain?
A: The deadlock error message contains the following useful information:
- Error number: 1205 indicating a deadlock was encountered
- Severity: 14
- State: 1
- Message text: Transaction (<TX ID>) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
- The transaction ID (TX ID) of the process chosen as the deadlock victim
Q: What are trace flags 1204 and 1222 used for?
A: Trace flags 1204 and 1222 are used for detecting and diagnosing deadlocks in SQL Server. They capture detailed deadlock information and log it in the SQL Server error log.
Q: How do trace flags 1204 and 1222 differ?
A: Trace flag 1204 focuses on the nodes involved in the deadlock, while trace flag 1222 provides a process-centric and resource-centric view of the deadlock. Trace flag 1222 also provides more detailed information like input buffers.
Q: Where can deadlock information captured by trace flags 1204 and 1222 be found?
A: In the SQL Server error log.
Q: What performance impact can trace flags 1204 and 1222 have?
A: They can increase I/O, CPU and memory usage, and impact concurrency due to the additional processing required to capture and log deadlock information.
Q: What steps are recommended for diagnosing deadlocks using trace flags?
A: Enable the trace flags, monitor the error log, analyze the deadlock information, resolve the deadlock, and then disable the trace flags.
Q: What is locking?
A: Locking is a mechanism used by databases to ensure that multiple transactions can access shared resources without compromising data integrity. When a transaction wants to access a resource (e.g., a row in a table), it requests a lock. If the lock is granted, the transaction can proceed with its operation.
Q: What is blocking?
A: Blocking occurs when one transaction is waiting for another transaction to release a lock on a resource. This can cause performance issues and delays in processing transactions.
Q: What is deadlocking?
A: A deadlock occurs when two or more transactions are waiting for each other to release locks, creating a circular dependency and preventing any of the transactions from proceeding.
Q: What are the two primary types of locks?
A: The two primary types of locks are:
Shared locks: Allow multiple transactions to read a resource, but none can modify it.
Exclusive locks: Allow only one transaction to access a resource for reading or writing.
If you believe I should have included any other interview questions over here. Do leave a comment. Meanwhile, do not forget to check out my popular consulting service Comprehensive Database Performance Health Check. You may also consider subscribing to YouTube.
Reference: Pinal Dave (http://blog.SQLAuthority.com)