For an instructor lead, in-depth look at learning SQL click below.
Introduction
In the world of database management, deadlock is a common issue that many programmers and database administrators come across. A deadlock occurs when two or more tasks permanently block each other by each task holding a lock on a resource which the other tasks are trying to lock. This results in an impasse where the tasks are unable to proceed. This is a very serious issue in SQL Server, as it can significantly slow down database operations, and in worst case, completely halt them.
Understanding the basics of SQL Server Deadlocks
|
1 2 3 4 5 6 7 8 |
/*Example of SQL code*/ BEGIN TRANSACTION UPDATE Table_1 SET Column_1 = value WHERE Condition UPDATE Table_2 SET Column_2 = value WHERE Condition COMMIT |
In the above example, we have two UPDATE statements. If two users were to execute this batch simultaneously, it could potentially lead to a deadlock if the rows being updated by the second UPDATE statement overlap between the two instances.
Identifying Deadlocks
SQL Server is capable of managing deadlock issues automatically by identifying deadlocks as they occur and choosing one of the tasks as a “victim” to rollback, thereby allowing other tasks to continue. However, it’s always better if you can identify and prevent deadlocks before they occur. First step towards this is finding out if deadlocks are occurring. You can do this with the help of SQL Server Profiler or with the system_health extended events session.
SQL Server Profiler
|
1 2 3 4 5 6 |
/*Trace Settings */ -- Filter for only deadlock graph events SELECT * FROM fn_trace_geteventinfo(@TraceID) WHERE eventid = 148 -- Deadlock graph |
System_health Extended Session
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
/* Deadlock graph from system_health session */ WITH DeadlockData AS ( SELECT CAST(target_data AS XML) AS TargetData FROM sys.dm_xe_sessions AS s JOIN sys.dm_xe_session_targets AS t ON s.address = t.event_session_address WHERE t.target_name = 'ring_buffer' AND s.name = 'system_health' ) SELECT TargetData.query('(<a href="mailto:RingBufferTarget/@event" >RingBufferTarget/@event</a>)[1]') AS DeadlockGraph FROM DeadlockData WHERE TargetData.exist(<a href="mailto:'RingBufferTarget/@event" >'RingBufferTarget/@event</a>[contains(.,''xml_deadlock_report'')]') = 1; |
Resolving Deadlocks
Design considerations are key aspects in the prevention of deadlocks. Here are a few to think about:
1. Always access objects in the same order;
2. Write as short as transactions as possible;
3. Try to avoid user interaction in the middle of transactions, because that’s typically the most common form of locking and blocking problem.
Wrap-up
Deadlocks can cause serious performance issues in SQL Server databases but with a little knowledge and application of best practices, they can be greatly minimized and even eliminated. Proactively monitoring from the database side is always the best defense.