Tips For Minimizing Deadlocks in SQL Server

A deadlock or fatal embrace is a situation which occurs when the a process is waiting for the resource that is locked by a second process and that second process is waiting for a resource that is locked by the first process. In this article, we will learn about ways to minimise deadlocks.

Tips for Minimizing Deadlocks

Database servers are configured in such a way that they can service multiple requests from multiple users. Obviously this increases the likelihood of conflicts because multiple processes can request access to same resources at the same time. This type of conflict is also known as block or blocking. Blocking usually resolves itself after the locking process releases the resource for waiting process. Sometimes, blocking creates a more serious condition, called a deadlock or fatal embrace, which occurs when the first process is waiting for the resource that is locked by the second process and the second process, is waiting for the resource that is locked by the first process (see below):

In this situation, both processes are stuck because each process is waiting for other to release the resource. Unless one process times out, the lock won’t clear itself.
We cannot totally prevent deadlocks, but they can be minimized by following the below tips:

Database Normalization

Bad database design is the priamry cause of deadlocks. As a Database Developer or DBA, we must ensure that our databases are properly normalized because it will reduce the likelihood of deadlock to occur.

Follow the consistent access pattern

The easiest way to avoid deadlocks is to be disciplined in your code. This can be achieved by ensuring that all the resources are accessed in the same order all the time. For example, if two concurrent transactions both started by requesting the lock for row 1 and later on requesting the lock for row 2. This will simply be a blocking situation rather than a deadlock because transaction 1 will never be deadlocking transaction 2 as resource locks will never be held out of order. This can be easily achieved by using Stored Procedures for data modifications activities because it will standardize the order of accessing the objects. It is also worth defining and implementing the programming policy when designing the application, which defines the order in which objects of the databases can be accessed. This will help you to avoid deadlocks.

Database Modifications

Make any necessary updates for the transaction before beginning the transaction and do not allow users to enter the data during transactions.

Avoid Cursors

If possible, limit the use of cursors within your code because the same locking rules will apply to a SELECT statement in a cursor definition that applies to another SELECT statement. When using cursors, ensure you have the correct isolation level or locking hint specified for your cursor SELECT statement. This is because SQL Server holds the locks for both SELECT statements within a cursor and the independent SELECT statement until both transactions is completed (This only applies if SQL Server is running in explicit or implicit transaction mode). For more information, see Cursor Locking.

Keep Transactions Small

Keep your transactions as short as possible because running several large transactions simultaneously increases the likelihood of a deadlock. If possible, breakdown the one large transaction in to several small transactions and then execute these transactions in batches. This is because exclusive or update locks are held longer for large transactions, which eventually block other activities and leads to possible deadlock situations. Executing the large transaction in batches will help to minimise the network roundtrips during the transaction, reducing the possible delays in completing the transaction and releasing the locks.

Reduce Transactions Time

Reduce the transaction time by making sure that you are not performing the same reads over and over again. If your application needs to read the same data more than once, then cache the data into variables, temporary tables or table variables. You can then reread the data from cache. This will help to reduce the lock time on actual resource.  We can also reduce lock time by making sure that our application grabs the locks at the latest possible moment and release it at its earliest time.

Controlling Lock Escalation

If applicable, use ROWLOCK or PAGLOCK to control of the lock escalation. This is because transaction locks in SQL Server consumes memory resources and as the number of locks increases, the memory decreases. If the percentage of memory used for transactions locks exceeds a certain threshold, then SQL Server converts the row or page locks in to table locks. This process is known as lock escalation. Lock escalation reduces the total number of locks held on the SQL Server instance, reducing the lock memory usage. While finer grain locks do consume more memory, but also can improve concurrency. For more information, see Lock Escalation (Database Engine).

Consider Using NOLOCK Hint

As we know, If we execute SELECT against table then SQL Server default isolation level locks the entire table and any other queries that try to access the same table will have to wait for the lock to be released. This is fine if we need accurate results, but if our table’s only stores historical data and these tables are updated only once a day and queried frequently during the day, then NOLOCK would be a better option. Carefully analyse your database environment and if appropriate, consider if we can use NOLOCK hint where possible.

Choose Appropriate Isolation Level

Consider using lower isolation level such as READ COMMITED for your transactions because it will reduce the locking contention. For example, if we use READ COMMITED isolation level then our share locks will be held for shorter duration as compared to higher isolation level such as SERIALIZABLE. For more information, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Consider Using Bound Connections

Consider using bound connections because it allows two or more connections share the same transactions and locks. For more information, see Using Bound Connections.

Conclusion

Deadlocks are caused by poor database design, inappropriate isolation level, inefficient code etc. In this article, we learned about the different ways to minimise deadlocks on SQL Server.