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 */
}
Last modified date: 01/30/2023