Search code examples
mysqlstored-proceduresinnodb

Execution flow, transactions and MySQL stored procedure

I want to know if stored procedure in MySQL is something more than sequence of SQL statements with some restrictions (http://dev.mysql.com/doc/refman/5.7/en/stored-program-restrictions.html).

Is there something special with transactions, DML on InnoDB tables etc.?

As far as I know it isn't, because I failed to find out opposite.

Edit:

Execution flow is also an interesting question.

Assume I have simple following procedure and autocommit is off: 

CREATE PROCEDURE someproc ()
    -> BEGIN
    ->   -- exec stmt1
    ->   -- exec stmt2  <--- assume it fails!
    ->   -- exec stmt3
    -> END//

What happens if stmt2 fails during execution for some reason?

In my opinion stmt2 changes will be rollbacked, but stmt1 changes are not and execution flow of procedure will be stopped so stmt3 won't be execute.

Solution

  • A MySQL stored program supports syntax beyond SQL statements, such as declaring variables, conditional branching, loops, cursors, dynamic SQL, etc. In that sense, it is more than a sequence of SQL statements.

    There's nothing "special" about DML statements on InnoDB tables or transactions in a MySQL stored program. A PROCEDURE is the most lenient in terms of allowed oprations. There are some restrictions on FUNCTIONS and TRIGGERS (as as you have found). The restrictions mostly stem from how functions get called (as an expression within a statement, or how triggers get fired (when a DML statement is executing.)

    EDIT

    Unless a HANDLER is declared to catch an exception/error, when an error is encountered within a MySQL stored PROCEDURE, the execution of the procedure stops and the error is returned to the caller.

    As far was what DML changes are committed or rolled back, that depends on whether the tables are MyISAM or not, and whether auto commit is enabled. or on whether a COMMIT or ROLLBACK is issued for the transaction. It doesn't matter whether the DML statement is executed within a stored procedure, or outside of a procedure.