A deadlock is defined as the situation where two or more processes
permanently block each other by acquiring the lock on a resource that has
already been requested by another process. This means one process is trying to
lock the resource while the other one has already locked it. The following graph
shows the state of deadlock.
- Process P1 has a lock on resource R1 and has requested a lock on resource R2.
- Process P2 has a lock on resource R2 and has requested a lock on resource R1.
- The situation shows that neither the process can continue without a resource being released nor a resource can be available till a process continues, which means a deadlock state exists.
Sometimes, a deadlock state is also called Cyclic Dependency as, process
P1 depends on the process P2. In SQL Server, database engine automatically
detects deadlock cycles. The SQL Server database engine chooses one of the
sessions as the deadlock victim and the current transaction is terminated to
break the deadlock.
Here, the process refers to a transaction. By default SQL Server
transaction doesn’t stop unless LOCK_TIMEOUT is set.
Deadlock can occur on any system having multiple threads not just
on relational database management system. When we talk about database engine,
sessions can encounter deadlock when acquiring non database resources like;
memory or threads.
The above figure shows that Transaction 1 has acquired a lock on
resource Supplier which Transaction 2 wants to acquire. However, Transaction 2
has acquired a lock on resource Part which Transaction 1 wants to acquire. Both
are dependent on each other. There is a deadlock between T1 and T2.
In an
SQL Server database, deadlocks can also occur in a table when it is partitioned
and LOCK_ESCALATION is set to auto. In this case concurrency increases, as
database engines allow table partition lock on HoBT level instead of Table
Level. Separate transaction holds partition locks in a table and wants a lock
on other transactions and results in a deadlock. This deadlock can be avoided
by setting LOCK_ESCALATION to TABLE and it will reduce the concurrency.
Resources That Can Cause Deadlock:
Following types of resources can cause deadlock by blocking
transactions or processes.
· Locks: Acquires
locks on resources like; pages, objects, rows metadata, and can cause deadlock.
If transaction T1 has a shared lock on Table Row r1 and waiting to get the
exclusive lock on row r2. Transaction T2 has a shared lock on row r2 and
waiting to get the exclusive lock on r1. In this situation both T1 and T2 wait
for resources to be released and dependent on each other.
· Worker Threads: Suppose,
one task is in queue and waiting for an available worker thread; this can cause
deadlock. Let’s suppose, Session S1 starts a transaction and acquires a shared
lock on row r1 and goes to sleep. Meanwhile, the active sessions running on all
available worker threads try to acquire exclusive lock (X) on row r1. In this
case session s1 cannot acquire worker thread and cannot commit transaction and
release the lock on r1, eventually resulting in a deadlock.
· Memory:
Let’s talk about the
two concurrent queries, Q1 and Q2 that execute user-defined functions,
acquiring 20 MB and 10 MB memory space respectively. If the query needs 30 MB
of space and available memory is only 20 MB, then both the queries have to wait
for each other to release memory which later results in a deadlock.
· Parallel Query Execution-Related Resources: In SQL Server producer,
coordinator, and consumer threads, associated with exchange port; may block
each other and cause deadlock. SQL Server determines the degree of parallelism
in case of parallel query execution, based on the current workload on a worker
thread.
Multiple Active Result Sets (MARS)
Resources. These resources are used to
control interleaving of multiple active requests.
1. User Resource.
When a thread is waiting for a resource that is controlled by a user
application.
2.
Session Mutex.
One task can run under the session at a given time and before the task starts,
it must have exclusive access to the session mutex.
SQL Server Deadlock Detection and Minimization
Various Modes of Deadlock
The transaction in
deadlock waits until it is broken by some external process. There are distinct
states of Deadlock; also known as Coffman
Conditions and these include:
- Mutual Exclusion: At a given period of time one resource is assigned to a single process.
- Resource Holding: A resource is hold by a process which is further waiting for another resources held by other executing process in the loop.
- No Preemption: When a process is being completed, it can release the resource that it holds while execution phases.
- Circular Wait: In this state, a process is waiting for a resource to be released by another process which is in turn waiting for some other resource.
Process of Detecting Deadlocks
Deadlock Detection scheme
first allows the deadlock to occur and then examine it for detecting that it
has been occurred; to further correct it. An algorithm is been utilized to
detect the allocation of resources that are further restarted or rolled back
some of the processes for removing the detected deadlock.
After detecting the
deadlock, the Database Engine dismisses the existing batch and the transaction
is rolled back and displays error 1205 to the respective application. When a
transaction for deadlock is rolled back, the victim then releases all the locks
that the specified transaction holds.
Error 1205 records the
information in the error log for all the processes\ threads or the resources
that are involved; or participated in the formation of particular deadlock.
Now, when the deadlock is been detected, it can be corrected by implementing
either of the given methods:
· Process Termination:
The chain of executing and the waiting processes in the deadlock can be
aborted. Either some or entire processes in the queue may be dismissed to
ensure fast and speedy resolution.
· Resource Allocation:
The allocated resources are successfully released by the processes and are
further allocated to the waiting processes in the loop.
The Concept of Minimizing Deadlocks
Though it is not possible
to avoid or eradicate the occurrence of Deadlock but this can be reduced to
some extent. The following coding conventions can be used to lower the
occurrence of Deadlock state.
View Objects in Actual
Order:
The transactions access
object in exactly same order in which they were generated. When the executing
process is completed or rolled back, the second process in the queue will be
started and the same procedure continues for all the following processes.
Avoid User Interference in
Transactions:
Avoid transaction that
involves user interaction such as the processes that requires user’s manual
response. The queries or processes that run without manual interaction are much
faster than those that are bounded with user interventions.
Minimizing the Volume of
Transactions:
The major cause behind the
occurrence of Deadlock situation is the large size of transactions. Minimizing
the volume of transactions and retaining them under one batch can help in
resolving this issue as the longer transactions holds resources for longer
period of time till the process gets completed or rolled back. Keeping the
transactions under single batch reduces the load over network as then it does
not have to take huge roundtrips.
Ample Resources for the
Transactions:
Ensure that there are
ample of resources available corresponding to the processes so that each
transaction is allocated with at least one resource. More the resources, faster
will be the process execution time.
Prevent
Locking Contention:
By running the transaction
at lower isolation level, the occurrence of deadlock situations can be
minimized. Implementation of READ_COMMITTED on transactions allows them to read
the data that is previously read but not modified; by succeeding transaction
without the completion of previous transaction.
Conclusion:
0 comments:
Post a Comment