6. Working with Transactions and Handling Errors : Error Handling : Handling of Deadlocks : Example: Handling Deadlocks with Two Cursors
 
Share this page                  
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: