MySQL : transaction within a stored procedure

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;

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

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.