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 2: Creating a deadlock

This tutorial assumes you have created the test database. If you have not, see Lesson 1: Creating the test database.

Tip

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

To create a deadlock
  1. Start Sybase Central and connect to the test database app_profiling.db with the user ID DBA and the password sql.

    If you are unfamiliar with starting Sybase Central and connecting to a database, see Connect to a local database.

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

  3. In Interactive SQL, run the following SQL statements:

    1. 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) );
    2. Insert values into each table

      INSERT INTO "deadlock1"("val") VALUES('x');
      INSERT INTO "deadlock2"("val") VALUES('x');
    3. 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;
    4. Commit the changes you made to the database

      COMMIT;
  4. Exit Interactive SQL.