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, ...