 |
» |
|
|
|
This code is intended as a guide; you will want to customize it
for your specific needs. The code illustrates status checking techniques with emphasis
on deadlock detection. Four generalized code segments are presented: A status checking routine to be used in conjunction with the other
code segments. Using a single kept cursor with locks. Using multiple cursors and cursor stability. Avoiding locks on terminal reads.
Common StatusCheck Procedure | |
SQLStatusCheck ()
{
/************************************************************/
/* Deadlock occurred: Set DeadLockFree to FALSE */
/************************************************************/
if (sqlca.sqlcode = -14024) {
DeadLockFree = FALSE;
/************************************************************/
/* If your program monopolizes CPU time by repeatedly */
/* reapplying a transaction, you could include a call */
/* to the XL PAUSE intrinsic at this point. */
/************************************************************/
}
/************************************************************/
/* Fatal Error: Show Messages, Release DBE, and terminate */
/************************************************************/
else if (sqlca.sqlcode < 0) {
do {
EXEC SQL SQLEXPLAIN :SQLMessage;
printf(SQLMessage);
} while (sqlca.sqlcode != 0);
EXEC SQL RELEASE;
}
/************************************************************/
/* On fatal errors, halt program to avoid an endless loop */
/************************************************************/
halt ();
/************************************************************/
/* No More Rows Found: Set EndofScan to TRUE */
/************************************************************/
if (sqlca.sqlcode = 100)
EndofScan = TRUE;
} /* SQLStatusCheck function */
|
Single Cursor WITH LOCKS | |
SQLSingleCursor ()
{
/***************************************************************/
/* First, declare the cursor: */
/***************************************************************/
EXEC SQL DECLARE C1 CURSOR FOR SELECT PartName,
FROM PurchDB.Parts WHERE SalesPrice > 500.00;
/***************************************************************/
/* Next, open the cursor using KEEP CURSOR WITH LOCKS option: */
/***************************************************************/
do {
DeadLockFree = TRUE;
EXEC SQL OPEN C1 KEEP CURSOR WITH LOCKS;
if (sqlca.sqlcode != 0)
SQLStatusCheck ();
} while (!DeadLockFree);
/***************************************************************/
/* COMMIT WORK in order to preserve the cursor position */
/***************************************************************/
EXEC SQL COMMIT WORK;
if (sqlca.sqlcode != 0)
SQLStatusCheck ();
/***************************************************************/
/* Execute BULK FETCH option until there is no deadlock */
/***************************************************************/
EndofScan = FALSE;
while (! EndofScan) {
do {
DeadlockFree = TRUE;
EXEC SQL BULK FETCH C1 INTO :PriceList, 1, 20;
if (sqlca.sqlcode != 0)
SQLStatusCheck ();
else {
EXEC SQL COMMIT WORK;
if (sqlca.sqlcode != 0)
SQLStatusCheck ();
}
} while (!DeadLockFree);
|
/*********************************************************/
/* Display qualified rows. SQLERRD[3] contains the */
/* number of qualified rows. */
/*********************************************************/
printf ("\n Part Name Sales Price\n");
for (i = 0; i < sqlca.sqlerrd[2]; i)
printf("%s %10.2f",PriceList[i].PartName,PriceList[i].SalesPrice);
} /* WHILE not EndofScan */
/***************************************************************/
/* Execute the CLOSE command until no deadlock occurs */
/***************************************************************/
do {
EXEC SQL CLOSE C1;
if (sqlca.sqlcode != 0)
SQLStatusCheck();
} while (!DeadLockFree);
EXEC SQL COMMIT WORK;
if (sqlca.sqlcode != 0)
SQLStatusCheck();
} /* SQLSingleCursor function */
|
Multiple Cursors and Cursor Stability | |
SQLMultiCursor ()
{
/***************************************************************/
/* First, declare your cursors: */
/***************************************************************/
EXEC SQL DECLARE C1 CURSOR FOR SELECT BranchNo FROM Tellers
WHERE TellerNo > 15000 FOR UPDATE OF Status;
EXEC SQL DECLARE C2 CURSOR FOR SELECT BranchNo FROM Branches
FOR UPDATE OF Credit;
/***************************************************************/
/* Next, Open cursor C1. Use explicit BEGIN WORK in do while loop. */
/* loop to make sure ALLBASE/SQL will use the CS isolation level */
/* in case the program runs into a deadlock condition. */
/***************************************************************/
do {
DeadLockFree = TRUE;
EXEC SQL BEGIN WORK CS;
if (sqlca.sqlcode != 0)
SQLStatusCheck();
else {
EXEC SQL OPEN C1 KEEP CURSOR WITH LOCKS;
if (sqlca.sqlcode != 0)
SQLStatusCheck();
}
} while (!DeadLockFree);
/***************************************************************/
/* COMMIT WORK in order to preserve the cursor position */
/***************************************************************/
EXEC SQL COMMIT WORK;
if (sqlca.sqlcode != 0)
SQLStatusCheck();
/****************************************************************/
/* Initialize EndofScan to FALSE for the outer and inner loops */
/****************************************************************/
EndofScan = FALSE;
while (! EndofScan) {
|
/**********************************************************/
/* The following do while loop is executed once per */
/* deadlock. We FETCH again using Cursor C1, reopen */
/* Cursor C2, then start to fetch rows using C2. */
/* Note that there is a deadlock, and when the */
/* transaction is aborted, Cursor */
/* C2 is closed and Cursor C1 returns to the beginning */
/* of the transaction. Any work done by the current */
/* transaction on the database is undone. This provides */
/* a method of reapplying the transaction when a */
/* deadlock at any point rolls it back. */
/**********************************************************/
do {
DeadLockFree = TRUE;
EXEC SQL FETCH C1 INTO :HostBranchNo1;
if (sqlca.sqlcode != 0)
SQLStatusCheck();
else /* 1 */ {
EXEC SQL OPEN C2;
if (sqlca.sqlcode != 0)
SQLStatusCheck();
else /* 2 */ {
while ((! EndofScan) && DeadLockFree) {
EXEC SQL FETCH C2 INTO :HostBranchNo2;
if (sqlca.sqlcode != 0)
SQLStatusCheck();
else /* 3 */ {
if (HostBranchNo1 == HostBranchNo2) {
/**********************************************************/
/* Update the Branches table. Note: You might wish */
/* to include a DateUpdated column in the Branches */
/* table that can be updated with a timestamp every */
/* time the Credit column is updated. Then, in case */
/* the program terminates abnormally, you will know */
/* which values in the Credit column were updated */
/* before termination. */
/**********************************************************/
EXEC SQL UPDATE Branches
SET Credit = Credit*0.005
WHERE CURRENT OF C2;
if (sqlca.sqlcode != 0)
SQLStatusCheck();
} /* if HostBranchNo1 */
} /* else 3 */
} /* inner while not EndofScan clause */
|
if (EndofScan) {
EndofScan = FALSE;
EXEC SQL CLOSE C2;
if (sqlca.sqlcode != 0)
SQLStatusCheck();
else /* 4 */ {
EXEC SQL UPDATE TELLERS SET Status = :NewStatus
WHERE CURRENT OF C1;
if (sqlca.sqlcode != 0)
SQLStatusCheck();
else /* 5 */ {
/**********************************************************/
/* Changes are committed and a new transaction begins. */
/* Cursor C1 still open; locks associated with the page */
/* pointed to by the cursor are kept. All locks for C2 */
/* are released when the transaction is committed */
/**********************************************************/
EXEC SQL COMMIT WORK;
if (sqlca.sqlcode != 0)
SQLStatusCheck();
} /* else 5 */
} /* else 4 */
} /* if EndofScan */
} /* else 2 */
} /* else 1 */
} while (!DeadLockFree);
} /* outer while not EndofScan clause */
/***************************************************************/
/* Repeat the CLOSE command until no deadlock is found: */
/***************************************************************/
do {
EXEC SQL CLOSE C1;
if (sqlca.sqlcode != 0)
SQLStatusCheck();
} while (!DeadLockFree);
/****************************************************************/
/* Final COMMIT WORK: current transaction ends, and no new */
/* transaction begins. THIS STEP IS ABSOLUTELY ESSENTIAL */
/* TO END THE KEEP STATE! */
/****************************************************************/
EXEC SQL COMMIT WORK;
if (sqlca.sqlcode != 0)
SQLStatusCheck();
} /* SQLMultiCursor function */
|
Avoiding Locks on Terminal Reads | |
SQLNoTermLock ()
{
/***************************************************************/
/* First, declare the cursor: */
/***************************************************************/
EXEC SQL DECLARE C1 Cursor FOR
SELECT PartNumber, SalesPrice FROM PurchDB.Parts;
/***************************************************************/
/* Execute the OPEN command until there is no deadlock: */
/***************************************************************/
do {
DeadLockFree = TRUE;
EXEC SQL OPEN C1 KEEP CURSOR WITH NOLOCKS;
if (sqlca.sqlcode != 0)
SQLStatusCheck;
} while (!DeadLockFree);
/***************************************************************/
/* COMMIT WORK to preserve the cursor position: */
/***************************************************************/
EXEC SQL COMMIT WORK;
if (sqlca.sqlcode != 0)
SQLStatusCheck;
/***************************************************************/
/* Now FETCH a row from the active set until EndofScan */
/***************************************************************/
while (! EndofScan) {
do {
DeadLockFree = TRUE;
EXEC SQL FETCH C1 INTO :PartNumber, :PresentSalesPrice;
if (sqlca.sqlcode != 0)
SQLStatusCheck();
else {
EXEC SQL COMMIT WORK;
if (sqlca.sqlcode != 0)
SQLStatusCheck();
}
} while (!DeadLockFree);
/*********************************************************/
/* Display the present price and prompt for a new one: */
/*********************************************************/
printf("\n Part Number: %d", PartNumber);
printf("\n Current Sales Price: %10.2f", PresentSalesPrice);
scanf ("\n Enter New Sales Price: %10.2f", NewSalesPrice);
|
/*********************************************************/
/* Verify that the value of PresentSalesPrice has not */
/* changed. If not, update with NewSalesPrice */
/*********************************************************/
do {
DeadLockFree = TRUE;
EXEC SQL SELECT SalesPrice INTO :SalesPrice FROM PurchDB.Parts
WHERE PartNumber = :PartNumber;
if (sqlca.sqlcode < 0)
SQLStatusCheck();
if (sqlca.sqlcode = 100)
printf("\n Part Number no longer in database. Not updated.")
else {
if (SalesPrice == PresentSalesPrice) {
EXEC SQL UPDATE PurchDB.Parts
SET SalesPrice = :NewSalesPrice
WHERE PartNumber = :PartNumber;
if (sqlca.sqlcode != 0)
SQLStatusCheck();
}
else printf("\n Current price has changed. Not updated.");
}
} while (!DeadLockFree);
} /* while not EndofScan */
/***************************************************************/
/* Execute the CLOSE command until there is no deadlock: */
/***************************************************************/
do {
EXEC SQL CLOSE C1;
if (sqlca.sqlcode != 0)
SQLStatusCheck();
} while (!DeadLockFree);
/****************************************************************/
/* Final COMMIT WORK: current transaction ends, and no new */
/* transaction begins. THIS STEP IS ABSOLUTELY ESSENTIAL */
/* TO END THE KEEP STATE! */
/****************************************************************/
EXEC SQL COMMIT WORK;
if (sqlca.sqlcode != 0)
SQLStatusCheck();
} /* SQLNoTermLock function */
|
|
