Spring Nested transaction support for mysql with spring transactional

I've a stored procedure in MySQL which is being executed in @Transactional method.

DELIMITER //
CREATE PROCEDURE UpdateEmployeeSalary (IN newSalary DECIMAL(10,2))
BEGIN
    UPDATE Employee
    SET Salary = newSalary
    WHERE EmployeeID = 1;
END //
DELIMITER ;

Now since @Transactional starts its own transaction and the SP doest have any, it gets rolled back if there's any error in the callee method or transaction, which is as expected.

However my concern comes from what if someone executes the SP from console and an error is thrown from the SP.

Example

DELIMITER //
CREATE PROCEDURE UpdateEmployeeSalary (IN newSalary DECIMAL(10,2))
BEGIN
    UPDATE Employee
    SET Salary = newSalary
    WHERE EmployeeID = 1;

    -- simulate error
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Failed to update employee salary';

END //
DELIMITER ;

In this case since mysql is in auto commit mode, it'll commit the salary update and then throw the error. However to not let that happen, if I add transaction inside the SP i.e.

DELIMITER //
CREATE PROCEDURE UpdateEmployeeSalary (IN newSalary DECIMAL(10,2))
BEGIN
START TRANSACTION;
    UPDATE Employee
    SET Salary = newSalary
    WHERE EmployeeID = 1;
COMMIT;
END //
DELIMITER ;

it then doesn't get rollback with spring @Transactional if there's error in the callee method since mysql doesnt support nested transaction (it gets committed on COMMIT)

I'm not sure what is the correct way to solve this problem

Currently ive removed the transaction from SP and let spring manage the transaction which works well but will go into inconsistent state when someone runs the SP via console (non transaction way) and it throws error post UPDATE call.

Comment From: snicoll

Thanks for getting in touch, but it feels like this is a question that would be better suited to Stack Overflow. As mentioned in the guidelines for contributing, we prefer to use the issue tracker only for bugs and enhancements. Feel free to update this issue with a link to the re-posted question (so that other people can find it) or add some more details if you feel this is a genuine bug.