Like the code below is it safe to do that?
create definer=`root`@`%` procedure `test`()
label: begin
declare exit handler for sqlexception rollback;
start transaction;
select condition from table where id = someid for update;
if condition = false then
leave label;
end if;
// some query;
commit;
end$$
Best Answer
I would argue that transaction control does not belong to stored procedures at all, apart from some very particular circumstances (e.g. where you want to log audit records regardless of what happens to the "outer" transaction -- i.e. "autonomous transaction"). The procedure has no way of knowing if it is part of a larger unit of work and therefore, if it commits or rolls back, it might violate consistency rules of a larger transaction.
Commit or roll back at the level where business rules are implemented.
In other words, yes, in the majority of cases it's the right thing to do "to leave/exit the stored procedure without commit or rollback" and let the caller decide (provided that you don't hide the exception if it occurs).