Unanswered: Deadlock handling, Retrying & Simulating in DB2
I have a requirement of adding retry logic in Cobol-DB2 based application programs.
The SQLCODEs that I need to handle are -911, -913 & -904 and for these SQLCODES I need to bring in a retry logic.
Background information for these SQLCODEs.
-911 -> When this SQLCODE is encountered , a deadlock has occurred on the transaction and an implicit rollback is performed by DB2
-913 -> A deadlock has occurred on the transaction and an implicit rollback has failed / not occurred on the transaction.
-904 -> A resource unavailable condition has occurred and no rollback will be ever committed by DB2.
According to me, the different ways to do a retry when either of these SQLCODEs occur are:
Retry during -911 ->
For normal select queries in embedded cobol sql programs, the select query can be retried for a certain period of time (2 mins) and if retry is successful then proceed with normal execution. (It is assumed that there are not insert or updates happening before).
If -911 deadlock happens in a program where there are inserts or updates happening , after a rollback everything from the tables into which data was inserted during this transaction would vanish. Hence there should be a commit happening after a certain number of records are Inserted/Updated.
During rollback, which would happen till the last commit point , the processing needs to be resumed using a checkpoint restart method.
If -911 occurs while using a cursor , the situation becomes really grim. All the cursors used & open till deadlock gets closed. Hence the fetch operation for the remaining records needs to be done again. Please let me know a better way of doing this.
Retry during -913 & -904 ->
Since no rollback occurs during this case, the corrsponding queries can be retried and continued is retry is successful.
Can someone please suggest some efficient way of handling retry logic?
It would be required to test the changes involving retry logic in the programs. This can be done by simulating the appropriate deadlock/resource unavailable condition.
It is known that by locking or deleting tables, these conditons can be simulated. Is there a way to simulate deadlock -913 SQLCODE?
Please let me know if you have any better methods for checking /handling & simulating deadlock conditions and using retry logic.