Transactions in Stored Procedures
Stored procedures support all transactional commands, namely BEGIN/START TRANSACTION, COMMIT, and ROLLBACK. This topic describes the behavior of transactions in stored procedures and provides guidance on how to use the
trancount session variable to control statement execution.
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
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
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 DB, so
0 are the only possible values of
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.