MySQL: transaction in stored procedure

Question

The basic structure of my stored procedure:

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, the result of query 1 is executed.

• How can I cancel a transaction if any request fails?

Answer 1

Take a look at http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html

Basically you declare an error handler that is called by rollback

 START TRANSACTION; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; EXIT PROCEDURE; END; COMMIT; 

Answer 2

Just an alternative to rkosegi code,

 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 

Answer 3

Transaction in MySQL stored procedure

To execute a ROLLBACK in a MySQL stored procedure, we need to declare an exit handler in the stored procedure. There are two types of handlers in the MySQL stored procedure.

• SQLException
• sqlwarning

sqlexception will execute when an error occurs during query execution, and sqlwarning will execute when the warning procedure is stored in MySQL. Let's see how we can have this block in a stored procedure.

 DELIMITER $$ CREATE PROCEDURE `transaction_sp` () BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR ROLLBACK; END; DECLARE exit handler for sqlwarning BEGIN -- WARNING ROLLBACK; END; START TRANSACTION; INSERT INTO table_name (id, name, address) values ('1','Avinash','xpertdeveloper.com'); UPDATE second_table set name="xyz" where id=4; COMMIT; END $$ 

Answer 4

Here is an example of a transaction that will roll back on error and return an 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 ; 

It is assumed that auto-exchange is set to 0. I hope this helps.

Answer 5

Several times, the procedure I wrote above does not work. Therefore, I am changing the structure as shown below.

 begin declare exit handler for SQLEXCEPTION begin ROLLBACK; select 'An unpexpected error sprunged in your transaction.try again!' as 'Error'; end; start transaction; insert into transact values(1,t_type,amount,tTime,accNo); insert into rate values(name,age,years); insert into tax values(ols,timed,sss); commit; end; 

declare exit for SQLEXCEPTION statement, I wrote at the beginning, at the end of the block. For the first time I used it without a start, end block. But that makes a mistake for my procedure.