Lesson 2: Creating a deadlock and capturing data

Create a deadlock and capture information about it by using the Database Tracing Wizard to create a diagnostic tracing session.

Prerequisites

This lesson assumes that you have the roles and privileges listed in the Privileges section at the start of this tutorial: Tutorial: Diagnosing deadlocks.

This lesson assumes that you have completed all preceding lessons. See Lesson 1: Setting up the database.

Context and remarks

In the application profiling tutorials, tracing information is stored in the test database (app_profiling.db), which is the same database you are running the tutorials on. However, if you profile a database that experiences heavy loads, you should consider storing tracing data in a separate database from your production database to avoid impacting performance.

Task

Start Sybase Central. Click Start » Programs » SQL Anywhere 16 » Administration Tools » Sybase Central.
In Sybase Central, connect to app_profiling.db as follows:
1. Click Connections » Connect With SQL Anywhere 16.
2. In the Connect window, complete the following fields to connect to the test database, app_profiling.db:
  1. In the Authentication field, type Database.
  2. In the User ID field, type DBA.
  3. In the Password field, type sql.
  4. In the Action dropdown list, select Start and connect to a database on this computer.
  5. In the Database file field, type C:\AppProfilingTutorial\app_profiling.db.
  6. In the Start Line field, type dbeng16 -x tcpip.
3. Click Connect.
In Sybase Central, click Mode » Application Profiling.

If the Application Profiling Wizard appears, click Cancel.
Start the Database Tracing Wizard as follows:
1. Click File » Tracing.
2. On the Welcome page, click Next.
3. On the Tracing Detail Level page, click High detail (recommended for short-term, intensive monitoring), and then click Next.
4. On the Edit Tracing Levels page, click Next.
5. On the Create External Database page, click Do not create a new database. I will use an existing tracing database, and then click Next.
6. On the Start Tracing page, click Save tracing data in this database.
7. To place no limits on the amount of stored tracing data, click No limit, and then click Finish.

Create the deadlock as follows:

In the left pane, ensure that app_profiling - DBA is selected, and then click File » Open Interactive SQL.

Interactive SQL starts and connects to the app_profiling.db database.
Repeat the previous step to open a second Interactive SQL window.

In the first Interactive SQL window, execute the following SQL statement:

CALL "DBA"."proc_deadlock1"();

In the second Interactive SQL window, execute the following SQL statement within 20 seconds of executing the SQL statement in the first Interactive SQL window:

CALL "DBA"."proc_deadlock2"();

After a few moments, an ISQL Error window appears indicating that a deadlock has been detected.

The deadlock occurred because proc_deadlock1 requires access to the deadlock2 table, which is locked by proc_deadlock2. At the same time, proc_deadlock2 requires access to the deadlock1 table, which is locked by proc_deadlock1.

Click OK.

SQL Anywhere stopped the deadlocked operations, so you can close the Interactive SQL windows.
In Sybase Central, stop the tracing session by selecting app_profiling - DBA in the left pane, and then clicking File » Tracing » Stop tracing with save.

Results

You created a deadlock and collected information about it.

Next

Proceed to Lesson 3: Reviewing blocked connection data.

Lesson 2: Creating a deadlock and capturing data

Task

See also