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: