Handling Deadlock
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.
To facilitate the proper coding and testing for these situations, you can use the following three template programs as guides in your resolution of similar error situations. Deadlock conditions are identified by the generic error code value of E_GEC2EC_SERIALIZATION.
The following templates assume the default OpenSQL transaction behavior (SET AUTOCOMMIT is OFF).
Noncursor Template for Handling Deadlock
This template 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 */
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 = E_GEC2EC_SERIALIZATION) 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:
Single Cursor Template for Handling Deadlock
This template is similar to the first, but with a single cursor added.
exec sql whenever not found continue;
exec sql whenever sqlwarning continue;
exec sql whenever sqlerror goto err;
/* branch on error */
exec sql declare c1 cursor for ...
start:
exec sql open c1;
while more rows loop
exec sql fetch c1 into ...
if (sqlca.sqlcode = E_GE0064_NO_MORE_DATA) then
exec sql close c1;
exec sql commit;
goto end;
end if;
exec sql insert into ...
exec sql update ...
exec sql select ...
end loop;
goto end
err:
exec sql whenever sqlerror call sqlprint;
if (sqlca.sqlcode = E_GEC2EC_SERIALIZATION) 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:
Master/Detail Template for Handling Deadlock
This template is like the previous one, but includes two cursors with a master/detail relationship.
exec sql whenever not found continue;
exec sql whenever sqlwarning continue;
exec sql whenever sqlerror goto err;
/* branch on error */
exec sql declare master cursor for ...
exec sql declare detail cursor for ...
start:
exec sql open master;
while more master rows loop
exec sql fetch master into ...
if (sqlca.sqlcode = E_GE0064_NO_MORE_DATA) 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 = E_GE0064_NO_MORE_DATA) 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 */
goto end
err:
exec sql whenever sqlerror call sqlprint;
if (sqlca.sqlcode = E_GEC2EC_SERIALIZATION) 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: 11/09/2022