One thing we should always keep in mind that Deadlocks cannot be avoided completely, but the number of deadlocks in an application can be minimized.
Locking is a mechanism used by the Microsoft SQL Server Database Engine to synchronize access by multiple users to the same piece of data at the same time
Applications do not typically request locks directly. Locks are managed internally by a part of the Database Engine called the lock manager.
Deadlocking is often confused with normal blocking. When one transaction has a lock on a resource that another transaction wants, the second transaction waits for the lock to be released. By default, SQL Server transactions do not time out (unless LOCK_TIMEOUT is set). The second transaction is blocked, not deadlocked.
A deadlock is a situation wherein two transactions wait for each other to give up their respective locks.
As I have mentioned in the beginning of this article, that Deadlocks cannot be avoided completely, but minimizing deadlocks can increase transaction throughput and reduce system overhead because fewer transactions are:
- Rolled back, undoing all the work performed by the transaction.
- Resubmitted by applications because they were rolled back when deadlocked.
Now due the advantages of minimizing deadlocks, the application should be very well designed in order to reduce the number of deadlocks in a multi-user system.
Below I am mentioning some of the ways to help avoid deadlocks:
- Transactions in an application should be short: If in an application, several queries whether it is SELECT, INSERT, UPDATE or DELETE takes more than few seconds to complete, blocking is likely to happen because exclusive or update locks are held by those long running queries. Hence, we should always try to reduce the transaction time by writing optimized T-SQL code, optimizing indexes, avoiding cursors. Keeping transactions in one batch minimizes network round trips during a transaction, reducing possible delays in completing the transaction and releasing locks.
- Queries should be properly executed: In many application, the running query can be canceled then it becomes very important that the code also roll back the transaction. In this type of application, we should always pass the queries inside the transactions to keep the database in the consistent state and to release all the locks held by the queries which can result in blocking and deadlocks.
- Using Low Isolation Level: If the application permits, then we should always try to determine if the code can be modified to minimize the lock level SQL Server selects (i.e. row vs. page, page vs. extent, extent vs. table) which helps to reduce the locking contention.
- User Interaction in Transactions should be avoided: We should always write transactions that executes and completes without any User Interaction as they tend to be faster than the transactions which involves user interaction. For example, if a transaction is waiting for user input, and the user goes to lunch, or even home for the weekend, the user holds up the transaction from completing. This degrades system throughput because any locks held by the transaction are released only when the transaction is committed or rolled back.
- Access Objects in the same order: If all the concurrent transactions access objects such as tables, views etc in the same order then the possibility of deadlocks is reduced to a great extent.
- Database Redesigning: Sometimes, redesigning the database can prevent deadlocks. Many a times, due to excessive normalization of the database every transactions involves multiple joins which holds locks on multiple tables which automatically increases the chances of blocking and deadlocks. In this situation, Denormalizing the tables can reduce the number of deadlocks.
- Number of Indexes: We should determine if additional or less indexes would improve the deadlock. Additional indexes would be needed if most of the times table scanning is occurring. As well as, less indexes would be needed if unneeded indexes exists which are not used in the query plan for any queries and these unnecessary indexes need to be updated during every INSERT, UPDATE and DELETE statements which increases the execution time of these statements which also increase the chances of deadlocks.
- Use Deadlock_Priority Option: We can use Deadlock_Priority Option to specify the relative importance of the current session to continue processing if it is deadlocked with another session.The default deadlock priority in SQL Server 2000 and 2005 is NORMAL. SQL Server 2000 has two other settings of LOW and HIGH, whereas SQL Server 2005 has 21 settings based on a numeric priority. With this being said, SQL Server scans for deadlocks on a regular basis (i.e. every 5 seconds in SQL Server 2005) and the following logic is used to determine the victim of a deadlock: If the DEADLOCK_PRIORITY for one of the spids is lower than the other, the lower priority spid is chosen as the victim. If the DEADLOCK_PRIORITY is the same for each spid involved, the spid that is less expensive/costly to rollback is chosen as the victim.
- Customizing the Lock Time Out: The LOCK_TIMEOUT setting allows an application to set a maximum time that a statement waits on a blocked resource. When a statement has waited longer than the LOCK_TIMEOUT setting, the blocked statement is canceled automatically, and error message 1222 (
Lock request time-out period exceeded) is returned to the application. Any transaction containing the statement, however, is not rolled back or canceled by SQL Server. Therefore, the application must have an error handler that can trap error message 1222. If an application does not trap the error, the application can proceed unaware that an individual statement within a transaction has been canceled.
- Using TRY/ CATCH: TRY/CATCH block helps to write structured and well-designed deadlock-resolving code. SQL Server 2005 returns the value 1205 [Invalid Deal Sequence number in table] as a result of the transaction getting deadlocked with another process. The catch block can catch the 1205 deadlock error and can take the appropriate steps suggested by the code.
SQL Server 2005 offers a more robust set of tools for handling errors than in previous versions of SQL Server. Deadlocks, which were virtually impossible to handle at the database level in SQL Server 2000, can now be handled. By taking advantage of these new features, we can now focus more on IT business strategy development and less on what needs to happen when errors occur.