Handling of Deadlocks
A deadlock occurs when two transactions are each waiting for the other to release a part of the database to enable it to complete its update. Transactions that handle deadlocks in conjunction with other errors can be difficult to code and test, especially if cursors are involved.
Example: Handling Deadlocks When Transactions Do Not Contain Cursors
The following example assumes your transactions do not contain a cursor:
exec sql whenever not found continue;
exec sql whenever sqlwarning continue;
exec sql whenever sqlerror goto err; /* branch
on error */
exec sql commit;
start:
exec sql insert into ...
exec sql update ...
exec sql select ...
exec sql commit;
goto end;
err:
exec sql whenever sqlerror call sqlprint;
if (sqlca.sqlcode = deadlock)
or (sqlca.sqlcode = forceabort) then
goto start;
else if (sqlca.sqlcode <> 0) then
exec sql inquire_sql (:err_msg =
errortext);
exec sql rollback;
print 'Error', err_msg;
end if;
end:
Example: Handling Deadlocks with One Cursor
The following example assumes your transactions contain a single cursor:
exec sql whenever not found continue;
exec sql whenever sqlwarning continue;
exec sql whenever sqlerror goto err;
exec sql declare c1 cursor for ...
exec sql commit;
start:
exec sql open c1;
while more rows loop
exec sql fetch c1 into ...
if (sqlca.sqlcode = zero_rows) then
exec sql close c1;
exec sql commit;
goto end;
end if;
exec sql insert into ...
exec sql update ...
exec sql select ...
end loop;
err:
exec sql whenever sqlerror call sqlprint;
if (sqlca.sqlcode = deadlock)
or (sqlca.sqlcode = forceabort) then
goto start;
else if (sqlca.sqlcode <> 0) then
exec sql inquire_sql (:err_msg =
errortext);
exec sql rollback;
print 'Error', err_msg;
end if;
end:
Example: Handling Deadlocks with Two Cursors
The following example assumes your transactions contains two cursors (two cursors with a master/detail relationship):
exec sql whenever not found continue;
exec sql whenever sqlwarning continue;
exec sql whenever sqlerror goto err;
exec sql declare master cursor for ...
exec sql declare detail cursor for ...
exec sql commit;
start:
exec sql open master;
while more master rows loop
exec sql fetch master into ...
if (sqlca.sqlcode = zero_rows) then
exec sql close master;
exec sql commit;
goto end;
end if;
/* ...queries using master data... */
exec sql insert into ...
exec sql update ...
exec sql select ...
exec sql open detail;
while more detail rows loop
exec sql fetch detail into ...
if (sqlca.sqlcode = zero_rows) then
exec sql close detail;
end loop;/* drops out of detail
fetch loop */
end if;
/* ...queries using detail & master data... */
exec sql insert into ...
exec sql update ...
exec sql select ...
end loop; /* end of detail fetch loop */
/* ...more queries using master data... */
exec sql insert into ...
exec sql update ...
exec sql select ...
end loop;/* end of master fetch loop */
err:
exec sql whenever sqlerror call sqlprint;
if (sqlca.sqlcode = deadlock)
or (sqlca.sqlcode = forceabort) then
goto start;
else if (sqlca.sqlcode <> 0) then
exec sql inquire_sql (:err_msg =
errortext);
exec sql rollback;
print 'Error', err_msg;
end if;
end:
Last modified date: 01/30/2023