Deadlocking occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has. Usualy, they have different incompatible locks which are illegal or conflict (as per the compatible mode on locks).When this happens, SQL Server identifies the problem and ends the deadlock by automatically choosing one process and aborting the other process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.
Identify Deadlock:
- Using SQL Profiler
- Using System_health extended event(It highly depends on the ring buffer)
- Using Server side trace (DBCC TRACEON (1204) )
- Using SQL Error Log with a trace
- Predefined notification functionalities to log the deadlock info using service broker, extended events etc.
Here are some tips on how to avoid/resolve deadlocking on your SQL Server:
- Ensure the database design is properly normalized.
- Have the application access server objects in the same order each time.
- During transactions, don’t allow any user input. Collect it before the transaction begins.
- Avoid cursors.
- Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch. Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If your application does need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there, not from SQL Server.
- Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.
- If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
- Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.
- If appropriate, use as low of an isolation level as possible for the user connection running the transaction.
- Consider using bound connections.
- Adding missing indexes to support faster queries
- Dropping unnecessary indexes which may slow down INSERTs for example
- Redesigning indexes to be "thinner", for example, removing columns from composite indexes or making table columns "thinner" (see below)
- Adding index hints to queries appropriately(I dont prefer this mostly, but it has got its own scope)
- Redesigning tables with "thinner" columns like smalldatetime vs. datetime or smallint vs. int
- Modifying the stored procedures to access tables in a similar pattern
- Keeping transactions as short and quick as possible: "mean & lean"
- Removing unnecessary extra activity from the transactions like triggers
- Removing JOINs to Linked Server (remote) tables if possible
- Implementing regular index maintenance; usually weekend schedule suffices; use FILLFACTOR = 80 for dynamic tables (Needs a good evaluation)
The list really goes on. The solution will vary from situation to situation.
