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.
No comments:
Post a Comment