 |
» |
|
|
|
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. The following four generalized code segments are
presented: Using status checking routine 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 | |
PROCEDURE SQLStatusCheck;
BEGIN
CASE SQLCA.SQLCODE OF
(*************************************************************)
(* Deadlock did not occur; Set DeadLockFree to TRUE *)
(*************************************************************)
0: DeadLockFree := TRUE;
(*************************************************************)
(* Deadlock occurred; set DeadLockFree to FALSE. *)
(* Exit status checking routine without displaying a message.*)
(*************************************************************)
-14024: BEGIN
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. *)
(*************************************************************)
END;
(*************************************************************)
(* No more rows found; Set EndOfScan-Flag to EndOfScan. *)
(* Exit status checking routine without displaying a message.*)
(*************************************************************)
100: EndOfScan := TRUE;
|
(*************************************************************)
(* For other fatal errors: *)
(* PERFORM S200-SQLEXPLAIN to display messages *)
(* RELEASE the DBE *)
(* Stop the program *)
(* *)
(* Some errors which could be considered fatal are: *)
(* -3040 DBA issued a STOP DBE command *)
(* -3043 DBA issued a terminate user command *)
(* -14046 log full error *)
(* -14047 system clock/timestamp error *)
(* -14074 DBCore internal error *)
(* -14075 DBCore internal error *)
(* -15048 DBCore internal error *)
(*************************************************************)
OTHERWISE
REPEAT
EXEC SQL SQLEXPLAIN :SQLMessage;
writeln(SQLMessage);
UNTIL SQLCA.SQLCODE = 0;
EXEC SQL RELEASE;
halt;
END; (* CASE Statement *)
END; (* Procedure SQLStatusCheck *)
|
Single Cursor WITH LOCKS | |
(*************************************************************)
(* Declare cursor C1. *)
(*************************************************************)
EXEC SQL DECLARE C1 CURSOR FOR
SELECT PartName, SalesPrice FROM PurchDB.Parts
WHERE SalesPrice > 500.00;
(*************************************************************)
(* Open cursor C1 using KEEP CURSOR WITH LOCKS option, *)
(* testing for deadlocks. *)
(*************************************************************)
DeadLockFree := FALSE;
REPEAT
EXEC SQL OPEN C1 KEEP CURSOR WITH LOCKS;
SQLStatusCheck;
UNTIL DeadLockFree;
(*************************************************************)
(* COMMIT WORK in order to preserve initial cursor position. *)
(*************************************************************)
EXEC SQL COMMIT WORK;
SQLStatusCheck;
(*************************************************************)
(* BULK FETCH qualifying rows from the Parts table using *)
(* cursor C1 until there is no more data, testing for *)
(* deadlocks. *)
(*************************************************************)
EndOfScan := FALSE;
REPEAT
DeadLockFree := FALSE;
REPEAT
EXEC SQL BULK FETCH C1 INTO :PriceList, 1, 20;
SQLStatusCheck;
UNTIL DeadLockFree OR EndOfScan;
IF DeadLockFree
BEGIN
(*************************************************************)
(* Execute COMMIT WORK to release all page locks held by *)
(* cursor C1 except the current page. *)
(*************************************************************)
EXEC SQL COMMIT WORK;
SQLStatusCheck;
|
(*************************************************************)
(* Display qualifying rows. SQLERRD[3] contains the actual *)
(* number of qualified rows. BUFFEREND contains the maximum *)
(* number of rows declared in the buffer which receives data *)
(* from the BULK FETCH command. *)
(*************************************************************)
IF SQLERRD[3] > BUFFEREND THEN
NUMROWS := BUFFEREND
ELSE
NUMROWS := SQLERRD[3];
FOR i := 1 TO NUMROWS DO
BEGIN
writeln(' Part Name: ',PriceList[i].PartName);
writeln(' Sales Price: ',PriceList[i].SalesPrice);
writeln;
END;
END;
UNTIL EndOfScan;
(*************************************************************)
(* CLOSE cursor C1, testing for deadlocks. *)
(*************************************************************)
DeadLockFree := FALSE;
REPEAT
EXEC SQL CLOSE C1;
SQLStatusCheck;
UNTIL DeadLockFree;
(*************************************************************)
(* Execute final COMMIT WORK to release all locks held by *)
(* cursor C1. *)
(*************************************************************)
EXEC SQL COMMIT WORK;
SQLStatusCheck;
|
Multiple Cursors and Cursor Stability | |
(*************************************************************)
(* Declare cursor C1 and cursor C2. *)
(*************************************************************)
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;
(*************************************************************)
(* Open cursor C1 using KEEP CURSOR WITH LOCKS option, *)
(* testing for deadlocks. Use an explicit BEGIN WORK CS *)
(* command in the loop to ensure that ALLBASE/SQL will use *)
(* the CURSOR STABILITY isolation level if a deadlock occurs.*)
(*************************************************************)
DeadLockFree := FALSE;
REPEAT
EXEC SQL BEGIN WORK CS;
IF SQLCA.SQLCODE = 0 THEN
EXEC SQL OPEN C1 KEEP CURSOR WITH LOCKS;
SQLStatusCheck;
UNTIL DeadLockFree;
(*************************************************************)
(* COMMIT WORK in order to preserve initial cursor position. *)
(*************************************************************)
EXEC SQL COMMIT WORK;
SQLStatusCheck;
(*************************************************************)
(* FETCH and UPDATE data in qualifying rows of the Tellers *)
(* table and Branches table using cursors C1 and C2 until *)
(* no more rows are found. *)
(*************************************************************)
EndOfScan := FALSE;
REPEAT
(*************************************************************)
(* FETCH data from Tellers table using cursor C1. *)
(*************************************************************)
EXEC SQL FETCH C1 INTO :HostBranchNo1;
(*************************************************************)
(* OPEN cursor C2 (without the KEEP CURSOR option). *)
(*************************************************************)
|
IF SQLCODE = 0 THEN
BEGIN
EXEC SQL OPEN C2;
(*************************************************************)
(* For each qualifying row in the Tellers table: *)
(* FETCH and UPDATE rows in the Branches table using cursor*)
(* C2 until no more rows are found, testing for deadlocks. *)
(*************************************************************)
IF SQLCODE = 0 THEN
BEGIN
DeadLockFree := TRUE;
(*************************************************************)
(* FETCH data from the Branches table using cursor C2. *)
(*************************************************************)
REPEAT
EXEC SQL FETCH C2 INTO :HostBranchNo2;
(*************************************************************)
(* Update Branches table if: *)
(* FETCH was successful (SQLCODE = 0), and *)
(* Teller.BranchNo = Branches.BranchNo *)
(*************************************************************)
IF SQLCODE <> 0 THEN
SQLStatusCheck
ELSE
IF HostBranchNo1 = HostBranchNo2 THEN
BEGIN
EXEC SQL UPDATE Branches
SET Credit = Credit * 0.005
WHERE CURRENT OF C2;
SQLStatusCheck;
END;
UNTIL EndOfScan OR NOT DeadLockFree;
IF EndOfScan THEN
BEGIN
EndOfScan := FALSE;
EXEC SQL CLOSE C2;
(*************************************************************)
(* After successfully completing the FETCH and UPDATE of data*)
(* in qualifying rows of the Branches table using cursor C2, *)
(* UPDATE the Tellers table using cursor C1. *)
(*************************************************************)
IF SQLCODE = 0 THEN
BEGIN
EXEC SQL UPDATE TELLERS
SET Status = :NewStatus
WHERE CURRENT OF C1;
|
(*************************************************************)
(* Execute COMMIT WORK to: *)
(* Save UPDATEs to Branches table using cursor C2 *)
(* Release all page locks held by cursor C2 *)
(* Save UPDATES to Tellers table using cursor C1 *)
(* Release pages locked by cursor C1 except current page *)
(*************************************************************)
IF SQLCODE = 0 THEN
EXEC SQL COMMIT WORK;
END;
END;
END;
END;
SQLStatusCheck;
UNTIL EndOfScan;
(*************************************************************)
(* CLOSE cursor C1, testing for deadlocks. *)
(*************************************************************)
DeadLockFree := FALSE;
REPEAT
EXEC SQL CLOSE C1;
SQLStatusCheck;
UNTIL DeadLockFree;
(*************************************************************)
(* Execute final COMMIT WORK to release all locks held by *)
(* cursor C1. *)
(*************************************************************)
EXEC SQL COMMIT WORK;
SQLStatusCheck;
|
Avoiding Locks on Terminal Reads | |
(*************************************************************)
(* Declare cursor C1. *)
(*************************************************************)
EXEC SQL DECLARE C1 CURSOR FOR
SELECT PartName, SalesPrice FROM PurchDB.Parts;
(*************************************************************)
(* Open cursor C1 using KEEP CURSOR WITH NOLOCKS option, *)
(* testing for deadlocks. *)
(*************************************************************)
DeadLockFree := FALSE;
REPEAT
EXEC SQL OPEN C1 KEEP CURSOR WITH NOLOCKS;
SQLStatusCheck;
UNTIL DeadLockFree;
(*************************************************************)
(* COMMIT WORK in order to preserve initial cursor position. *)
(*************************************************************)
EXEC SQL COMMIT WORK;
SQLStatusCheck;
(*************************************************************)
(* FETCH and DISPLAY data in qualifying rows of the Parts *)
(* table using cursors C1 until no more rows are found, *)
(* testing for deadlocks. *)
(*************************************************************)
EndOfScan := FALSE;
REPEAT
(*************************************************************)
(* FETCH data from the Parts table using cursor C1, testing *)
(* for deadlocks. *)
(*************************************************************)
DeadLockFree := FALSE;
REPEAT
EXEC SQL FETCH C1 INTO :PartNumber, :PresentPrice;
SQLStatusCheck;
UNTIL DeadlockFree;
(*************************************************************)
(* Execute COMMIT WORK to release all page locks held by *)
(* cursor C1. *)
(*************************************************************)
EXEC SQL COMMIT WORK;
SQLStatusCheck;
(*************************************************************)
(* Display values from Parts.PartNumber and Parts.SalesPrice,*)
(* and prompt user for a new sales price. *)
(*************************************************************)
writeln(' Part Number: ',PartNumber);
writeln(' Sales Price: ',PresentPrice);
prompt ('Enter new sales price: ');
readln (NewPrice);
(*************************************************************)
(* Re-select data from the Parts table and verify that the *)
(* SalesPrice has not changed. If unchanged, update the row *)
(* with the value in NewPrice. *)
(*************************************************************)
DeadLockFree := FALSE;
REPEAT
|
(*************************************************************)
(* Re-select data from the Parts table. *)
(*************************************************************)
EXEC SQL SELECT SalesPrice INTO :SalesPrice
FROM PurchDB.Parts WHERE PartNumber = :PartNumber;
SQLStatusCheck;
IF EndOfScan THEN
writeln('Part number no longer in database. Not updated')
ELSE
IF SalesPrice NOT = PresentPrice
writeln('Current price has changed. Not updated')
ELSE
(*************************************************************)
(* If Parts.SalesPrice has not changed, update the qualifying*)
(* row with the value in NewPrice. *)
(*************************************************************)
BEGIN
EXEC SQL UPDATE PurchDB.Parts
SET SalesPrice = :NewPrice
WHERE PartNumber = :PartNumber;
SQLStatusCheck;
END;
UNTIL DeadLockFree;
UNTIL EndOfScan;
(*************************************************************)
(* CLOSE cursor C1, testing for deadlocks. *)
(*************************************************************)
DeadLockFree := FALSE;
REPEAT
EXEC SQL CLOSE C1;
SQLStatusCheck;
UNTIL DeadLockFree;
(*************************************************************)
(* Execute final COMMIT WORK to release all locks held by *)
(* cursor C1. *)
(*************************************************************)
EXEC SQL COMMIT WORK;
SQLStatusCheck;
|
|
