Detecting blocked and blocking queries
If a transaction is waiting for some resources because the same resources are locked by other transactions, that transaction is considered a blocked transaction. On the contrary, a transaction that has locked the resources and caused other transactions to wait is considered a blocking transaction.
Long-running transactions can block other transactions and queries for a long time. In a heavily transacted database, many times we face the "blocking" problem. If a transaction is not completed because it is blocked, it can take time to complete, which in turn blocks the other transactions.
In this recipe, we will learn how to find which queries are blocked by which queries, and how to kill those blocking queries, as part of the immediate solution!
Getting ready
As we are going to see how to find blocked and blocking queries, we will first create a scenario so that we can create a blocking query.
The following are the prerequisites for this recipe:
An instance...
