Transactions in Stored Procedures
On this page
Stored procedures support all transactional commands, namely BEGIN/START TRANSACTION, COMMIT, and ROLLBACK.
trancount session variable to control statement execution.
By default, each SQL statement inside an SP is executed sequentially and independently from any preceding or following statement.
This behavior also applies to transactional statements in the following way.
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() ASBEGINSTART TRANSACTION;INSERT INTO t VALUES (1); -- Insert is successfulCALL my_func(); -- Throws unhandled exceptionCOMMIT; -- Commit is unreachable, and the transaction remains openEND //DELIMITER ;
In the example above, an integer is successfully inserted, but an unhandled exception prevents the transaction from being committed.
Open transactions often result in unexpected behavior and can have serious consequences.
ECHO(), any SQL statements inside the SP will be executed as part of the prior uncommitted transaction.
To prevent unexpected behavior and to determine if a transaction is currently open, you can use the
trancount session variable.
1 if there is an open transaction, and a value of
0 if there is not an open transaction.
0 are the only possible values of
The following example demonstrates how to use
trancount in an SP:
DELIMITER //CREATE PROCEDURE check_trancount() ASDECLAREtrancount_query QUERY(i INT) = SELECT @@trancount;BEGINIF SCALAR(trancount_query) = 0 THENSTART 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.
Last modified: June 22, 2022