Scholarly Overview- What is a Deadlock In SQL Server?

What is a Deadlock In SQL Server?

Deadlock in SQL Server is a situation where two processes are waiting for one another to issue the locked resources. Therefore, neither a process can move forward until it gets the resource freed up, nor does, it can be completed unless it moves with resources made available by another process.

Let us understand with a simple example:

Suppose, there are two database sessions running on SQL Server: DB1 and DB2. Session DB1 requested for data lock and let’s denote that data as “X”. Similarly, the DB2 has a lock on data we denote it with “Y”.

Now, the session DB1 requires to lock Y for running a statement, but it is help by the DB2 session. Similarly, DB2 requires to lock X but it is already locked and held by session DB1. In this situation, neither of the database sessions would be able to proceed further and run the statement unless any one of them release the lock from the data. This clash between databases for freeing up the locked resources depicts what actually a Deadlock is.

Common Deadlocks in SQL Server:

Cycle Deadlock: This type of deadlock occurs when processes acquire lock on different resources and then they call for acquiring resources that both the process have. For example: A Process (P1) puts an exclusive lock on page 1:001 during a transaction and another Process (P2)puts an exclusive lock on page 1:300. Then, P1 tries to lock the page 1:300 and waits for its availability. Simultaneously, P2 requests for a lock on page 1:001. And this is how a deadlock in SQL Server occurs.

Conversion Deadlock: In this case, both the processes (P1, P2) have a shared lock on the resource and then wants to convert the shared lock into exclusive lock within a transaction. In this situation, neither P1 nor P2 can release the shared lock and thus a deadlock happens.

Deadlock In SQL Server 2014, not only happens due to locked resources, but it also takes place on varied resources that includes memory, multiple active result sets etc. For example: A Process P1 is holding a lock on a waretable and is in the queue to get memory released in order to continue. Another Process P2 have some memory on hold, but it cannot release it until the lock on the ware table is released. Therefore, the processes deadlock.

Deadlock Detection in SQL Server: Automated Detection!

In SQL Server, an individual process called “LOCK MONITOR” runs that keeps a track of deadlock situations in every 5 seconds. In the first phase, this process checks out for the processes that are waiting for resources to be released. It examines deadlock by looking out for the list of waiting lock requests and finds out if two processes are requesting for lock on same resource (circular). If deadlock is detected, the SQL Server will rollback a transaction of any one process and quits the associated batch. To make the user known with the problem, the application throws back error 1205.

The SQL Server examines the processes and the one that calls for least rollback will be terminated. Nevertheless, if the processes have same rollback, the application will randomly select the process to terminate. If deadlock in SQL Server detects, if reduces the time interval as low as 100ms. And seconds.

Settings SQL Server Deadlock Priority:

By using SET DEADLOCK_PRIORITY statement, it is possible for SQL admins to set priority for a process with the parameters LOW, NORMAL, and HIGH. Also, priority can be set in numerical values between -10 to 10. By default, the priority set is NORMAL. So, if a deadlock happens and priority is other than default, then the process with less priority will become the victim of deadlock.

Deadlock in SQL Server is a situation of resource contention and if it happens, the application tries to handle it itself. Also, the user can get to know that the process is aborted due to resource locking with the error code 1205.