Lesson 2: Creating a deadlock and capturing information about it

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

Tip

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 than your production database to avoid impacting performance.

 Capture deadlock data

  1. In Sybase Central, click Mode » Application Profiling.

    If the Application Profiling Wizard appears, click Cancel.

  2. Start the Database Tracing Wizard as follows:

    1. In the left pane, click app_profiling - DBA, and 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.

  3. Create the deadlock as follows:

    1. In the left pane, click app_profiling - DBA, and then click File » Open Interactive SQL.

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

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

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

      CALL "DBA"."proc_deadlock1"();

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

      CALL "DBA"."proc_deadlock2"();

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

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

    6. Click OK.

  4. SQL Anywhere stopped the deadlocked operations, so you can close the Interactive SQL windows.

  5. In Sybase Central, stop the tracing session by clicking the app_profiling - DBA connection in the left pane and clicking File » Tracing » Stop Tracing With Save.

  6. Click this link to continue the tutorial: Lesson 3: Reviewing blocked connection data.

 See also
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1