602SQL Documentation Index  

Errors and Transactions

Transactions join database operations into atomic units. Most errors affect transactions, an error may cause a transaction to terminate prematurely or cause a rollback.

If an error or an exception causes a transaction rollback to occur, further actions may be one of the following:

  1. If the error/exception is "NOT FOUND" or a "handleable exception condition" and is processed by a handler, then the transaction is not affected (however the handler type determines if some statements are to be skipped or if the atomic block is to be rolled back).
  2. If the error belongs to the "rollback exception condition" type and is processed by a handler, then the transaction is rolled back before the handler is executed. After the handler is terminated, the program continues in the place set by the handler type.
  3. If the error or exception does not belong to a category processed by some handler, or no handler is found, the processing of SQL statements is terminated and the transaction is rolled back. The transaction termination depends on the client:

Details of Exception Handling Using EXIT Handler

If an exception of the "rollback exception condition" type is raised, changes since the transaction beginning are rolled back, however, the commands after the block end - where the EXIT handler for this exception is declared - are executed. If such behavior is undesirable, the EXIT handler can be moved to the external block or the RESIGNAL command can be executed inside it.

Exception handling can be illustrated by the following example:

The Handling procedure listed bellow demonstrates exception handling using EXIT handler. The handlable parameter determines whether they are raised by "handlable exception condition" (true) or "rollback exception condition" (false). The resignal_it parameter determines whether the handler will execute the RESIGNAL command. If the resignal_it parameter is true, then the procedure returns an error, since no other handler for this error has been declared.

To raise "handlable exception condition", we will use user exception ABC. To raise "rollback exception condition", we will use a table with a unique key; the procedure will try to insert into it values causing key duplicity. The table is defined like follows:


The table will simultaneously contain a result of procedure work. The procedure is:

PROCEDURE Handling(IN handlable Boolean, IN resignal_it Boolean);
  declare abc condition;
  delete from restab;
  insert into restab values(0);
    declare exit handler for sqlexception, abc
      update restab set val=val+100;
      IF resignal_it THEN resignal;  END IF;

   // make an update and commit it:
    update restab set val=val+1;   // sets [val] to 1
   // make an uncommitted update:
    update restab set val=val+10;  // sets [val] to 11
   // raise the exception:
    IF handlable THEN
      signal abc; // raises the user-defined handlable exception
      insert into restab values(11); // raises the "key duplicity" rollback-type exception
    END IF;
    update restab set val=val+1000; // not done because the block had been exited
  update restab set val=val+10000;  // done unless the exception is resignalled

Execution of the Handling procedure has following result:

Parameter handlable Parameter resignal_it Result val Procedure return code
false false 10101 OK
false true 101 Key duplicity 40004
true false 10111 OK
true true 111 User exception ABC is not handled 45000