8/23/2021

Application That Can Deadlock

83
Application That Can Deadlock
Stephen West July 29th, 2015 SQL Master Database

Deadlock condition can break our code at run time and can destroy business logic. We should avoid this condition as much as we can. This article is contributed by Vishal Garg. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to [email protected] A deadlock occurs when there is a circular chain of threads or processes which each hold a locked resource and are trying to lock a resource held by the next element in the chain. For example, two threads that hold respectively lock A and lock B, and are both trying to acquire the other lock. Applications should catch deadlocks and notify monitoring. Generally you want to find out about deadlocks from the calling application and this just makes sense because the calling application needs to handle errors and failed queries against the SQL Server. It’s not just deadlocks that can happen to an application.

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. 7 to xp packhome. 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.

Currently we are using CAP Java. In the ‘before’ section of our CDS event handlers, we are trying to run a UPSERT on the database, so it will be timely up-to-date to provide real-time data.

However two (or more) calls simultaneously access the before handle, which causes the transaction rollback described in this article (it says the transaction is rolled back because a deadlock is detected).

Application That Can Deadlock Cause

  1. Can you (or anyone else) confirm that a transaction has already begun when the before handle is accessed?
  2. Can you confirm that no two transactions are allowed to write to two different tables in the same database model? (Because that is what seems to be happening).
  3. Finally how can we solve this, given that we really need to update/insert into tables in that before handle? We are thinking: is there some sort of way to manipulate the transaction behaviour? Maybe use Update and Insert instead of Upsert?

Thanks…

What Is A Deadlock

Kjeld