Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » SQL Anywhere Server - SQL Usage » Monitoring and Improving Database Performance » Application profiling tutorials » Tutorial: Diagnosing deadlocks


Lesson 3: Capturing deadlock data

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

To capture deadlock data
  1. In Sybase Central, choose Mode » Application Profiling.

    If the Application Profiling Wizard appears, click Cancel.

  2. Start the Database Tracing Wizard.

    1. In the left pane click app_profiling - DBA, and choose File » Tracing.

    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.

  3. Create the deadlock.

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

    2. Repeat the previous step to open a second Interactive SQL window.

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

      CALL "DBA"."proc_deadlock1"();
    4. 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.

    5. Click OK.

  4. Close both Interactive SQL windows.

  5. To stop the tracing session, in Sybase Central select the app_profiling - DBA database and choose File » Tracing » Stop Tracing With Save.