Stored procedures use transactions like any other database interface. A transaction can be committed or rolled back either inside the procedure or outside the procedure. Inside the procedure, a commit or roll back is done by using the following syntax:
EXEC SQL COMMIT WORK; EXEC SQL ROLLBACK WORK;
These statements end the previous transaction and start a new one.
If a transaction is not committed inside the procedure, it can be ended externally by using any of the following methods:
▪ solidDB SA,
▪ another stored procedure,
▪ autocommit, if the connection has AUTOCOMMIT set to ON.
The autocommit functionality works differently for statements inside a stored procedure than for statements outside a stored procedure. For SQL statements outside a stored procedure, each individual statement is implicitly followed by a COMMIT WORK operation when AUTOCOMMIT set to ON. For a stored procedure, however, the implicit COMMIT WORK operation is executed after the stored procedure has returned to the caller. Note that this does not imply that a stored procedure is "atomic". As previously indicated, a stored procedure can contain its own COMMIT and ROLLBACK statements. The implicit COMMIT WORK operation that is executed after the procedure returns will commit only that portion of the stored procedure statements that were executed since any of the following actions:
▪ the last COMMIT WORK statement inside the procedure,
▪ the last ROLLBACK WORK statement inside the procedure,
▪ the start of the procedure (if no COMMIT or ROLLBACK statements were executed during the procedure).
If one stored procedure is called from inside another stored procedure, the implicit COMMIT WORK operation is done only after the end of the outermost procedure call. There is no implicit COMMIT WORK operation done after nested procedure calls.
For example, in the following script, the implicit COMMIT WORK operation is executed only after the CALL outer_proc(); statement: