Lesson 2: Create a deadlock and capture 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.

 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 as follows:

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

  3. Create the deadlock as follows:

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

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

    3. In the first 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 of running 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 choosing File » Tracing » Stop Tracing With Save.

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

 See also
Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0