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.

Prérequis

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.

Contexte et remarques

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

Start Interactive SQL. Click Start » Programs » SQL Anywhere 16 » Administration Tools » Sybase Central.
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.

In Interactive SQL, execute the following SQL statements:

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) );

Insert values into each table:

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

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;

Commit the changes that you made to the database:
COMMIT;

Close Interactive SQL.

Résultat

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

Suivant

Proceed to Lesson 2: Creating a deadlock and capturing data.

Lesson 1: Setting up the database

Task

See also