Set up the application profiling test database, app_profiling.db, to create a deadlock by creating two tables and two procedures.
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.
Start Interactive SQL. Click Start » Programs » SQL Anywhere 16 » Administration Tools » Sybase Central.
In Interactive SQL, connect to app_profiling.db as follows:
In the Connect window, complete the following fields to connect to the test database, app_profiling.db:
In the Authentication field, type Database.
In the User ID field, type DBA.
In the Password field, type sql.
In the Action dropdown list, select Start and connect to a database on this computer.
In the Database file field, type C:\AppProfilingTutorial\app_profiling.db.
In the Start Line field, type dbeng16 -x tcpip.
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:
Close Interactive SQL.
Discuss this page in DocCommentXchange.
|Copyright © 2014, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0|