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.
This tutorial assumes you have created the test database, app_profiling.db.
Connect to app_profiling.db as follows:
In Sybase Central, in the SQL Anywhere 12 plug-in, click Connections » Connect With SQL Anywhere 12.
In the Connect window, complete the following fields to connect to the test database, app_profiling.db.
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
Click Connect.
In the left pane, click app_profiling - DBA, and then click File » Open Interactive SQL.
Interactive SQL starts and connects to the app_profiling.db database.
In Interactive SQL:
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) ); |
Execute the following SQL statements to insert values into each table:
INSERT INTO "deadlock1"("val") VALUES('x'); INSERT INTO "deadlock2"("val") VALUES('x'); |
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; |
Execute the following SQL statements to commit the changes you made to the database:
COMMIT; |
Close Interactive SQL.
Click this link to continue the tutorial: Lesson 2: Creating a deadlock and capturing information about it.
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |