MySQL Transactions — The Right Way

DROP PROCEDURE IF EXISTS mytestproc;
 
DELIMITER $$
 
CREATE PROCEDURE mytestproc()
BEGIN
    START TRANSACTION;
    SAVEPOINT x;
    
    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION    
        BEGIN
            ROLLBACK TO SAVEPOINT x; -- Rollback on exception    
            RESIGNAL; -- Rethrow the error (if you want to raise it upstream, i.e., in .NET/java code)
        END;
        
        #SAVEPOINT x; #named savepoint      
        
        SELECT 1;
    #Other statements here
    END;
    
    COMMIT;
    # Clean Up
    RELEASE SAVEPOINT x;
END$$    
 
DELIMITER ;
 
CALL mytestproc();