Transactions in Stored Procedures

Understanding Transaction Processing in a Stored Procedure

By default, each SQL statement inside an SP is executed sequentially and independently from any preceding or following statement. If an SP throws an unhandled exception during the course of its execution, any SQL statement executed before the error will be processed, and any SQL statement following the error will not be processed.

This behavior also applies to transactional statements in the following way. If an SP throws an unhandled exception after starting a transaction and before committing it, the transaction will remain open. Uncommitted transactions are not automatically rolled back if an SP fails to execute completely.

Consider the following example, which starts a transaction, inserts an integer into a table, and attempts to commit the transaction:

DELIMITER //
CREATE PROCEDURE insert_transaction_error() AS
  BEGIN
    START TRANSACTION;
    INSERT INTO t VALUES (1); -- Insert is successful
    CALL my_func(); -- Throws unhandled exception
    COMMIT; -- Commit is unreachable, and the transaction remains open
  END //
DELIMITER ;

In the example above, an integer is successfully inserted, but an unhandled exception prevents the transaction from being committed. At this point, the transaction remains open. To ensure that the transaction does not remain open endlessly, add a rollback for the transaction in the exception handling section of the stored procedure.

Open transactions often result in unexpected behavior and can have serious consequences. For example, if an open transaction exists when an SP is invoked using either CALL() or ECHO(), any SQL statements inside the SP will be executed as part of the prior uncommitted transaction. This behavior will continue until a statement is executed that terminates the transaction, such as COMMIT, or ROLLBACK.

The trancount Session Variable

To prevent unexpected behavior and to determine if a transaction is currently open, you can use the trancount session variable. This variable has a value of 1 if there is an open transaction, and a value of 0 if there is not an open transaction. Note that nested transactions are not supported in SingleStore, so 1 and 0 are the only possible values of trancount.

The following example demonstrates how to use trancount in an SP:

DELIMITER //
CREATE PROCEDURE check_trancount() AS
  DECLARE
    trancount_query QUERY(i INT) = SELECT @@trancount;
  BEGIN
    IF SCALAR(trancount_query) = 0 THEN
      START TRANSACTION;
    END IF;
  END //
DELIMITER ;

In the example above, the trancount session variable is read, and if a transaction is not currently open, it starts a new transaction.