Lesson 1: Set up steps for creating a deadlock

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.

 To create a deadlock

  1. This tutorial assumes you have created the test database, app_profiling.db. If you have not, see Create a test database for the application profiling tutorials.

  2. Connect to app_profiling.db as follows:

    1. In Sybase Central, in the SQL Anywhere 12 plug-in, choose Connections » Connect With SQL Anywhere 12.

    2. In the Connect window, complete the following fields to connect to the test database, app_profiling.db, and then click Connect:

      • Authentication   Database

      • User ID    DBA

      • Password    sql

      • Action   Start A Database On This Computer

      • Database File   C:\AppProfilingTutorial\app_profiling.db

      • Start Line   dbeng12 -x tcpip

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

  4. In Interactive SQL:

    1. Execute the following SQL statements to create two tables you will use later to create the deadlock:

      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. Execute the following SQL statements to insert values into each table:

      INSERT INTO "deadlock1"("val") VALUES('x');
INSERT INTO "deadlock2"("val") VALUES('x');

    3. Execute the following SQL statements to create two procedures that you will use later 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. Execute the following SQL statements to commit the changes you made to the database:

      COMMIT;

  5. Close Interactive SQL.

  6. Click this link to continue the tutorial: Lesson 2: Create a deadlock and capture information about it.

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