2

I have a stored procedure, let us call it sp_A which calls other stored procedures, let us call them sp_B and sp_C. For the sake of consistency an XA TRANSATCION is started within sp_A. What I actually want to do is to log if the execution of sp_B or sp_C fails and if so, which were the arguments passed to the failing stored procedure. But as sp_B and sp_C do some inserts in some tables, I need to do a XA ROLLBACK if sp_B or sp_C fails.

So my question is: How can I log into a table while I am within a transaction (or how to execute an insert statement outside of the transaction scope)?

To give you a little example of what I want to do (I've simplyfied the code and renamed most of the parameters/variables used to focus on the problem, I would never dream of using parameter names like a, b or c ;-) ):

The stored procedure sp_A looks like this:

CREATE PROCEDURE `sp_A`
(
  IN a INT,
  IN b VARCHAR(64),
  OUT c INT
)
sp_label:BEGIN
  DECLARE l_errorMessage TEXT;
  DECLARE l_spSuccess INT;
  DECLARE l_errorOccured INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
    SET l_errorOccured = 1;
  END;

  XA START 'sp_A';

  CALL sp_B(a, b, l_spSuccess);

  IF l_errorOccured OR (l_spSuccessResult < 0) THEN
    XA END 'sp_A';
    XA ROLLBACK 'sp_A';
    SET c = -1;
    SET l_errorMessage = CONCAT('CALL sp_B(', a, ', ', b, ', l_spSuccess);');
    CALL sp_Log(l_errorMessage);
    LEAVE sp_label;
  END IF;

  CALL sp_C(a, b, l_spSuccess);

  IF l_errorOccured OR (l_spSuccessResult < 0) THEN
    XA END 'sp_A';
    XA ROLLBACK 'sp_A';
    SET c = -2;
    SET l_errorMessage = CONCAT('CALL sp_C(', a, ', ', b, ', l_spSuccess);');
    CALL sp_Log(l_errorMessage);
    LEAVE sp_label;
  END IF;

  XA END 'sp_A';
  XA COMMIT 'sp_A';
  SET c = 1;
END;

The stored procedure sp_B looks like this (sp_C looks similar):

CREATE PROCEDURE `sp_B`
(
  IN a INT,
  IN b VARCHAR(64),
  OUT c INT
)
sp_label:BEGIN
  DECLARE l_errorMessage TEXT;
  DECLARE l_spSuccess INT;
  DECLARE l_errorOccured INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
    SET l_errorOccured = 1;
  END;

  INSERT INTO `someTable` (`a`, `b`) VALUES (a, b);

  IF l_errorOccured THEN
    SET c = -1;
    SET l_errorMessage = CONCAT('INSERT INTO `someTable` (`a`, `b`) VALUES (', a, ', ', b, ');');
    CALL sp_Log(l_errorMessage);
    LEAVE sp_label;
  END IF;

  SET c = 1;
END;

sp_Log just inserts data into a table which I want to use as my logging table.

So actually the logging within sp_A works just fine (because the call to sp_Log is outside a transaction) but the logging within sp_B or sp_C won't work because by rolling back the transaction started in sp_A, the inserts done by the call of sp_Log within sp_B or sp_C are reverted of course.

(I have simplified the example, in sp_B and sp_C. There is more going on than just one simple insert statement and just to have more fun, there are transactions started and commited/rolled back within sp_B and sp_C as well.)

I would appreciate any hints you could give me. Thanks!

1

It seem's that one viable way of solving this issue is using a table engine that does not support transactions. In this case changing the table engine of the log tables from InnoDB to MyISAM solved the problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.