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.