Testing Blocking and Deadlocks in the SQL Server agent (4378359)

How can a user test deadlocks and blocking in the SQL Server agent?

Lock: is done by database when any connection accesses the same piece of data concurrently. One connection needs to access a piece of data .

Block: when two connections need access to same piece of data at the same time. One connection is blocked because at a particular time, only one connection can have access. SQL knows that once the blocking process finishes the resource will be available and so the blocked process will wait (until it times out), but it won’t be killed.

Deadlock: happens when one connection is blocked and waiting for a second to complete its work, and this situation is again with another process as it waiting for first connection to release the lock. Neither process can finish because they are waiting on locked resources.  They are deadlocked. One of them must be killed to allow either of them to finish.

Timeout: when a client application makes a request for a resource, and while waiting for a response to be returned, decides things have taken too long, and stops waiting. This usually results in an error being raised by the client API (our database agent).

• Timeouts occur for a variety of reasons, including blocking, the request needing to do a ton of work on the server, or maybe the network is saturated or simply really slow. There are millions of reasons, all ultimately because the client was waiting, and gave up after a certain period of time (the timeout threshold).
• An important thing to note about timeouts is that they do not cause other problems (except if the error that's raised is handled incorrectly), they only a symptom of another problem.
• To solve the timeout issue, solve the issue that caused the timeout.

Fixing tImeout issues

In the SQL Server agent log file, a timeout often looks like this:

com.quest.qsi.fason.core.collection.processor.InstanceProcessor - Failed to run DBSSDatabaseSummaryProcessor processor of DBSS_Database_Summary collection. java.lang.RuntimeException: Failed to execute collection [DBSS_Database_Summary], reason=The query has timed out.

Timeouts can often be addressed in our agent by increasing the query timeout value for the collection in agent’s administration panel in the Collections area. 

To set the timeout parameter follow the steps below:

1. Go to the Global View Databases dashboard
2. Select the problematic agent and click Settings | Administration 
3. Select "Collections" and for the problematic collection, change the query timeout to 300 seconds
4. Finally Save the changes

Creating a block and deadlock for testing in a SQL Server database

We can simulate a block and deadlock in SQL Server for testing by doing the following 

1). Open SQL Server Management Studio and run the following scripts in a test database. These scripts create 2 simple tables containing 2 integer columns each. The id column in each table is the primary key (with a clustered index) and the col field in the second table references the id field in the first (i.e. there is a foreign key between the 2 tables on the col field in the second table).

CREATE TABLE Tbl1 (id INT NOT NULL PRIMARY KEY CLUSTERED, col INT)

CREATE TABLE Tbl2 (id INT NOT NULL PRIMARY KEY CLUSTERED, col INT REFERENCES dbo.Tbl1(id))

2). Once you’ve created the above mentioned tables, open up a New Query window and execute the following script. This script will start a transaction and insert a record into table Tbl1. The new record will contain a primary key field value of 2. The transaction will remain open, i.e. it won’t be committed or rolled back (yet).

BEGIN TRAN
INSERT dbo.Tbl1 (id, col) VALUES (2, 999)

3). Open up another New Query window and execute the following script. This script will also start a new transaction. It will then insert a record into table Tbl2 with a value of 2 for the ‘col’ field, which references the primary key in Tbl1. Since the transaction which inserted a record with a primary key value of 2 into Tbl1 hasn’t committed or rolled back yet, the transaction on Tbl2 waits for the first transaction to finish. This transaction will also be left uncommitted. The primary key field value being inserted into Tbl2 is 111.

BEGIN TRAN
INSERT dbo.Tbl2 (id, col) VALUES (111, 2)

Navigate in Foglight to the SQL Server agent’s Blocking (current) dashboard and within 60 seconds you should see an event similar to the following: (you can refresh the page if necessary)

The first insert statement is blocking the second insert statement.

Likewise, in the SQL PI dashboards the blocking is captured by a different mechanism using the sampling query which runs once every second.

4). Return to the first Query window and execute replace the previous statement with the following, then execute it. This script attempts to create a record with a primary key field value of 111 in Tbl2 as part of the open transaction in Session #1. At this stage, the SQL Engine will detect a deadlock and roll back one of the two open transactions. You should see the deadlock error message (such as the one shown below) in one of the two session windows.

BEGIN TRAN
INSERT dbo.Tbl2 (id, col) VALUES (111, 555)

Msg 1205, Level 13, State 47, Line 2
Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The blocking (current) statement will be cleared

And there now should be a Deadlock in the SQL Server agent’s Deadlocks page

Note: Since the blocking occurs for more than a second, the deadlock will still likely be shown in SQL PI as a blocked event after the blocking concludes. 

Alternative, deadlocks that happened immediately and that didn't go through extended blocking such as those which were part of job steps, will more likely be shown in SQL PI as a Deadlock.