 |
» |
|
|
|
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 | |
S100-SQL-STATUS-CHECK.
**************************************************************
* Deadlock did not occur; Set Deadlock-Flag to DeadlockFree. *
* Exit status checking routine without displaying a message. *
**************************************************************
IF SQLCODE = 0
MOVE SPACE TO Deadlock-Flag
GOTO S100-EXIT.
**************************************************************
* Deadlock occurred; set Deadlock-Flag to Deadlock. *
* Exit status checking routine without displaying a message. *
**************************************************************
IF SQLCODE = -14024
MOVE "X" TO Deadlock-Flag
**************************************************************
* If your program monopolizes CPU time by repeatedly *
* reapplying a transaction, you could include a call *
* to the XL PAUSE intrinsic at this point. *
**************************************************************
GOTO S100-EXIT.
**************************************************************
* No more rows found; Set EndOfScan-Flag to EndOfScan. *
* Exit status checking routine without displaying a message. *
**************************************************************
IF SQLCODE = 100
MOVE "X" TO EndOfScan-Flag
GOTO S100-EXIT.
**************************************************************
* 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 *
**************************************************************
PERFORM S200-SQLEXPLAIN THRU S200-EXIT
UNTIL SQLCODE = 0
EXEC SQL
RELEASE
END-EXEC.
STOP RUN.
S100-EXIT.
EXIT.
S200-SQLEXPLAIN.
EXEC SQL
SQLEXPLAIN :SQLMessage
END-EXEC.
DISPLAY SQLMessage.
S200-EXIT.
EXIT.
S300-OPEN-C1-WITH-LOCKS.
EXEC SQL
OPEN C1 KEEP CURSOR WITH LOCKS
END-EXEC.
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT.
S300-EXIT.
EXIT.
S400-OPEN-C1-WITH-NOLOCKS.
EXEC SQL
OPEN C1 KEEP CURSOR WITH NOLOCKS
END-EXEC.
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT.
S400-EXIT.
EXIT.
S500-CLOSE-C1.
EXEC SQL
CLOSE C1
END-EXEC.
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT.
S500-EXIT.
EXIT.
S600-COMMIT-WORK.
EXEC SQL
COMMIT WORK
END-EXEC.
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT.
S600-EXIT.
EXIT.
|
Single Cursor WITH LOCKS | |
A100-SINGLE-CURSOR.
**************************************************************
* Declare cursor C1. *
**************************************************************
EXEC SQL
DECLARE C1 CURSOR FOR
SELECT PartName, SalesPrice FROM PurchDB.Parts
WHERE SalesPrice > 500.00
END-EXEC.
**************************************************************
* Open cursor C1 using KEEP CURSOR WITH LOCKS option, *
* testing for deadlocks. *
**************************************************************
MOVE "X" TO Deadlock-Flag.
PERFORM S300-OPEN-C1-WITH-LOCKS THRU S300-EXIT
UNTIL Deadlock-Free.
**************************************************************
* COMMIT WORK in order to preserve initial cursor position. *
**************************************************************
PERFORM S600-COMMIT-WORK THRU S600-EXIT.
**************************************************************
* BULK FETCH data from the Parts table using cursor C1 until *
* there is no more data. Display qualifying rows. *
**************************************************************
MOVE SPACE TO EndOfScan-Flag.
PERFORM A200-FETCH-AND-DISPLAY THRU A200-EXIT
UNTIL EndOfScan.
**************************************************************
* CLOSE cursor C1, testing for deadlocks. *
**************************************************************
MOVE "X" TO Deadlock-Flag.
PERFORM S500-CLOSE-C1 THRU S500-EXIT
UNTIL Deadlock-Free.
**************************************************************
* Execute final COMMIT WORK to release all locks held by *
* cursor C1. *
**************************************************************
PERFORM S600-COMMIT-WORK THRU S600-EXIT.
A100-EXIT.
EXIT.
A200-FETCH-AND-DISPLAY.
**************************************************************
* BULK FETCH qualifying rows from the Parts table using *
* cursor C1 until there is no more data, testing for *
* deadlocks. *
**************************************************************
MOVE "X" TO Deadlock-Flag.
PERFORM A300-BULK-FETCH-C1 THRU A300-EXIT
UNTIL Deadlock-Free OR EndOfScan.
IF EndOfScan
GOTO A200-EXIT.
**************************************************************
* Execute COMMIT WORK to release all page locks held by *
* cursor C1 except the current page. *
**************************************************************
PERFORM S600-COMMIT-WORK THRU S600-EXIT.
**************************************************************
* 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. *
**************************************************************
PERFORM A400-DISPLAY-ROW THRU A400-EXIT
VARYING NUMROWS FROM 1 BY 1
UNTIL NUMROWS = SQLERRD(3) OR NUMROWS = BUFFEREND.
A200-EXIT.
EXIT.
A300-BULK-FETCH-C1.
EXEC SQL
BULK FETCH C1 INTO :PriceList, 1, 20
END-EXEC.
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT.
A300-EXIT.
EXIT.
A400-DISPLAY-ROW.
DISPLAY " Part Name: " PARTNAME(NUMROWS).
DISPLAY " Sales Price: " SALESPRICE(NUMROWS).
DISPLAY " ".
A400-EXIT.
EXIT.
|
Multiple Cursors and Cursor Stability | |
B100-MULTI-CURSOR.
**************************************************************
* Declare cursor C1 and cursor C2. *
**************************************************************
EXEC SQL
DECLARE C1 CURSOR FOR
SELECT BranchNo FROM Tellers WHERE TellerNo > 15000
FOR UPDATE OF Status
END-EXEC.
EXEC SQL
DECLARE C2 CURSOR FOR
SELECT BranchNo FROM Branches
FOR UPDATE OF Credit
END-EXEC.
**************************************************************
* 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. *
**************************************************************
MOVE "X" TO Deadlock-Flag.
PERFORM B400-BEGIN-WORK-OPEN-C1 THRU B400-EXIT
UNTIL Deadlock-Free.
**************************************************************
* COMMIT WORK in order to preserve initial cursor position. *
**************************************************************
PERFORM S600-COMMIT-WORK THRU S600-EXIT.
**************************************************************
* 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. *
**************************************************************
MOVE SPACE TO EndOfScan-Flag.
PERFORM B200-FETCH-C1-AND-UPDATE THRU B200-EXIT
UNTIL EndOfScan.
**************************************************************
* CLOSE cursor C1, testing for deadlocks. *
**************************************************************
MOVE "X" TO Deadlock-Flag.
PERFORM S500-CLOSE-C1 THRU S500-EXIT
UNTIL Deadlock-Free.
**************************************************************
* Execute final COMMIT WORK to release all locks held by *
* cursor C1. *
**************************************************************
PERFORM S600-COMMIT-WORK THRU S600-EXIT.
B100-EXIT.
EXIT.
B200-FETCH-C1-AND-UPDATE.
**************************************************************
* FETCH data from Tellers table using cursor C1. *
**************************************************************
EXEC SQL
FETCH C1 INTO :HostBranchNo1
END-EXEC.
**************************************************************
* OPEN cursor C2 (without the KEEP CURSOR option). *
**************************************************************
IF SQLCODE = 0
EXEC SQL
OPEN C2
END-EXEC.
**************************************************************
* 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
MOVE SPACE TO Deadlock-Flag
PERFORM B300-FETCH-C2-AND-UPDATE THRU B300-EXIT
UNTIL EndOfScan OR Deadlock
IF EndOfScan
MOVE SPACE TO EndOfScan-Flag
EXEC SQL
CLOSE C2
END-EXEC.
**************************************************************
* 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
EXEC SQL
UPDATE TELLERS SET Status = :NewStatus
WHERE CURRENT OF C1
END-EXEC.
**************************************************************
* 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
EXEC SQL
COMMIT WORK
END-EXEC.
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT.
B200-EXIT.
EXIT.
B300-FETCH-C2-AND-UPDATE.
**************************************************************
* FETCH data from the Branches table using cursor C2. *
**************************************************************
EXEC SQL
FETCH C2 INTO :HostBranchNo2
END-EXEC.
**************************************************************
* Update Branches table if: *
* FETCH was successful (SQLCODE = 0), and *
* Teller.BranchNo = Branches.BranchNo *
**************************************************************
IF SQLCODE = 0 AND HostBranchNo1 = HostBranchNo2
EXEC SQL
UPDATE Branches
SET Credit = Credit * 0.005 WHERE CURRENT OF C2
END-EXEC.
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT.
B300-EXIT.
EXIT.
B400-BEGIN-WORK-OPEN-C1.
**************************************************************
* 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. *
**************************************************************
EXEC SQL
BEGIN WORK CS
END-EXEC.
IF SQLCODE = 0
EXEC SQL
OPEN C1 KEEP CURSOR WITH LOCKS
END-EXEC.
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT.
B400-EXIT.
EXIT.
|
Avoiding Locks on Terminal Reads | |
C100-NO-TERM-LOCK.
**************************************************************
* Declare cursor C1. *
**************************************************************
EXEC SQL
DECLARE C1 CURSOR FOR
SELECT PartName, SalesPrice FROM PurchDB.Parts
END-EXEC.
**************************************************************
* Open cursor C1 using KEEP CURSOR WITH NOLOCKS option, *
* testing for deadlocks. *
**************************************************************
MOVE "X" TO Deadlock-Flag.
PERFORM S400-OPEN-C1-WITH-NOLOCKS THRU S400-EXIT
UNTIL Deadlock-Free.
**************************************************************
* COMMIT WORK in order to preserve initial cursor position. *
**************************************************************
PERFORM S600-COMMIT-WORK THRU S600-EXIT.
**************************************************************
* FETCH and DISPLAY data in qualifying rows of the Parts *
* table using cursors C1 until no more rows are found, *
* testing for deadlocks. *
**************************************************************
MOVE SPACE TO EndOfScan-Flag.
PERFORM C200-FETCH-AND-DISPLAY THRU C200-EXIT
UNTIL EndOfScan.
**************************************************************
* CLOSE cursor C1, testing for deadlocks. *
**************************************************************
MOVE "X" TO Deadlock-Flag.
PERFORM S500-CLOSE-C1 THRU S500-EXIT
UNTIL Deadlock-Free.
**************************************************************
* Execute final COMMIT WORK to release all locks held by *
* cursor C1. *
**************************************************************
PERFORM S600-COMMIT-WORK THRU S600-EXIT.
C100-EXIT.
EXIT.
C200-FETCH-C1-AND-DISPLAY.
**************************************************************
* FETCH data from the Parts table using cursor C1, testing *
* for deadlocks. *
**************************************************************
MOVE "X" TO Deadlock-Flag.
PERFORM C300-FETCH THRU C300-EXIT
UNTIL DeadlockFree.
**************************************************************
* Execute COMMIT WORK to release all page locks held by *
* cursor C1. *
**************************************************************
PERFORM S600-COMMIT-WORK THRU S600-EXIT.
**************************************************************
* Display values from Parts.PartNumber and Parts.SalesPrice, *
* and prompt user for a new sales price. *
**************************************************************
DISPLAY " Part Number: " PartNumber.
DISPLAY " Sales Price: " PresentSalesPrice.
DISPLAY "Enter new sales price: ".
ACCEPT NewSalesPrice.
**************************************************************
* Re-select data from the Parts table and verify that the *
* SalesPrice has not changed. If unchanged, update the row *
* with the value in NewSalesPrice. *
**************************************************************
MOVE "X" TO Deadlock-Flag.
PERFORM C400-SELECT-AND-UPDATE THRU C400-EXIT
UNTIL DeadlockFree.
C200-EXIT.
EXIT.
C300-FETCH-C1.
EXEC SQL
FETCH C1 INTO :PartNumber, :PresentSalesPrice
END-EXEC.
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT.
C300-EXIT.
EXIT.
C400-SELECT-AND-UPDATE.
**************************************************************
* Re-select data from the Parts table. *
**************************************************************
EXEC SQL
SELECT SalesPrice INTO :SalesPrice FROM PurchDB.Parts
WHERE PartNumber = :PartNumber
END-EXEC.
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT.
IF EndOfScan
DISPLAY "Part number no longer in database. Not updated."
GOTO C500-EXIT.
IF SalesPrice NOT = PresentSalesPrice
DISPLAY "Current price has changed. Not updated."
GOTO C500-EXIT.
**************************************************************
* If Parts.SalesPrice has not changed, update the qualifying *
* row with the value in NewSalesPrice. *
**************************************************************
EXEC SQL
UPDATE PurchDB.Parts
SET SalesPrice = :NewSalesPrice
WHERE PartNumber = :PartNumber
END-EXEC.
PERFORM S100-SQL-STATUS-CHECK THRU S100-EXIT.
C400-EXIT.
EXIT.
|
|
