MySQL : transaction within a stored procedure
MysqlSqlStored ProceduresTransactionsMysql Problem Overview
The basic structure of my stored procedure is,
BEGIN
.. Declare statements ..
START TRANSACTION;
.. Query 1 ..
.. Query 2 ..
.. Query 3 ..
COMMIT;
END
> MySQL version: 5.1.61-0ubuntu0.11.10.1-log
Currently, if 'query 2' fails, result of 'query 1' is committed.
- How can I rollback the transaction if any of the query fails?
Mysql Solutions
Solution 1 - Mysql
Take a look at http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html
Basically you declare error handler which will call rollback
START TRANSACTION;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
EXIT PROCEDURE;
END;
COMMIT;
Solution 2 - Mysql
Just an alternative to the code by rkosegi,
BEGIN
.. Declare statements ..
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
.. set any flags etc eg. SET @flag = 0; ..
ROLLBACK;
END;
START TRANSACTION;
.. Query 1 ..
.. Query 2 ..
.. Query 3 ..
COMMIT;
.. eg. SET @flag = 1; ..
END
Solution 3 - Mysql
Here's an example of a transaction that will rollback on error and return the error code.
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_CREATE_SERVER_USER`(
IN P_server_id VARCHAR(100),
IN P_db_user_pw_creds VARCHAR(32),
IN p_premium_status_name VARCHAR(100),
IN P_premium_status_limit INT,
IN P_user_tag VARCHAR(255),
IN P_first_name VARCHAR(50),
IN P_last_name VARCHAR(50)
)
BEGIN
DECLARE errno INT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO;
SELECT errno AS MYSQL_ERROR;
ROLLBACK;
END;
START TRANSACTION;
INSERT INTO server_users(server_id, db_user_pw_creds, premium_status_name, premium_status_limit)
VALUES(P_server_id, P_db_user_pw_creds, P_premium_status_name, P_premium_status_limit);
INSERT INTO client_users(user_id, server_id, user_tag, first_name, last_name, lat, lng)
VALUES(P_server_id, P_server_id, P_user_tag, P_first_name, P_last_name, 0, 0);
COMMIT WORK;
END$$
DELIMITER ;
This is assuming that autocommit is set to 0. Hope this helps.
Solution 4 - Mysql
[This is just an explanation not addressed in other answers]
At least in recent versions of MySQL, your first query is not committed.
If you query it under the same session you will see the changes, but if you query it from a different session, the changes are not there, they are not committed.
What's going on?
When you open a transaction, and a query inside it fails, the transaction keeps open, it does not commit nor rollback the changes.
So BE CAREFUL, any table/row that was locked with a previous query like SELECT ... FOR SHARE/UPDATE
, UPDATE
, INSERT
or any other locking-query, keeps locked until that session is killed (and executes a rollback), or until a subsequent query commits it explicitly (COMMIT
) or implicitly, thus making the partial changes permanent (which might happen hours later, while the transaction was in a waiting state).
That's why the solution involves declaring handlers to immediately ROLLBACK
when an error happens.
Extra
Inside the handler you can also re-raise the error using RESIGNAL
, otherwise the stored procedure executes "Successfully":
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
#.. Query 1 ..
#.. Query 2 ..
#.. Query 3 ..
COMMIT;
END