Hi,

I have a question regarding ROLLBACK in my stored procedure. Basically if there is any error, I would like the transaction to rollback. Am I right in assuming that I need the first ROLLBACK below? Thanks!!


CREATE PROCEDURE SPROC (id VARCHAR(24), OUT errormsg VARCHAR(128))
RESULT SETS 0
MODIFIES SQL DATA
NOT DETERMINISTIC
LANGUAGE SQL

BEGIN
DECLARE pid INTEGER DEFAULT -1;
DECLARE errorCode INTEGER DEFAULT 1;
DECLARE SQLCODE INTEGER DEFAULT 0;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET errorCode = SQLCODE;

SELECT pkeyid INTO pid FROM abc WHERE empid = id;--
IF pid = -1 THEN
--------- ROLLBACK; ------- ( Do I need this??? )
SET errormsg = 'Error : Failed to lookup id';
RETURN;
END IF;


INSERT INTO XYZ (....) VALUES (....);
IF errorCode != 1 THEN
ROLLBACK;
SET errormsg = 'Error : Failed to insert into xyz table';
RETURN;
END IF;
END
;

It is often considered a bad idea to issue either ROLLBACK or COMMIT in a routine, unless it is absolutely necessary. The routine should raise an error condition, which the calling application should handle and roll back the transaction, retry, or perform other actions as appropriate.

thanks for the reply n_j! Can you elaborate a little as to why it's a bad idea to rollback in the routine itself? thanks!!

At some point your application may grow to more than one stored procedure and nested SP calls. Transaction management should happen at a higher level in the application; a lower-level component, such as a stored procedure, does not necessarily have the context available to it in order to make a decision whether the transaction should be committed or rolled back. It may not even know that it is only a small part of a larger transaction, especially in two-phase commit situations.

In short: a COMMIT or ROLLBACK inside a stored procedure actually impacts the outside transaction in which the procedure was called. ROLLBACK is a bit of a special case, however. You can (and probably should) set a savepoint and then roll back to that savepoint only.

As to your question: you don't need a ROLLBACK here:
The reason is that you only did a SELECT and no data modifications. So no data changes need to be un-done. The only thing a ROLLBACK would achieve is to release any locks, depending on the isolation level.