Tutorial: Diagnosing deadlocks

Use this tutorial to learn how to use the Database Tracing Wizard to view deadlocks that might occur in your database. You can use the Database Tracing Wizard to examine the conditions under which the deadlocks are occurring, and the connections that are causing them.

Note

This tutorial assumes you have followed the setup steps for the application profiling tutorials. If you have not, see Setting up for the application profiling tutorials.

Introduction

Deadlocks occur when two or more transactions block one another. For example, Transaction A requires access to Table B, but Table B is locked by Transaction B. Transaction B requires access to Table A, but Table A is locked by Transaction A. A cyclical blocking conflict occurs.

A good indication that deadlocks are occurring is when SQLCODE -306 and -307 are returned. To resolve a deadlock, SQL Anywhere automatically rolls back the last statement that created the deadlock. Performance problems occur if statements are constantly rolled back.

Preparation

Tip

You can copy and paste the SQL statements in this tutorial into Interactive SQL.

To create a deadlock

Start Sybase Central and connect to the test database app_profiling.db with the user ID DBA and the password sql.

If you have not created the test database, see Setting up for the application profiling tutorials.

If you are unfamiliar with starting Sybase Central and connecting to a database, see Connecting to a database on your own computer from Sybase Central or Interactive SQL.
In the left pane, click app_profiling - DBA, and then choose File » Open Interactive SQL.

Interactive SQL starts and connects to the app_profiling.db database.

In Interactive SQL, run the following SQL statements:

Create two tables

CREATE TABLE "DBA"."deadlock1" (
   "id" UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT, 
   "val" CHAR(1) ); 
CREATE TABLE "DBA"."deadlock2" ( 
   "id" UNSIGNED BIGINT NOT NULL DEFAULT AUTOINCREMENT, 
   "val" CHAR(1) );

Insert values into each table

INSERT INTO "deadlock1"("val") VALUES('x');
INSERT INTO "deadlock2"("val") VALUES('x');

Create two procedures which you later use to cause the deadlock

CREATE PROCEDURE "DBA"."proc_deadlock1"(  ) 
   BEGIN 
      LOCK TABLE "DBA"."deadlock1" IN EXCLUSIVE MODE; 
      WAITFOR DELAY '00:00:20:000'; 
      UPDATE deadlock2 SET val='y'; 
   END; 
CREATE PROCEDURE "DBA"."proc_deadlock2"(  ) 
   BEGIN 
      LOCK TABLE "DBA"."deadlock2" IN EXCLUSIVE MODE; 
      WAITFOR DELAY '00:00:20:000'; 
      UPDATE deadlock1 SET val='y'; 
   END;

Commit the changes you made to the database
COMMIT;

Exit Interactive SQL.

Profiling the problem

The Database Tracing Wizard can be used to create a diagnostic tracing session. The tracing session captures deadlock data.

To use the Database Tracing Wizard to create a diagnostic tracing session

In Sybase Central, choose Mode » Application Profiling.

If the Application Profiling Wizard appears, click Cancel.
Start the Database Tracing Wizard.
1. In the left pane click app_profiling - DBA, and choose File » Tracing.
  The Database Tracing Wizard appears.
2. On the Welcome page, click Next.
3. On the Tracing Detail Level page, select High Detail (Recommended For Short-Term, Intensive Monitoring) and click Next.
4. On the Edit Tracing Levels page, click Next.
5. On the Create External Database page, select Do Not Create A New Database. I Will Use An Existing Tracing Database, and then click Next.
6. On the Start Tracing page, select Save Tracing Data In This Database.
7. To place no limits on the amount of stored tracing data, select No Limit, and then click Finish.
8. Click Finish.

Create the deadlock.

In the left pane of Sybase Central, select the app_profiling - DBA database and choose File » Open Interactive SQL.
Interactive SQL starts and connects to the app_profiling - DBA database.
Repeat the previous step to open a second Interactive SQL window.

In one Interactive SQL window, run the following SQL statement:

CALL "DBA"."proc_deadlock1"();

In the second Interactive SQL window, run the following SQL statement within 20 seconds:

CALL "DBA"."proc_deadlock2"();

After a few moments, the ISQL Error window appears indicating that a deadlock has been detected. This is because proc_deadlock1 requires access to the deadlock2 table, which is locked by proc_deadlock2. And proc_deadlock2 requires access to the deadlock1 table, which is locked by proc_deadlock1.

Click OK.

Close both Interactive SQL windows.
To stop the tracing session, in Sybase Central select the app_profiling - DBA database and choose File » Tracing » Stop Tracing With Save.

Analyzing the data

The Application Profiling mode provides a graphical representation of the connections participating in the deadlock. It also provides a Connection Blocks tab that provides additional information on the blocked connections.

To view a graphical representation of the deadlock

Open the analysis file created during the tracing session.
1. In Sybase Central, choose Application Profiling » Open Analysis File Or Connect To A Tracing Database.
  The Open Analysis Or Connect To Tracing Database window appears.
2. Select In A Tracing Database.
3. Click Open.
4. Click the Identification tab, and type DBA in the User ID field and sql in the Password field.
5. Click the Database tab, and in the Database File field, browse to and select app_profiling - DBA.
6. Click OK.
View the graphical representation of the deadlock.
1. In the Application Profiling Details pane click the Status tab and choose the most recent ID from the Logging Session ID list.
  If the Application Profiling Details pane does not appear, choose View » Application Profiling Details.
2. At the bottom of the Application Profiling Details pane, click the Deadlocks tab. The most recent deadlock appears. Click the Deadlock list to view additional deadlocks.
The following image shows how the UPDATE statements created a deadlock condition.

For each connection, a table is shown with a arrow pointing at the other connection.

Each connection involved in the deadlock is represented by a table with the following fields:

Connection Name This field shows the user ID that opened the connection.
SQL Statement This field shows the actual statement involved in the deadlock. In this case, the deadlock was caused by the UPDATE statements found in the procedures you executed from each instance of Interactive SQL.
Owning Connection ID This field shows the ID of the connection that blocked the current connection.
Record ID This field shows the ID of the row that the current connection is blocked on.
Rollback Operation Count This field shows the number of operations that must be rolled back as a result of the deadlock. In this case, the procedures contained only the UPDATE statements, so the count is 0.

View deadlock data

You may want to view more deadlock data, such as how often they occur, and how long they last. Use the Connection Blocks tab to view a list of all deadlocks recorded during the database tracing session.

To view deadlock data

In the Application Profiling Details pane, click the Database Tracing Data tab.
Click the Connection Blocks tab, just above the Database Tracing Data tab.

The Connection Blocks tab appears, displaying block time, unblock time, and duration of each blocked connection.