ROLLBACK, SAVEPOINT, and RELEASE are supported at the session level (outside of stored procedures) only if AUTOCOMMIT is off. Otherwise, ROLLBACK, SAVEPOINT, and RELEASE must be used within a stored procedure.
Any committed statements within a stored procedure are controlled by the outermost transaction of the calling SQL application.
A SAVEPOINT applies only to the procedure in which it is defined. That is, you cannot reference a SAVEPOINT defined in another procedure.
Examples
The following example sets a SAVEPOINT then checks a condition to determine whether to ROLLBACK or to RELEASE the SAVEPOINT.
CREATE PROCEDURE Enroll_student( in :student ubigint, IN :classnum integer);
BEGIN
DECLARE :CurrentEnrollment INTEGER;
DECLARE :MaxEnrollment INTEGER;
SAVEPOINT SP1;
INSERT INTO Enrolls VALUES (:student, :classnum, 0.0);
SELECT COUNT(*) INTO :CurrentEnrollment FROM Enrolls WHERE class_id = :classnum;
SELECT Max_size INTO :MaxEnrollment FROM Class WHERE ID = :classnum;
IF :CurrentEnrollment >= :MaxEnrollment THEN
ROLLBACK to SAVEPOINT SP1;
ELSE
RELEASE SAVEPOINT SP1;
END IF;
END;
Note that COUNT(expression) counts all nonnull values for an expression across a predicate. COUNT(*) counts all values, including null values.