Deadlocking is a common issue in SQL Server that can have a direct negative impact on your users. What is deadlocking and how can it be resolved? All relational database management systems provide some kind of locking by design. There are times you want a table to be locked so that multiple updates happening to that table at the same time don’t conflict, or you don’t get incorrect data when selecting from that same table. These situations are slightly different when deadlocking occurs. During deadlocking, processes are in a stalemate – neither process is able to obtain exclusive access to the table because the other process is preventing it, and neither process can complete as long as the other one exists. In this case, the SQL Server database engine makes a decision – it must kill one process so the other process can complete. If an application is written properly, the application will account for deadlocks and rerun the killed process so hopefully the user doesn’t experience anything other than a slight delay. Even so, this can still be problematic for your users. So how can deadlocking be avoided?
The first step in minimizing deadlocks is to identify them. There are many different tools you can use to get detailed information on deadlocks. Some companies choose to purchase a 3rd party monitoring tool which can give them all sorts of diagnostic information, including deadlock information. A simple google search for SQL monitoring tools will show you many products that can do the job. You can also set up an extended event or you can use profiler, both of which will capture the deadlock graph to show you what objects are being deadlocked and by whom. One of the simplest and quickest methods I have found is to turn on trace flag 1222. Executing the command below in SQL Server will log all deadlocks to the SQL Error Log. As is the case with most things, each method has pros and cons, so do the research and choose the method that works best for your environment. And don’t forget to put this trace flag in your startup parameters if you want this information to be available after a SQL restart.
DBCC TRACEON (1222, -1)
Once you’ve identified the queries that are causing the deadlocks, it’s time to start analyzing them. Take each query involved and see how it can be tuned to run faster. Are there any missing indexes? Is there a transaction waiting on user input? Is the query holding a lock longer than necessary? Perhaps the query can be broken up into smaller parts. Are there any unnecessary joins, or are the queries overly complicated? Even if you can tune a query to run 1 second faster, that 1 second could mean all the difference in the amount of time the tables are being locked and thereby made available again for the next query.
So now you’ve tuned all the queries involved in the deadlock to run as efficiently as possible, and you’re still seeing deadlocks. It could be that your business processes are such that certain transactions are run so often, and from so many different places, that they are going to step on each other no matter how much you tune them. In this case you may need to consider a solution called Snapshot Isolation.
Simply stated, when Snapshot Isolation is used in SQL Server, writers will not block readers and readers will not block writers. This scenario is accomplished by using row versioning. When data is modified and the affected rows are committed, they are then copied to tempdb and given version numbers. If another session needs to read these same rows that are being updated, what will be returned to the reader is the version of the data at the time the read transaction started. Keep in mind the major drawback with Snapshot Isolation is that it does use more overhead in your database, specifically with tempdb, so this must be accounted for. There are 2 types of snapshot isolation that can be implemented in SQL Server – Read Committed Snapshot Isolation (commonly referred to as RCSI) and Explicit Snapshot Isolation.
When RCSI is turned on at the database level, all queries running in the database will behave the same way. To enable RCSI execute the following command:
ALTER DATABASE <database name> SET READ_COMMITTED_SNAPSHOT ON;
When using Explicit Snapshot Isolation, individual sessions that you specify will use Snapshot Isolation. To enable Explicit Snapshot Isolation, execute the following command:
ALTER DATABASE <database name> SET ALLOW_SNAPSHOT_ISOLATION ON;
Then within your code, include the following command before beginning your transaction:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
Keep in mind this article is intended to give a basic high-level overview of how you can use Snapshot Isolation to alleviate continued deadlocking. The topic of isolation levels is very detailed and there are many, many articles written on it. There are pros and cons to each solution, so it’s best to do some research before deciding which is right for your environment.
Deadlocking can sometimes feel like an impossible issue in your SQL Server database, especially when you don’t have enough information as to what is causing the deadlocks. The truth is, there are actually many ways to get detailed information on the specific queries causing your deadlocks. Once you have this information, query tuning should be your first line of defense. When you’re confident that you’ve tuned effectively, look to Snapshot Isolation to provide more relief. Using all of this information to your advantage should give your end users the application experience they expect.