13. Understanding the Locking System : Deadlock : Deadlock in Applications
 
Share this page                  
Deadlock in Applications
The following sample program checks for deadlock after each statement of a multiple query transaction.
When a deadlock occurs, the transaction containing the statement aborts. There is no need to issue a rollback, as the transaction has already been aborted, and transaction retries until it successfully completes.
This sample program is written in embedded SQL/Fortran:
exec sql include SQLCA;
exec sql whenever sqlerror goto 100;
exec sql whenever not found continue;
exec sql begin declare section;
       integer*4 x;
exec sql end declare section;
x = 0;
exec sql commit;
10  continue;
exec sql select max(empno) into :x from emp;
exec sql insert into emp (empno) values (:x + 1);
exec sql commit;
goto 200;
100  if (sqlcode .eq. -4700) then goto 10
        endif
200
  .
  .
If no deadlock checking and handling is done, the program drops through to the next statement, which may result in incorrect results.
For example: The SELECT statement to retrieve the maximum employee number failed with a deadlock, there was no deadlock checking, the INSERT statement processes:
insert into emp (empno) values (:x + 1)
Because the SELECT statement did not complete, this statement inserts whatever was in ":x" + 1, which is probably an incorrect value or even a duplicate value.
Ingres 4GL provides the WHILE and ENDLOOP statements that perform the function of a GOTO statement and allow for checking and handling of deadlock.
The following is an example of Ingres 4GL code:
initialize(flag=integer2 not null, 
        err=integer2 not null) =
{
}
'Go' = {
        flag := 1;
        a: while 1=1 do
        b: while flag=1 do
                repeated update empmax
                        set maxempno=maxempno + 1;
                inquire_ingres (err = errno);
                if err = 49900 then
                        endloop b; /* jump to endwhile of loop b */
                endif;
                repeated insert into emp (empno)
                        select maxempno from empmax;
                inquire ingres (err = errorno);
                if err = 49900 then
                        endloop b; /* jump to endwhile of loop b */
                endif;
                flag := 0; /*resets flag if MST successful */
                endwhile; /* end of loop b */
        if flag = 0 then
                commmit
                endloop a; /* jump to endwhile of loop a */
        endif;
        endwhile; /* end of loop a */
}