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

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Usage » Performance improvements, diagnostics, and monitoring » Application profiling tutorials » Tutorial: Diagnosing deadlocks

 

Lesson 1: Setting up the database

Set up the application profiling test database, app_profiling.db, to create a deadlock by creating two tables and two procedures.

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 tutorial assumes that you have created the test database, app_profiling.db. See Tutorial: Creating a test database for the application profiling tutorials.

Context and remarks

Deadlocks occur when two or more transactions block one another. For example, Transaction A requires access to Table B, but Table B is locked by Transaction B. Transaction B requires access to Table A, but Table A is locked by Transaction A. A cyclical blocking conflict occurs.

A good indication that deadlocks are occurring is when SQLCODE -306 and -307 are returned. To resolve a deadlock, SQL Anywhere automatically rolls back the last statement that created the deadlock. Performance problems occur if statements are constantly rolled back.

 Task
  1. Start Interactive SQL. Click Start » Programs » SQL Anywhere 16 » Administration Tools » Sybase Central.

  2. In Interactive SQL, connect to app_profiling.db as follows:

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

    2. Click Connect.

  3. In Interactive SQL, execute 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:



      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 that you made to the database:

      COMMIT;
  4. Close Interactive SQL.

Results

You have created two tables and two procedures with which to create a deadlock.

 See also