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.




Array

5 Responses to “Tips For Minimizing Deadlocks in SQL Server”

  1. Clear and concise – excellent piece.

  2. “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”

    I think this is terribly misleading.

  3. Deadlocks can also be very tricky relating to “internal” DBMS issues such as the updating/splitting etc of index pages – basically completely out of programmer control. Consider a case where all transactions – no matter what they do – must update billing and/or auditing information when completed. In other words, these updates participate in every transaction no matter what they do – a recipe for deadlock dramas. Best way to deal ? – put the billing/auditing data on an asynch queue and use a dedicated updater to prcess one at a time. Guaranteed deadlock free…

  4. Great tips. Thank-you.

  5. There are only two deadlock scenaries:

    1) different order of locking resources
    2) promotion locking level inside transaction.

    Only 2 rules to prevent deadlocks:
    1) use one order when you use resources
    2) use max.level on locking, do not “read then write” or so on.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |